Sort 315 Discussions, By:
Please Login in order to post a comment
For MS SQL Server, you can use RIGHT function like;
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
Similar to mine in SQL Server
order by substring(name,len(name)-2, 3) , id asc;
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
order by right(name,3),ID;
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.
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);
select name from students where marks>75 order by substr((name),-3,3),ID asc;
could you please explain why you wrote (-3,3)??
IT IS STILL NOT WORKING
SUBSTR(name), -3, 3) will not work. Replace that with SUBSTR(name, length(name), -2, 3)
Thank you,it is working.
WHAT EXACTLY THIS SUBSTR CLAUSE MEANS ????
SUBSTRING( string, start_position, length )
Assume Name is Jay.
So String= 'Jay'
So it will check from 1st to 3rd letter.
it is not working because we can orderby only those column which are present in select clause and id is not ibn select clause
**It is not necessary to keep ID in select condition.
Instead of substrings , use simple Right(name,3),Id asc
You can also use substring(name,-3,3)
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)
@lombahujur, I have just came across this pretty interesting function. Here's where you can read about it.
The "right()" function is a embemnded function in MySQL?
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.
can we do this without using right
Can do that if you're using Oracle.
You can use substring. But not conventionally.
select name from students WHERE marks > 75 order by substr(name,-3), ID ASC;
ORACLE solution works for me
WHERE marks > 75
ORDER BY SUBSTR(name,-3),id ASC;
its not wrking bro
select Name from STUDENTS where Marks >75 order by SUBSTR(Name, (length(Name)-2), length(Name));
GOOD ONE BRO
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;
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 ;
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:
select name from STUDENTS where marks>75 order by substr(name,length(name)-2, length(name)), id;
you don't need to use length function.
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 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.
RIGHT FUNCTION NOT WORKING
Yeah, RIGHT function is MySQL specific. Use SUBSTR(string, start [, length]) for other databases.
SUBSTR(string, start [, length])
Can you pls tell ?
what is the use of right?
RIGHT function 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
in my code its showing that right is an invalid identifier and thus shows error. how to approach it next?
This is my solution (MySQL):
WHERE MARKS > 75
ORDER BY SUBSTRING(NAME, -3), ID ASC;
ASC is not needed.
desc is default
ASC is default
ASC is not the default. If you do not add ASC to the code it will not pass.
Why -3 in substring?
Order your output by the last three characters of each name.
That's what the -3 does here.
Instead of SUBSTRING(NAME, -3) use RIGHT(NAME, 3) for clarity. And ASC is not required like others said as it's the default for MySQL.
Here is Oracle solution from my HackerrankPractice repository:
WHERE Marks > 75
ORDER BY SUBSTR(Name, - 3), Id ASC;
Feel free to ask if you have any questions :)
this my solution for oracle
select name from students where marks > 75 order by substr(name,-3,3),id asc;
Nice Work.. out put came
SELECT NAME FROM Students WHERE MARKS > 75 ORDER BY SUBSTR(NAME,-3,3) ASC, ID ASC;
no need for the last 3, just substr(name, -3)
select name from students where marks > 75 order by SUBSTR(name,-3),ID ASC;
Seleact Name from Student where Marks >75 ordere by substr(Name,-3,3) asc, ID asc;
aabove query for Oracle, since we knew the ordering with last three letters of NAME field, -3 indicated the third character form last and 3 the length of character formt he starting position indicated.
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME,3), ID;
how come two different students have same id ,
id should be unique in the data
16 Devil 76
16 Priya 76
17 Evil 79
17 Priyanka 77
select name from STUDENTS where marks >75 order by substr(name,-3),id;