sonergonul For MS SQL Server, you can use RIGHT function like;
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME, 3), ID ASC;
Sposibo Similar to mine in SQL Server
select name from students where marks>75 order by substring(name,len(name)-2, 3) , id asc;
behbudovn You can just use RIGHT(Name,3) function
surajbhupal 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 ;
kennethpatierno 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
dhananjay_etl_t1 select name from students where marks>75 order by right(name,3),ID;
aashu24ahuja Can you please explain the syntax of substring using the above example (-2,3) ?
ericyim821 //-2 means the substring starts from the third last character of the city name //3 means the length of the substring is 3
nehabhattt According to me -2 means starts from the 2nd last character and -3 means start from third last character. please explain
adiptabiswas 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.
prashi0294 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
djjava7655 its not working.... remove ID ASC; the right query is **
select name from students where marks>75 order by substr((name),-3,3);
**
sanjeev_soni2006 select name from students where marks>75 order by substr((name),-3,3),ID asc;
vaibhav31595 could you please explain why you wrote (-3,3)??
namrataravat01 IT IS STILL NOT WORKING
mobi5592 SUBSTR(name), -3, 3) will not work. Replace that with SUBSTR(name, length(name), -2, 3)
abhiksamanta01 Thank you,it is working.
namrataravat01 WHAT EXACTLY THIS SUBSTR CLAUSE MEANS ????
jaypsingh_011 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.
ashishmangal_in1 it is not working because we can orderby only those column which are present in select clause and id is not ibn select clause
sanjeev_soni2006 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.
arulsinfo Instead of substrings , use simple Right(name,3),Id asc
architbhatia You can also use substring(name,-3,3)
lombahujur could you please explain " right(name, 3)" this part? what does right means?
sav_inder279 [deleted]
iddheepak It means select the last 3 characters.,
(select 3 characters starting from the right most end of the string)
lochek_alon @lombahujur, I have just came across this pretty interesting function. Here's where you can read about it.
AK103 The "right()" function is a embemnded function in MySQL?
kunalmunjal [deleted]saikishore_namb1 i got the same error .so, i removed distinct and it worked. i don't know why!!
swapnil_nayak 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.
HemanthKnP It works
mekalaaishwarya1 can we do this without using right
Thoros_of_Myr Can do that if you're using Oracle.
Bucci You can use substring. But not conventionally.
ferhatkurann FOR ORACLE
select name from students WHERE marks > 75 order by substr(name,-3), ID ASC;
avishek_nagi ORACLE solution works for me
SELECT name FROM students WHERE marks > 75 ORDER BY SUBSTR(name,-3),id ASC;
sudheerc710 its not wrking bro
mariusz_z2409 For Oracle: select Name from STUDENTS where Marks >75 order by SUBSTR(Name, (length(Name)-2), length(Name));
raj_yadav GOOD ONE BRO
ash127 why '-2'?
manojchowdary556 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;
jayadevanadhith1 what does the ID ASC part mean ?
ferhatkurann "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.
Bucci Order by ID in ascending order.
vaibhav31595 ascending by default it is in ascending order
anusuji what does id asc means??
srohith77 It sorts the ID column in ascending order..
yashrajarora0 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.
adiptabiswas wts right function now?>
jknaveenraj50 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 ;
nyomanyudis1995 Thanks
diegomsaiz Thank you very much!! I've been stucked with this.
kishanalagiya ya Right function is right for this query...
mark_plutowski substr also works
select name from students where marks > 75 order by substring(name,-3), id;
kashish_sahu_cs1 it's not working...
ltbatista30 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;
codertoaster 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.
kuttumiah 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.
shailyagarwal445 RIGHT FUNCTION NOT WORKING
kuttumiah Yeah,
RIGHTfunction is MySQL specific. UseSUBSTR(string, start [, length])for other databases.
aayushi2001 in my code its showing that right is an invalid identifier and thus shows error. how to approach it next?
jsabilla This is my solution (MySQL):
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY SUBSTRING(NAME, -3), ID ASC;
tvallday ASC is not needed.
OussamaWiss desc is default
tomliangg ASC is default
nikimaria87 ASC is not the default. If you do not add ASC to the code it will not pass.
vishal758 -_-
ramya14301 [deleted]
rishabhbatta Why -3 in substring?
tomliangg Order your output by the last three characters of each name. That's what the -3 does here.
kuttumiah Instead of
SUBSTRING(NAME, -3)useRIGHT(NAME, 3)for clarity. AndASCis not required like others said as it's the default for MySQL.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT Name FROM Students WHERE Marks > 75 ORDER BY SUBSTR(Name, - 3), Id ASC;
Feel free to ask if you have any questions :)
hanawa5291 this my solution for oracle select name from students where marks > 75 order by substr(name,-3,3),id asc;
himambasha Nice Work.. out put came
bhaveshchetwani SQL -
SELECT NAME FROM Students WHERE MARKS > 75 ORDER BY SUBSTR(NAME,-3,3) ASC, ID ASC;
moraesdno no need for the last 3, just substr(name, -3)
swatimishra_puri select name from students where marks > 75 order by SUBSTR(name,-3),ID ASC;
dharshusankar 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.
Devloger My MySQL Solution
SELECT NAME FROM STUDENTS WHERE MARKS > 75 ORDER BY RIGHT(NAME,3), ID;
debanjan1190 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
sumit_mobifly select name from STUDENTS where marks >75 order by substr(name,-3),id;
Sort 315 Discussions, By:
Please Login in order to post a comment