We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
You query is only returning the last 3 letters of NAME, not the full name. You need to return NAME and then sort by 'right(name, 3), ID ASC'.... It's just an order of operations problem. You have the right idea though
As i have recently started learning sql.....
I want to know what is the difference b/w mysql,oracle,MS sql server,db2.... Is there any syntax difference or what??? plzzz tell me
Right function helps to extract a number of characters starting from the Right position
syntax :
RIGHT(string, number_of_chars_from_right)
eg :
SELECT RIGHT("Hackerrank",4)
O/P :
rank
Additional : Vice-versa for LEFT function
the question is that you have to return complete name of students on the basis of last 3 characters, but you are only selcting last 3 characters of the name for displaying. The output of your query will only show alphabets not the complete name.
@surajbhupal
right(name,3) selects the last 3 characters in the string.
Here, in this code, you are displaying only the last 3 characters of all the selected students.
Whereas in the problem statement, you are asked to display the names of Students, not just last 3 characters.
You are asked to order by last 3 characters of student Names.
So the code for MY SQL could be:
selectName// Select all student namesfromStudents// From Student tablewheremarks>75// who scores over 75orderbyright(Name,3),ID;// and order name by (last 3 characters), ID
ACTUALLY THE CODE IS RIGHT BUT THE EXECUTION IS WRONG AFTER THE SELECT STATEMENT YOU MUST WRITE ONLY NAME THERE BECAUSE WE HAVE TO PRINT THE COMPLETE NAME NOT THE LAST THREE CHARACTERS OF NAMES
select name FROM students
WHERE marks>75 ORDER BY RIGHT(name,3),Id ASC;
you have to output name of a student
select right(name,3) from students where marks > 75 order by ID asc ;and this query gives last three character of name only
thats why its not working.
It's the generic procedure of traversing through a string or list. If you go reversal, you would have the end value as -1 followed by -2...a[-n] till the first value. So, -3 means the third last character, 3 means the 4th character, i.e, you are starting from the reverse order of the values stored, thus the values are in negative ascending.
[length(name)-2] would mean the third last character.
Try this :
select name from students where marks >75 order by substr(name,length(name)-2,3), id asc;
Ok. The SUBSTR function I used in this excercise has 3 parameters:
String = corresponds to the name of a student.
The starting position = length(Name)-2. For example, If I've got a name like 'Carlos', this name's length is 6. I substract 2 to define the starting position, I mean 4. In this case, I will start extracting from the 'L' character.
How many characters am I going to extract?: In this case will be 3. And that's how I get the String to use it in the 'Order by' clause after the query.
Okay, so suppose a string's length is 5, and we want the last 3 letters of the string,
I'd draw five pipes for you :
| | | | | -
In order to fetch last three pipes, I'd have to start from the 3rd pipe and go till the end (i.e. the 5th pipe).
let's say a new string has length 6, for cutting off the last 3 letters,
| | | | | |
I'd have to start from the 4th pipe and go till 6th. This means we'd have to start from 2 shy from the end cause,
the end pipe is the third pipe itself. Hence, Length(string) -2;
we can generalize this one - for a string of length x, if we want a substring of last Y letters,
then this : substr(string, (length(string) - y+1),y)
substr stands for substring. If you want a piece of a string, in the same order of course, A Substr() will cut the string into a thread of a length specified by the user.
Takes three arguments, 1. the field name of the string you want to cut out a piece of.
2. the starting point (where to cut)
3. the length (how much to cut)
This plays out well in this problem, where we want a sorted order of a particular piece of the name field.
anyone can tell me the difference between this
SELECT id, substring(name,-3), marks
FROM students
WHERE marks>75
ORDER BY substring(name,-3),
id; what is wrong in this way i got the same output as using
SELECT (NAME) FROM STUDENTS WHERE MARKS>75 ORDER BY SUBSTR(NAME,-3,3), ID ASC;
Inside the substring function you can replace len(name)-2 with -3. Negative means te counting starts from behind. And you do not need to specify asc after id as asc is the default option considered by ORDER BY.
In MYSQL we will have a function called Right (), which takes the args. as Right(Str,length) by using this we can sucessfully retrive the characters of the string characters from the rightside. Hence we are using right(name,3) to select the last 3 characters and then used orderby to order the string.
hey!! its written no where in question to give only distinct output.
There may be a case where 2 students may have same name but different id and marks.
id is the field in students table and asc means ascending order.
if you write id desc then it will sort the selected attribute acc to decreasing order of id field.
SELECT SUBQUERY1.name FROM
(SELECT ID,name, RIGHT(name, 3) AS ExtractString
FROM students where marks > 75 ) SUBQUERY1 order by SUBQUERY1.ExtractString ,SUBQUERY1.ID asc ;
Adding ASC explicitly is not required for Oracle/MySQL. This will be used by default if you leave it. Moreover both of the ordering is ascending of this query. Leaving ASC for this query should be fine.
hi, can anyone explain execution order of this query?
In problem question asked that if two or more names of last three characters are same then we have sort to by id but in this query only sort by right() and do not checking for names having same characters
Nice :) Just when you sort by order, it sorts ascending by default so you do not need to specify ASC, you just need to specify DESC when you want to sort descending :)
Hi can you please tell how in this case ORDER BY is working ?
Why is it not ordering both name and id at the same time . I mean to say why it is going to id only when duplicate comes ?
Higher Than 75 Marks
You are viewing a single comment's thread. Return to all comments →
For MS SQL Server, you can use RIGHT function like;
Similar to mine in SQL Server
You can just use RIGHT(Name,3) function
Hey, I started with right fucntion only, can anyone explain why it's not working
select right(name,3) from students where marks > 75 order by ID asc ;
You query is only returning the last 3 letters of NAME, not the full name. You need to return NAME and then sort by 'right(name, 3), ID ASC'.... It's just an order of operations problem. You have the right idea though
select name from students where marks>75 order by right(name,3),ID;
As i have recently started learning sql..... I want to know what is the difference b/w mysql,oracle,MS sql server,db2.... Is there any syntax difference or what??? plzzz tell me
https://www.w3schools.com/sql/sql_alter.asp
Can you explain use of right function ?
Right function helps to extract a number of characters starting from the Right position syntax : RIGHT(string, number_of_chars_from_right) eg : SELECT RIGHT("Hackerrank",4) O/P : rank Additional : Vice-versa for LEFT function
worked but it not working only with right(name,3) ;
SELECT name from students where marks > 75 order by substr(name,length(name)-2, 3) , id asc;
Can you please explain me the importance of len(name) -2? Thanks in advance.
substr function needs 3 input params
select name from students where Marks>75 order by right(Name,3),right(Name,2),right(Name,1),ID;
It worked for me
you need to print the entire name so you need to select the entire name but order by last three alphabets so SELECT NAME (for selecting full name)
FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
Why are we ordering name by id?
the question is that you have to return complete name of students on the basis of last 3 characters, but you are only selcting last 3 characters of the name for displaying. The output of your query will only show alphabets not the complete name.
hope you understand.
select name from students where marks>75 order by right(name,3) , id asc;
@surajbhupal right(name,3) selects the last 3 characters in the string.
Here, in this code, you are displaying only the last 3 characters of all the selected students. Whereas in the problem statement, you are asked to display the names of Students, not just last 3 characters. You are asked to order by last 3 characters of student Names.
So the code for MY SQL could be:
Hope this helps :)
HELLO, Why is it necessary to order it by ID as well?
It is given in question.
ACTUALLY THE CODE IS RIGHT BUT THE EXECUTION IS WRONG AFTER THE SELECT STATEMENT YOU MUST WRITE ONLY NAME THERE BECAUSE WE HAVE TO PRINT THE COMPLETE NAME NOT THE LAST THREE CHARACTERS OF NAMES
--Check this----- select name from STUDENTS where marks>75 order by right(name,3),id asc;
select name FROM students WHERE marks>75 ORDER BY RIGHT(name,3),Id ASC;
you have to output name of a student select right(name,3) from students where marks > 75 order by ID asc ;and this query gives last three character of name only thats why its not working.
You have to select the Name and order it by last 3 letters.
you have to display the whole name.. so write select name.......(check reference output given in the qsn)
Can you please explain the syntax of substring using the above example (-2,3) ?
//-2 means the substring starts from the third last character of the city name //3 means the length of the substring is 3
According to me -2 means starts from the 2nd last character and -3 means start from third last character. please explain
It's the generic procedure of traversing through a string or list. If you go reversal, you would have the end value as -1 followed by -2...a[-n] till the first value. So, -3 means the third last character, 3 means the 4th character, i.e, you are starting from the reverse order of the values stored, thus the values are in negative ascending.
you should use -3 instead of -2, because the index for last index is -1 so as we need last three characters we need to use -3
Use this it works SELECT (NAME) FROM STUDENTS WHERE MARKS>75 ORDER BY SUBSTR(NAME,-3,3), ID ASC;
-3 depicts last three characters whereas 3 depicts length
its not working.... remove ID ASC; the right query is **
**
select name from students where marks>75 order by substr((name),-3,3),ID asc;
could you please explain why you wrote (-3,3)??
-3 means starting from the third last character and 3 means upto 3 characters
[length(name)-2] would mean the third last character. Try this : select name from students where marks >75 order by substr(name,length(name)-2,3), id asc;
IT IS STILL NOT WORKING
select name from students where Marks >75 order by substr(name,-3,3), id asc;
SUBSTR(name), -3, 3) will not work. Replace that with SUBSTR(name, length(name), -2, 3)
it is not working for me
select Name from STUDENTS where marks > 75 order by SUBSTR(Name, length(Name)-2,3), id asc;
This works on Oracle.
Greetings.
can u please explain why -2,3 range ??
Ok. The SUBSTR function I used in this excercise has 3 parameters:
String = corresponds to the name of a student.
The starting position = length(Name)-2. For example, If I've got a name like 'Carlos', this name's length is 6. I substract 2 to define the starting position, I mean 4. In this case, I will start extracting from the 'L' character.
How many characters am I going to extract?: In this case will be 3. And that's how I get the String to use it in the 'Order by' clause after the query.
I hope this explanation works :)
thank you soo much
Why length -2?
Okay, so suppose a string's length is 5, and we want the last 3 letters of the string, I'd draw five pipes for you : | | | | | - In order to fetch last three pipes, I'd have to start from the 3rd pipe and go till the end (i.e. the 5th pipe). let's say a new string has length 6, for cutting off the last 3 letters,
| | | | | |
I'd have to start from the 4th pipe and go till 6th. This means we'd have to start from 2 shy from the end cause, the end pipe is the third pipe itself. Hence, Length(string) -2;
we can generalize this one - for a string of length x, if we want a substring of last Y letters, then this : substr(string, (length(string) - y+1),y)
Wow, Thank you. That helps.
thanx
great
Thank you,it is working.
WHAT EXACTLY THIS SUBSTR CLAUSE MEANS ????
substr stands for substring. If you want a piece of a string, in the same order of course, A Substr() will cut the string into a thread of a length specified by the user. Takes three arguments, 1. the field name of the string you want to cut out a piece of. 2. the starting point (where to cut) 3. the length (how much to cut) This plays out well in this problem, where we want a sorted order of a particular piece of the name field.
Thank you ! It works
I tried but it is not working. It is showing error in code and I don't understand why.
ERROR at line 1: ORA-00933: SQL command not properly ended
I've definitely put the ';' at the end of code.
SUBSTR(NAME,-3) will also give same result.
I have checked W3Schools page about Right function, it does not use "ID ASC" part in this code. Why this is used?
anyone can tell me the difference between this SELECT id, substring(name,-3), marks FROM students WHERE marks>75 ORDER BY substring(name,-3), id; what is wrong in this way i got the same output as using SELECT (NAME) FROM STUDENTS WHERE MARKS>75 ORDER BY SUBSTR(NAME,-3,3), ID ASC;
SUBSTRING( string, start_position, length ) Assume Name is Jay. So String= 'Jay' start_pos= len(string)-2=3-2=1 length=3
So it will check from 1st to 3rd letter.
in sql they start counting strings from 1 not from 0, so it's -3, 3 try to belive
it is not working because we can orderby only those column which are present in select clause and id is not ibn select clause
select name from students where marks>75 order by substr((name),-3,3),ID asc;
**It is not necessary to keep ID in select condition.
but why we need id in asc?
in order to order the names in ascending based on last three characters
it has written in question
what does id here mean
Instead of substrings , use simple Right(name,3),Id asc
You can also use substring(name,-3,3)
WHAT IS THE USE OF ID?
select name from students where marks>75 order by substr(name,-3,3) , id asc;
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTR(NAME, -3, 3) ASC, ID ASC;
hey im using this query why this is not working???
select name from students where marks > 75 order by substring(name,-3), name;
because in your query amina is coming before aamina but aamina comes first then amina according to sorting order
you can ommit "ASC" due that's the default order
can u explain why len(name)-2 , why we should minus 2 from length
select name from students where marks>75 order by substr(name,length(name)-2,length(name)),id
Inside the substring function you can replace len(name)-2 with -3. Negative means te counting starts from behind. And you do not need to specify asc after id as asc is the default option considered by ORDER BY.
why is it len(name) -2 and not -3?
Don't we have to use DISTINCT ? Because question says there is no duplicates in last three characters and arranged by id.......
why are you taking len(name)-2, please explain
you can avoid len() by using minus
select name from students where marks > 75 order by substr(name,-3,3),id asc;
len(name)-2, 3) ....... -2 ok i get it.. but why 3.....? if 3 is ositive that number es located on the left ???
could you please explain " right(name, 3)" this part? what does right means?
It means select the last 3 characters.,
(select 3 characters starting from the right most end of the string)
hello sir is there any job for me on SQL, please my mailId: rahilkannur2@gmail.com
lol
@lombahujur, I have just came across this pretty interesting function. Here's where you can read about it.
In MYSQL we will have a function called Right (), which takes the args. as Right(Str,length) by using this we can sucessfully retrive the characters of the string characters from the rightside. Hence we are using right(name,3) to select the last 3 characters and then used orderby to order the string.
The "right()" function is a embemnded function in MySQL?
Yeah RIGHT() and LEFT() are in MySQL as String functions
i got the same error .so, i removed distinct and it worked. i don't know why!!
hey!! its written no where in question to give only distinct output. There may be a case where 2 students may have same name but different id and marks.
https://dba.stackexchange.com/questions/34951/order-by-clause-is-allowed-over-column-that-is-not-in-select-list
It works
can we do this without using right
Can do that if you're using Oracle.
You can use substring. But not conventionally.
Yes, is there any possible way to write a query using regexp in MySql?
FOR ORACLE
select name from students WHERE marks > 75 order by substr(name,-3), ID ASC;
ORACLE solution works for me
SELECT name FROM students WHERE marks > 75 ORDER BY SUBSTR(name,-3),id ASC;
its not wrking bro
For Oracle: select Name from STUDENTS where Marks >75 order by SUBSTR(Name, (length(Name)-2), length(Name));
GOOD ONE BRO
why '-2'?
Becuase he want's sort by last 3 letters.
The Simple way to write the same query is as below. The Above query and below query will yeild the same result. Cheers !!
Select Name from Students where marks>75 order by lower(Substr(Name,-3,3)),ID asc;
WHY TO USE ID HERE ?
It gives an result but while running the test case it shows error, kindly guide me to fix this
what does the ID ASC part mean ?
"If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID."
I put that part because of this part above in explanation of problem. I do not know if it works without it, maybe it works.
Order by ID in ascending order.
ascending by default it is in ascending order
what does id asc means??
It sorts the ID column in ascending order..
id is the field in students table and asc means ascending order. if you write id desc then it will sort the selected attribute acc to decreasing order of id field.
wts right function now?>
SELECT SUBQUERY1.name FROM (SELECT ID,name, RIGHT(name, 3) AS ExtractString FROM students where marks > 75 ) SUBQUERY1 order by SUBQUERY1.ExtractString ,SUBQUERY1.ID asc ;
Thanks
Thank you very much!! I've been stucked with this.
ya Right function is right for this query...
substr also works
select name from students where marks > 75 order by substring(name,-3), id;
it's not working...
In Oracle, it worked perfectly for me like this:
you don't need to use length function. Just write select name from STUDENTS where marks>75 order by substr(name,-3,3), id;
When you pass a negative number in the substr method as the starting index, it starts the count from the end of the string.
Adding
ASCexplicitly is not required for Oracle/MySQL. This will be used by default if you leave it. Moreover both of the ordering is ascending of this query. LeavingASCfor this query should be fine.RIGHT FUNCTION NOT WORKING
Yeah,
RIGHTfunction is MySQL specific. UseSUBSTR(string, start [, length])for other databases.Can you pls tell ? what is the use of right? Thanks
RIGHTfunction is for selecting characters from the right side of the string. Alternatively you can say that from the end of the string.For more details check here
Thank you
in my code its showing that right is an invalid identifier and thus shows error. how to approach it next?
Nice approach
can u explain order by Right(name,3)
What does ID ASC mean?
why ID has to be in asc?
hi, can anyone explain execution order of this query? In problem question asked that if two or more names of last three characters are same then we have sort to by id but in this query only sort by right() and do not checking for names having same characters
can you please tell the working of id asc
id is the column name on which you are going to print in ascending order so id asc;
@sonergonul can you explain how the secondary sort is working here in this example with this code ?
Thanks
Nice :) Just when you sort by order, it sorts ascending by default so you do not need to specify ASC, you just need to specify DESC when you want to sort descending :)
for oracle
select name from students where marks > 75 order by substr(name,-3,3) ;
thanks it work for me..
Hi, if i run this code i am getting error, saying "right" invalid identifier.
nice )
what is this ID ? and why we need this ?
can you explain why you did not include ID after the select statement? mine was asking to add ID bcs i ordered by ID.
by default the clause "order by" is ascending so you could omit 'asc'
SELECT name FROM students WHERE marks > 75 ORDER BY RIGHT(name,3), id
how does "ID ASC" help with getting DISTINCT substrings?
WHy we including ID can you explain??
because it's written in the question itself
If two or more students both have names ending in the same last three characters (i.e.: Bobby, Robby, etc.), secondary sort them by ascending ID.
**this is another similer one. using substring instead of right or left func. **
select name from students where marks>75 order by substring(name,-3), id asc;
why id asc?
because we want result in ascending order therefore here we use id
Hi can you please tell how in this case ORDER BY is working ? Why is it not ordering both name and id at the same time . I mean to say why it is going to id only when duplicate comes ?
I had this same query but with a "DISTINCT" before "NAME". Why did it throw an error?
why id asc??
Was wondering what happens if student's name is 2 characters, eg: Zu, Li
Hii sonergonul can you please explain why you use( ,id asc) i mean why we use comma and why dont and , or.
why you use ID here ?
Please share link for right function. I haven't heard of this before
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
Thank you Rishabh but I am looking for webpage / blog for detail information.
What is ID ASC here
THIS CODE CONTAINING RUN TIME ERROR CAN YOU PLEASE FIX IT
in order by, why can't use "AND" instead of" ,"
why should we use ID before
wht that id is given
In the above code why can not we use 'and' before 'ID ASC;' whats wrong in the below mentioned code??
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3) and ID ASC;
Can u explain this query . ?
what is the meaning of RIGHT(NAME, 3) ????
This query should be flawed as it works only when ID is provided in sorting. Tests were successful so it is showing green.
Can you explain how your query works as mentioned in question to use secondary sort if there are similar chars in last three chars?
Why do we need ID?
select distinct(name) from students where marks>75 order by substr(name,length(name),3), id;
what is the work of id asc here can u explain ?
You need to order it in ASC on the basis of ID
Hi - can you explain why you use "ID ASC" ?
What does ID ASC mean?
Can anyone please explain what I have done wrong here?
Use comma after RIGHT(NAME,3), it will work