andrewstuart This question needs to be rewritten for clarity. "the possible minimum value" is completely unclear and doesn't give any idea as to the scope of the question. The shortest city name in the table is my best guess, but it shouldn't be a guessing game.
In fact, this question is so unclear and contrived I really think it should be removed.
dhayalram [deleted]
ireneassoni [deleted]
booties Agreed, it makes very little sense, given the context.
salilsharma [deleted]baisestar [deleted]
nghedberg For the sake of discussion, I'll offer a differing opinion: having worked (in the US healthcare system in particular) for quite a number of years now, I've found that the most difficult part of solving a problem usually isn't the coding itself, but rather figuring out exactly what it is your client wants. This question is pretty clear compared to some of the client requests I've received in the past (albeit much simpler) - this isn't so much a statement about your interpretation of the question (which I agree with), but rather the challenges you'll encounter when non-technical individuals ask you to help them get technical answers, the problem itself isn't clear in the client's mind, there's a language gap between teams, etc. If every question posed is textbook-clear during training, coders will be ill-served when they finally reach 'real world' problems, which are as much about language and definitions as mathematics. Is the language of this question problematic? Sure. But I'd consider the process of thinking through what exactly the questioner wants a learning experience - one that will make the next unclear question clearer - and then move on to the next challenge.
andrewstuart Yeah, I totally agree, but I'm not sure that's something a site like this can gauge well, as there's no back-and-forth with whomever asked the question. In situations involving ambiguous requirements, a direct clarifying question will work wonders, but I haven't seen a response or clarification despite a direct question. In the real world, if you give me unclear requirements and don't respond when I ask for clarification, you get the result of my best guess as to what you meant. Here, there are ambigious requirements and a very specific test case you must pass.
Tritonal Andrew, I'm not sure what world you live in. In mine the requirements are typically vague and often absent all together.
In this world, I agree, your point make perfect sense, however. I wrote to the author using the "Suggested Edit" button to express my concern. Perhaps that's something you may consider.
I believe this problem statement is one of the more clearly written ones, however.
manikantareddy_2 select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station));
harikabuddaraju1 compiler is throwing error(wrong answer) for this query...
manikantareddy_2 I DIDNOT GET ANY ERROR TRY ONE MORE TIME
reshantio30 THATS CODING WAS WRONG!!!!!
satvikdhandhania I am also getting error for using LIMIT statements.
Tesla91 [deleted]
AlAmin_Shaon for c#
select distinct top 1 city,LEN(city) from station order by len(city);select distinct top 1 city,LEN(city) from station order by len(city) desc;
carnag3kid7 This works, but I thought for a moment that in the case where a result set includes cities with the same length the query might fail if you don't do
order by citySay the result set looks like this:
- CITY
- Bb
- Aa
Then wouldn't
select distinct top 1 city,LEN(city) from station order by len(city)return Bb instead of Aa.
eellor So order by,
for min, .... order by len(city), city
for max, ... order by len(city) desc, city
marriyam_qureshi true for mysql.. not oracle... would u know how to write this for oracle? Thanks
deepeshjha17 use length() instead of len() in ms sql server.
Alexcharleswort1 LEN() is used in sql server, too.
manishpriyadars1 SELECT * FROM( ( SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) UNION (SELECT * FROM ( select CITY, LENGTH(CITY) K FROM STATION WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY)) FROM STATION ) ORDER BY CITY ASC ) WHERE ROWNUM=1 ) );
sadlife WORKING...BUT EXPLAIN
justinepdevasia /* FOR FIRST QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1;
/* FOR SECOND QUERY */ SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY DESC LIMIT 1;
kanchandhondge97 great. . .It works fine. .Thnks
yolo19962019 [deleted]
ziedzaydoun You don't need to put the first ORDER BY cause by default it's asc.
priyankagarg717 Try this:
Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1; Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
amc1140 why write "city asc limit 1" rather than just "asc limit 1"? It seems like this made a difference in getting the right answer, but not sure what change that made
REX_37 I think if you erase "city" from "city asc limit 1" then it will not maintain the alphabetical order.
cryptex_mk Yes, that is correct. That's why it happens.
ting_fai_cheung It works on MySQL , once I read your answer I know I thought too much, I just need to write two independent queries then it will output the answer. I don't have to just write one query to output this.
meerarevathy_99 what if out of all the cities with the same minimum/maximum length , we wanted to select the city that came last in the alphabetical order ?
vadlamudinaveen1 but i tried in this way:
select city ,length(city) from station where length(city)= (select min(length(city)) from station order by length(city) asc);
select city,length(city) from station where length(city)=(select max(length(city))from station order by city asc); the output is Roy-3 Amo -3 Lee -3 Marine On Saint Croix 21 but i got the logic of what you have written a thumbs up...hemant9494 Try This
(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY LIMIT 1) UNION (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY ASC LIMIT 1)
faisalrasid it works
erturkbusra34 Why LIMIT 1?
Abe10 So that only one value gets printed.
saxenasagar323 but we also have to print cities of same lenghth! so will it work for that?
h306412990 Since you just need to print one of the shortest or longest item.
rajkartik098 this doesn't work in oracle. is there any keyword 'limit' in oracle cause i have serched and didn't found any??
austindholmes Thats what im looking for bro. Beside "where rownum <= num" which wont work for this because of all the other stuff
Ru5h1bh Thanks man!!
bikkimahato123 not working
tanishaslewis I just added union to your code and put the select statements in separate brackets and it worked. Thanks!
(Select city, LENGTH(city) from station Order By Length(city), city asc limit 1) union (Select city, LENGTH(city) from station Order By Length(city) desc, city desc limit 1);
sahil_a2111 [Second Query]Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1;
Here city should be asc as we need to sort city in ascending order of their occurance. so final query Select City,char_LENGTH(City) from Station Order By Length(City) desc, City asc limit 1;
dragonachu This is more clean. Thanks!!
(Select City,char_LENGTH(City) from Station Order By Length(City), City asc limit 1) union all (Select City,char_LENGTH(City) from Station Order By Length(City) desc, City desc limit 1);
arora90_r Same
virenderkumar try in oracle. if you try in SQL then its not work. It will work only in oracle plateform;
raghu_icecraft -- This is for oracle select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station));
-- For MySql
select CITY, length(CITY)from station order by length(CITY), city limit 1; select CITY, length(CITY)from station order by length(CITY) desc, city limit 1; `
satvikdhandhania Oh! You're right. I didn't change the SQL system. Thanks!
mehvishsyed97 can u plz explain this thing?
manis_lohani a simple solution: select small.a,b from (select city a,length(city) b from station order by b, a)small where rownum=1 union select big.a,b from (select city a , length(city)b from station order by b desc ,a desc)big where rownum=1;
kushagraahuja Can you please explain this?
skitram select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as a order by length(city),city limit 1) as b union all select city,length(city) from (select city , length(city) from (select city from station order by length(city)) as c order by length(city) desc,city limit 1) as d
should work for mysql
abhayupadhyay There is nothing to explain in oracale to select selected number of raw we used ROWNUM and in MUSQL we use LIMIT
ANit_AgGarwal IN BIGGEST A SHOULD BE SORTED ASC BECAUSE WE NEED THE FIRST ENTRY NOT THE LAST FOR SAME LENBGTH CASE
lobato_diana wrong. In the last query should be ordered by b desc, a ASC as we have to take the first alphabetically. But the table seems to have only one city with the largest string so you get luckily the expected results.
jasmine_kaur select CITY, length(CITY) from station order by length(CITY), city limit 1 select CITY, length(CITY) from station order by length(CITY) desc, city limit 1
i have written this expression in mysql ut its giving error:- ERROR 1064 (42000) at line 4: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select CITY, length(CITY) from station order by length(CITY) desc, city limit 1' at line 2....what is the problem? pls rectify..pls help!!
dragos_nae You need semicolons after each query. Your code should look like this:
select CITY, length(CITY) from station order by length(CITY), city limit 1; select CITY, length(CITY) from station order by length(CITY) desc, city limit 1;
_imuv15 select city, length(city) from station order by length(city), city limit 1; select city, length(city) from station order by length(city) desc limit 1;
agunfd THIS CODE IS NOT WORKING FOR ME
soniabhi1 For Mysql select city,char_length(city) from station order by city,char_length limit 1; select city,char_length(city) from station order by city desc,char_length desc limit 1;
garciaantoniher1 It was easier than I thought, good job
kammaprasannasa1 WHY U HAVE WRITTEN CITY LIMIT 1 AND DESC CITY LIMIT 1 IN DOWN LNE CAN U EXPLAIN
Gillu_Hacker wrong doesnt work for mySql
npeterscampbell SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY), CITY LIMIT 1; SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC, CITY LIMIT 1;
varunbankadgr8 what does the LIMIT keyword do?
Duc_Shuu The LIMIT keyword limit the number of record as you expect
diepngocnguyen_1 You can learn more here "https://www.w3schools.com/sql/default.asp"
welcometodevan Limit will help you to select only particular number of entries e.g. 1 in this case
johnty05 suppose there are multiple cities with min length ( as it is in this case, 3 cities) , your 'limit' displays the number of cities as per set by you.
omkar_halikar14 I had a doubt,order by clause is applied only on len of city name ,does sorting of same length cities alphabetically happen implicitly . If it did happen by default ,what would be the query if we wanted it to be in desc order alphabetically
adityagesh I don't think it would work if the there are multiple cities with minmum length for eg : Ame Aao Azi
You might have to sort it first.
kritika2017patel select city , length(city) from station order by length(city) desc , city desc limit 1; select city , length(city) from station order by length(city) asc , city asc limit 1;
S_hivam in the first one , -> city asc limit 1; should be there
Damon_Gao I am also confused about that. we should limit answer to 1 and how can we sort it by alphabetic
saumya_k20 Its by default.
Logan_47 why we can't just use limit why we are using city limit 1???
ajayverma1244 same question here also if we dont use city limit it wont give answer
sinchan18_khatua What is the use of limit?
johnty05 add ; after the first query
abhy_m_51092 just use UNION between the two queries to combine the result
manikantasai164 Oracle code is not working
a_anand_IT110 Can you just explain the desc part. I understood the whole part but didn't get this particular part
ORDER BY LENGTH(CITY) desc,
khurshidrpvv [deleted]jacob0306 @adityaanand12345 normally it will be sorted in ascending order when you use ORDER but in this case we want to get the longest name so we use desc to sort it in descending order so we can get the first one.
viveknair1997 The desc part just orders the query in descending order.
abhi_kadam62 by default order by sorts in ascending order, U need to specify desc so that it does it in descending order ie A-Z!!
AzizulAsif A-Z is ascending, and Z-A descending, I think.
kumarikajaldell order by clause used for sorting purpose like if you want to print something in ascending order or decending order , by default order by clause print ascending but if you want print name in descending order that time we use desc to sort
rushabh_devalia ------------------------oracle---------------------------- select city,length(city) from (select city,rownum as rid from (select city,length(city) from station order by length(city),city)) where rid = 1 or rid = (select max(rownum) from station);
yasmin_elderiny could you please explain this query ?
sameer_sinha Great solution
benjamindiaz3141 this answer is similar to the MySQL solutions but because LIMIT doesn't exist in Oracle we use rownum as rid to select within the order of length of city and city, we then say rid is 1 or the maximum rownum of station.
anitharamya can u plz explain this answer....
gruszecki_adam Can U tell me how work a.* ?
nathanielgibson1 The a in the a.* is an alias. He used the 'a' bolded below to give the name to the select statement in front of that point so he could refrence it in the beginning. The a.* portion is refrencing all of the rows and columns in the 'a' query.
select city,length_city from (select a., rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count() from station));
itsamanraj315 plzz explain Y u hav use city before limit 1 in D query???
lakshyauttrani17 so that you can limit the value of the city column to 1 as you only need one output in each case
anitharamya k..
vnc_ghost1998 what is the use of limit?
Rajkumar97 explanation for oracle pls....!!
sonalisokal [deleted]shashikumarverm1 thanx
shashikumarverm1 YOU query is absolutely right...
vigneshvinoth96 Thk u sir...
imhulk25 In Oracle, what does that select a.* do?? what is a here?
sumeetbhikule2 please explain the code as a beginner i didnt get it..
ramana_hadoopdev can u explain this one
cinderella_and_1 Why do we have to add city in front of limit?
divij_berry1999 I didn't understand the sql one. specially the desc?
AlAmin_Shaon its for descending sorting.
jainsunny0507 sir,what is use of city limit ? i am beginner....
hwb92516 Select * FROM (select CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY), CITY LIMIT 1) AS T;
Select * FROM (select CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY CHAR_LENGTH(CITY) DESC, CITY LIMIT 1) AS T;
"order by... limit x" means giving the first x numbers of line with this order
CHARITHA3095 it is not working
lobato_diana that cannot be the right query as you can have more than one city with the longest string and you are taking out the last one alphabetically ordered. Luckily the table seems to have only one city with the longest string, so you get the expected results. But that query does not follow the rules.
shivam18401840 This will not work if we have more than one cities of max length.
Joshua_Murithi its correct.
albreight86 -- another way for oracle:
select t1.* from (select City, length(City) from station order by length(city) desc, city) t1 where rownum <= 1 union select t2.* from (select City, length(City) from station order by length(city) , city) t2 where rownum <= 1;
Nobody_me [deleted]
sonshicyu1 With SQL I think you should use 'ORDER BY LENGTH(CITY), CITY' since they only choose the city that comes first alphabetically.rinindya_nindy thankyou, :)
mdkamranasad can u please explain , what does "select a.*, " do
kammaprasannasa1 WHY U HAVE WRITTEN CITY LIMIT 1
nimmymary_john20 For Oracle one, the query, though good, would not fulfil the logic demanded in the question. the question asked to get the minimum and maximum length of the cities along with the city name and if two cities have the same length , the output should be the first city in ascending order. By that logic, if in case there were two records having maximum city length, your query will fetch the last record, the the first city in ascending order withing maximum city length. Would like to know your opinion
G_vesh The MySql solution is quite easy while the oracle one is confusing me. Can you please explain how the oracle solution is working.
ziedzaydoun THANKS
mamidalashravani ya,i too agree..the complier is showing me as wring answer for this query
pooja_pingale22 coding is right, try it in oracle
azharudheenpn select city, length(city) from station order by length(city) ASC limit 1; select city, length(city) from station order by length(city) DESC limit 1;
showing error
vladimir37 There is a simpler way.
aakash_mishra201 Code wont work for following city names data: ABC PQR QXY PQRS DEFG
you code will give: ABC 3 PQRS 4
But we need ABC 3 DEFG 4
devendermahajan For first row sort the table by city length in ascending and the than by city name also in ascending order limiting the output to 1 For second row sort the table by city length in descending and the than by city name in ascending order again limiting the output to 1.
harsh07 Can't we do this in one single query rather than 2?
nirajkmr28 SIR, THANK YOU.
harish_patnaik this query is for oracle
dhiraj_thakkar This seems to select the alphabetically last city for max length. The test does not catch this as it has only one city with max length. I modified this to:
select city, length(city) city_length from (select city, length(city) city_length from station order by city_length, city) where rownum = 1 union select city, length(city) city_length from (select city, length(city) city_length from station order by city_length desc, city) where rownum = 1;
mohdsakibimran plz explain query and also expalin a.*
asrinathr It works fine. But could you please explain your query
adamlevine217 what if longest name has multiple entries? for eg. rownum has values 1-500, but 496-500 are same. Task in this case is to pick 496th entry(alphabetical), but solution here picks 500th value not the 496th value.
ericashi35 I didnt fully understand the code. What is select a.*, rownum r from() part doing?
alonsogutierrez1 This answer is good enough; the table has only one city name with a distinct length value of 21, however if there were two then this query will incorrectly select the last one of the two in alphabetical order.
Devishwash what a.* do ? and what is a here ?
snath402 It is perfectly worked
gudepusaijagann1 COMPILER THROUGH AN ERROR
pateljinal110 select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1;
this will work
gkrishan751 very nice;
juanx002 for SQL Server:
select top 1 city, len(city) from station order by len(city), city;
select top 1 city, len(city) from station order by len(city) desc, city desc;vadlamudinaveen1 this will work because we are not ordering the elements by their length we are doing it by the aplhabetical order so it does it.....and in the first query the result obtained will be having some set so we must pick out the top one...cool...
cdvdgzb Thank you.....worked for me!!
cassin select top 1 city,len(city) as shortest from station order by len(city),city select top 1 city,len(city) as longest from station order by len(city) desc, city
You dont have to "city desc" the second query because they want to display the city alphabetically.
Mads647 He ordered DESC in the second query to get the maximum length. By ordering it ASC in the forst query, he got the city with the minimum length.
Adibas03 Andrew, were you finally able to solve it?
tridevg [deleted]salilsharma [deleted]
AllisonP In a real-world scenario, you meet and/or communicate with the client about their spec to confirm and clarify that you understand what they are asking for. After these issues have been addressed--in a real-world scenario--you often even enter into a clear contract with said client about precisely what will be delivered.
Yes, this problem was badly worded and it wasn't hard to figure out what they wanted based on the given examples. Yes, developing the ability to intuit what your client needs out of the mess of what they think they need is an important life skill; however, this is not real life. This is a website that markets themselves as a tool for teaching, training, and exercising code skills. The exercises on this website are a professional business product, and the users act as the client. There is no mode of instant contact with the problem's poster. It is more than reasonable for users to expect concise problem descriptions and accurate solution testing, just as you would expect from exercises in a textbook.
Odiumediae Very well put.
Marcio_courense1 The same reply over and over again, minus point.
BansheeGhoul With all the 'real world' example you gave , you should understand that this problem you are giving is in the basics tab... so those who try this are actually learning the stuff, provided the fact that you don't even give summary tutorial for these problem .... let us know how can one learn (which is expected from these basic tutorial atleast).
onthelineguy Agree except one can ask clarifying questions from a real life client. Trying "SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY);" is simplistic but under certain assumptions could also be correct. In other words, logically the question can be proved ambiguous and there is no recourse for clarifying questions.
john48677 [deleted]
khiryz92 I appreciate the real world input and definitely agree. I feel as I practice I should become more versed in both sides, the business and technical and this site is providing that free.
GeekyCode Well said man. I cant agree more.
vigneshad true statement and well said
shivlahane Nice approach to look at problem!
souvikmodak SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY) WHERE ROWNUM = 1; SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY) WHERE ROWNUM = 1;
sebi_essling Oracle provides the FETCH FIRST mechanism which allows you to select n rows with order by. This is much better then SELECT * around your whole statement.
For example:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY FETCH FIRST 1 ROWS ONLY;
Note: The Orderby is executed before we fetch the first row.
wittrup Yes, this came with Oracle Database 12c (https://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS113).
SELECT * FROM V$VERSION;
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production
adam_allard I get an error when trying this:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY FETCH FIRST 1 ROWS ONLY * ERROR at line 1: ORA-00933: SQL command not properly ended
virenderkumar this is not a right query or syntex. please do read about syntex of "ORDER BY" and also on "HAVING" clause.
adam_allard the syntax for
ORDER BYis correct. If I run the exact same query without theFETCHclause then the query works just fine.Going off these docs, my query with
FETCHshould work just fine. https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1virenderkumar Try this in "oracle" envoirment:
select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station))
adam_allard Yes I'm using Oracle DB, as my original post stated.
Thank you for your answer, but I am wondering why Oracle's
FETCHclause is not working here...faiza_fiz2015 Can you explain this query?
aravind_bijjala1 if there are more cities with length 21 this doesnt work
srishti_me Semicolon is missing
kumarikajaldell can you explain this code
arpitmalviya5nov NOT WORKING
dheeman6588 simplest answer : (select city,(length(city)) from station order by length(city) desc ,city limit 1)
karwa_Yashu Throwing as wrong answer
anandvidvat what is the need of 'city' after lenght(city) in the statement? similary after length(city) desc, city.. why ? SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY) WHERE ROWNUM = 1; SELECT * FROM(SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY) WHERE ROWNUM = 1;
MohammedHaroon AFTER ORDERING FOR LENGTH IN ASC ORDER, YOU ALSO NEED TO ORDER THE CITIES ALPHABETICALLY.
nandakishore8031 If you dont mention any order, by default is Ascending order. So that query is right.
rahulpal1321996 can you explain how it is working without the min and max ?
sandeep_sappal you are ordering it and limiting it to a single row hence your aim is fulfilled.
sandeep_sappal select CITY,length(city) from station WHERE ROWNUM <= 1 order by length(city),CITY desc; select CITY,length(city) from station WHERE ROWNUM <= 1 order by length(city) ASC;
why this code is incorrect?
Vincho EVEN THIS QUERY IS GIVING WRONG ANSWER....SOMETHING IS WRONG
SELECT CITY, LENGTH(CITY) FROM STATION WHERE ROWNUM <= 1 ORDER BY LENGTH(CITY);
SanjeewaDias SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) limit 1; SELECT MIN(CITY),LENGTH(CITY) FROM STATION GROUP BY LENGTH(CITY) ORDER BY LENGTH(CITY) desc limit 1;
this code was a modification of onthekineguy
rohanparkar20 thank you it works for me
msidargo could you tell me how "MIN(CITY)" works? How could a STRING use MIN function?
kowalczyk_krzys1 If you have a rownum (i.e. Oracle) you can't use it in one query with order by, the rownum is assigned before order by is applied. You can wrap it in another query and use rownum there, or use row_number, or switch db.
sebi_essling The problem is that rownum limits your rows before order by is executed. Use FETCH FIRST mechanism i described in comment above.
sebi_essling [deleted]
tuyendev If you want use rownum, you should rewrite code like this
SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY)) WHERE ROWNUM <= 1;
dhananjayt771 samircity This is the best answer for MYSQL, thanks
samircity I customised it for MS SQL Server as below:
DECLARE @V1 VARCHAR(MAX)
DECLARE @V2 VARCHAR(MAX)
DECLARE @V3 VARCHAR(MAX)
DECLARE @V4 VARCHAR(MAX)
SET @V1 = (SELECT MIN(LEN(CITY)) FROM STATION);
SET @V2 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V1 ORDER BY CITY );
SET @V3 = (SELECT MAX(LEN(CITY)) FROM STATION);
SET @V4 = (SELECT TOP 1 CITY FROM STATION WHERE LEN(CITY) = @V3 ORDER BY CITY );
bharath_narsa select top 1 CITY,min(len(CITY)) len from STATION group by CITY order by len asc; select top 1 CITY,max(len(CITY)) len from STATION group by CITY order by len desc;
pradeepsripada Thank you
idusortus Just getting in to SQL Server and was poking around for a clear example of how to implement variables - thanks for this! Seeing this implementation helped me connect some dots.
lvrosa Without use of variables (MySQL):
SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = ( SELECT MIN(LENGTH(CITY)) FROM STATION ) ORDER BY CITY LIMIT 1; SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY) = ( SELECT MAX(LENGTH(CITY)) FROM STATION ) ORDER BY CITY LIMIT 1;
ssheu Hi, I'm wondering - why does WHERE LENGTH(CITY) = MAX(LENGTH(CITY)) not work? I get a "Invalid use of group function" error.
mchuong Double check that you're not using Oracle, but that you're using MySQL
aashiq_baig That's because you need to use 'having' when you use a group function like max or length
claritywhisk SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY LIMIT 1;
Logan_47 what the is use of writing city before limit???
Ruch_ka It works, but also add city to order by list
select * from (select city, length(city) from station order by length(city), city) where rownum <= 1; select * from (select city, length(city) from station order by length(city) desc, city) where rownum <= 1;
johnasaville Simple and elegant. Nice.
lalithanand303 IT WORKED FOR ME ORACLE
sumitit78 I am also agree with you.
salilsharma [deleted]salilsharma [deleted]oscarooko SELECT TOP(1) CITY, LEN(CITY) AS CITY_LEN FROM STATION GROUP BY CITY ORDER BY LEN(CITY) DESC;
SELECT TOP(1) CITY, LEN(CITY) AS CITY_LEN FROM STATION GROUP BY CITY ORDER BY LEN(CITY) ASC;
singhjagdeep113 This is the right query for oracle select city,length_city from (select a.*, rownum r from (select length(city) length_city,city from station order by length_city, city) a) where r in (1,(select count(*) from station));
crujzo Can u plz explain it
ernestoal66 select * from (select city , length(city) from station order by length(city) desc, city desc) where rownum = 1 union select * from (select city , length(city) from station order by length(city) asc, city asc) where rownum = 1;
sugandh_kverma Thank you ... It worked for me.
poonamnayak604 Thank you. its working fine.
manishc30 I think the question is straight forward. They have also given the example. Check the example and you will be able to understand.
msorquiano HOW TO COUNT CHARACTERS IN A STRING??
manikantasai164 You can use length
test71213 SHUT YOUR MOUTH BRENDA
kkqnguyen This question is pretty clear it wants you to query for two cities, one with the longest name and one with the shortest name, and if there is a tie for either then use the city name that comes first alphabetically.
If you have to resort to guessing then there is an issue with the query methodology.
This query can be solved dynamically using MIN and Max, or even the ORDER BY clause with LIMIT.
dariushsieh17 select * From ( select CITY, length(CITY) from Station order by length(CITY) desc, city ) Where rownum=1;
select * From ( select CITY, length(CITY) from Station order by length(CITY) asc, city ) Where rownum=1;
--- Oracle you need to use rownum=1 to limit to one observation. You need to first sort everything within the subquery and then use rownum to limit it to 1 value.
in007 THNAK U
Deepjyoti120281 yup i agree with you
muhammadnurakbar select city, len(city) FROM STATION where city in (Select top 1 city FROM STATION order by len(city), city ) or city in (Select top 1 city FROM STATION order by len(city) desc ) ;
done
rhushikesh1 at the end just add order by city ;
chinnareddy [deleted]navin_thamindu Indians..
code_raja (SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MAX(LENGTH(CITY)) FROM STATION) ORDER BY CITY ASC LIMIT 1) UNION (SELECT CITY,LENGTH(CITY) FROM STATION WHERE LENGTH(CITY)=(SELECT MIN(LENGTH(CITY)) FROM STATION) ORDER BY CITY ASC LIMIT 1)
wasitshafi [deleted]wasitshafi @andrewstuart Sorry i don' t agree...infact its one of the good queestions. see my submission.
- select * from (select city, length(city) from station where length(city) = (select min(length(city)) from station) order by city) where rownum = 1;
2.select * from (select city, length(city) from station where length(city) = (select max(length(city)) from station) order by city) where rownum = 1;
ajaydalavi99 MY SQL SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ,CITY LIMIT 1; SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC ,CITY DESC LIMIT 1;
LeCodeManiac Please instead of compiler just saying wrong answer as a reason it should show us the expected output, it gives us more clues when the question isn't explanatory enough
chadwalt These guys should work upon that. Cause it annoying.
AlexiVossos Answer in MS SQL SERVER:
SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY),CITY; SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC,CITY;
mitaraya11 I think it work only for ms sql sever2012 but won't work for oracle, Is there anything like "TOP" in oracle.
vi_nastya in oracle you can use WHERE ROWNUM = 1 instead
xiaoyuexiao0325 Try this:
select city, length(city) from station order by length(city) asc, city asc limit 1; select city, length(city) from station order by length(city) desc, city asc limit 1;
Danielapps ive changed the code and it still works. select city, length(city) from station order by length(city) asc, city limit 1; select city, length(city) from station order by length(city) desc, city limit 1;
why did you put 'asc' limit 1
Dhannesh we can also write this query in MySql as select city, length(city) from station order by length(city) limit 1; select city, length(city) from station order by length(city) desc limit 1;
chandrakishor00 'asc' stands for ascending order. The query works fine even when 'asc' is removed from the query because the default order for string is alphabetical(first words starting with 'a' then 'b',...) which is exactly what this question demands.
shenzhuo YES,very helpful!
crazykingfisher sir can you explain this code and also what's the use of 'limit 1'? thanks in advance
pgopi no, instead you can use rownum in oracle
pedrokarneiro TOP in Orcale will be in the WHERE clause AS "WHERE ROWNUM = N". This one is a big catch because each SGBD works differently with the TOP concept. For better details, see http://www.w3schools.com/sql/sql_top.asp All the best!
pedrokarneiro -- Oracle solution -- SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY) WHERE ROWNUM = 1 UNION SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY) WHERE ROWNUM = 1;
deepshashank SIR CAN YOU EXPLAIN THIS PLEASE
dhananjayt771 
return1 PLease explain this
saurabh250987si1 Thanks dhananjay.. it worked :-)
dwayneuni I second it. Can you please explain this.
pedrokarneiro Hello Deep...
This query is about unifying the results of two subqueries, so we use the UNION clause.
The WHERE ROWNUM = 1 in both subqueries is there in order to do a "TOP 1". That is the way this is done in Oracle. It is not very didactic but that's how Oracle undestands it. A simple explanation for that is here: https://stackoverflow.com/questions/3451534/how-do-i-do-top-1-in-oracle
The reason of what I have put in the ORDER BY, I think it is due to the statement of the challenge, but I'd have to look into it again.
All the best!
JagseerSingh thanks sir it helps
dwayneuni may you please explain this to me to on how to understand it.
nalia_nalia Thank you! None of the previous codes worked and I really has trying to do this for 3 days already, while for my codes there was always the "SQL query not ended properly" error.
sivalakshmi YES IN ORACLE TOP IS PRESENT..
daniil This is two different queries. Does it accept this?
pattadevinayak01 [deleted]SiddhuKR Answer in MS SQL SERVER:
Good Approach but when we are asked to display as single result set . This works
-- creating sample STATION table and inserting value create table STATION(id int , city varchar(50)); insert into STATION(id,city) values (1,'madras'),(2,'Salem'),(3,'Trichy'),(4,'Ambur'),(5,'Velur');
-- CTE table with extra colum having city string length with AddedLengthCol as ( select city, len(city) as lencount from STATION )
-- Used Rank_Dense to sove it . select s.city , s.lencount,rank1 from (select city , lencount , DENSE_RANK() over (PARTITION BY lencount order by city ) as rank1 from AddedLengthCol) as s where s.rank1=1 and ( s.lencount = (select max(lencount) from AddedLengthCol) or s.lencount = (select min(lencount) from AddedLengthCol))
SiddhuKR /Better Solution SQL SERVER:/ select min(city),len(min(city)) from STATION union select min(city),len(min(city)) from STATION where len(city) =(select len(max(city)) from STATION)
beverand I went with the CTE, but it doesn't work when I run it here. On SQL Server it returns the answer properly: with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen desc; with n as (select city, len(city) citylen from [SalesLeads_New].[dbo].[ba_car_cooper] group by city )select top 1 city,citylen from n order by citylen asc
yangzii0920 [deleted]yangzii0920 this one works well, though I didn't know it could be 2 queries...tricky!
leonardo_dcbv That was my answer too. Is there a way yo improve it?
mandhardpes This is correct answer for MS Sql Server. Thank You.
maybe_mae THANK YOU
I had gotten it almost right, but I had no idea I was supposed to actually use Order By to get the lowest and highest numbers.
marinskiy Here is Oracle solution from my HackerrankPractice repository:
SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY), CITY) WHERE ROWNUM = 1 UNION SELECT * FROM (SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC, CITY) WHERE ROWNUM = 1;
Feel free to ask if you have any questions :)
shinjinidatta25 Hey thanks for this.
csugirtha Many thanks, but why is ROWNUM=1 outside the select statements? Its not clear.
haastyle It is outside as you want to return just rownum = 1 from the inner query. The inner query is getting all the city and lengths, then orders it, the outside query is selecting all and then grabbing rownum = 1 from the inner query. If you do rownum = 1 in the inner query it will return the first row before it does the order by clause so you will not get the desired results.
https://www.periscopedata.com/blog/sql-query-order-of-operations for more details
ashita_gaur Why ROWNUM =1 is used here ?
VJ5189 Here is the answer in oracle without union and in single sql statement.
select MIN(city) , length(city) from station group by length(city) having length(city) =(select MIN(length(city)) from station ) or length(city) =(select MAX(length(city)) from station ) order by length(city) ASC;
poulami_dash it worked....
poulami_dash is there a way to do in mysql?
android991 Yes. You also could avoid HAVING keyword and use UNION
( SELECT CITY, LENGTH(CITY) AS `length` FROM STATION WHERE 1 ORDER BY `length` ASC, CITY ASC LIMIT 1 ) UNION ( SELECT CITY, LENGTH(CITY) AS `length` FROM STATION WHERE 1 ORDER BY `length` DESC, CITY ASC LIMIT 1 )
alsaalsa I used this, but got an error
varinderpatwal working here
varinderpatwal ( SELECT CITY, LENGTH(CITY) AS
lengthFROM STATION WHERE 1 ORDER BYlengthASC, CITY ASC LIMIT 1 ) UNION ( SELECT CITY, LENGTH(CITY) ASlengthFROM STATION WHERE 1 ORDER BYlengthDESC, CITY ASC LIMIT 1 )
dnj97metalhead select LENGTH(city) as `length` from table station order by length asc, city asc limit 1) union (select length(city) as `length` from table station order by length desc, city asc limit 1);
dipali_shatavara Select top 1 city, len(city) from Station where Len(City) =(SELECT Min(Len(City))FROM station)order by city ASC
Select top 1 city, len(city) from Station where Len(City) =(SELECT Max(Len(City))FROM station)order by city ASC
ranjan07 ASC is not required.....by the way nice one
uit166 IT'S INCORRECT if there were more rows with max length = 21 this is not generic it will fail.
jena_pritam234 If we remove order by also it will work
alsaalsa this works thanks
[deleted] Select City, Length(City) From Station Order by Length(City), City Limit 1;
Select City, Length(City) From Station Order by Length(City) DESC, City Limit 1;
Naldershof Annoyingly enough the "equivalent"
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 LIMIT 1; SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 DESC LIMIT 1;
Doesn't work correctly since there's multiple entries having the same value for the shortest string. In a production setting you'd probably want to extend this answer to include a more complex order by.
lorbicki SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2, 1 ASC LIMIT 1; SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2 DESC LIMIT 1;
rajapriyansarav1 Can i know why order by 2,1 is given
lorbicki Yes, when you type:
SELECT CITY, CHAR_LENGH(CITY) FROM STATION
You mentioned two columns: CITY (1) and CHAR_LENGTH (2)
So, when you type:
SELECT CITY, CHAR_LENGTH(CITY) FROM STATION ORDER BY 2, 1
You're saying: order first by CHAR_LENGTH (2) and after that take this results and order by CITY (1)
schachnerje Thanks for the answer. Why is it that when you type ORDER BY in:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2, 1 DESC LIMIT 1;
it does not work the same way as the ascending list? (It suddenly disregards sorting alphabetically)
andy_slowmo Because 'ORDER BY' function now has two parameters which are:
- '2' - And will first order the results by this one
- '1 DESC' - So if there are results with equal 'LENGTH(city)' after the first ordering it will order these (the equal length ones) by REVERSE alphabetical order.
*Note: The DESC only applies to the 1 argument/parameter in front of it NOT to all of them;
Further note: I am not a pro with MySQL but I've tested this a bit and this is what I noticed;
charlesgoh @andy_slowmo Looks like you are right. So it seems if we wanted to be very detailed we could do:
SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2 ASC, 1 ASC LIMIT 1; SELECT CITY, LENGTH(CITY) FROM STATION ORDER BY 2 DESC, 1 ASC LIMIT 1
That way the code looks more representative of the results
xiaoxia_pan Can I add a 'UNION' on your code, but it not working...just wondering
bishtkrishna7094 for largest city why not this? select city, length(city) from station order by 2 desc,1 limit 1;
karthikapanneer1 [deleted]
kev_rocky This is the cleanest query for this problem. Short and to the point!
rishaba My SQL Version:
select city, length(city) from station order by length(city),city asc limit 1; select city, length(city) from station order by length(city) desc limit 1;
ia_ishikaagarwal thanks it worked for me.
hous61 can you please tell me why do you add the limit 1 part?
JessePattinson The queries we write for this challenge are meant to return the city with the longest name and the city with the shortest name (with city name sort order as a tie breaker). The "limit 1" here is so that each query only returns the first result, thus returning the longest and shortest city names.
thebick This is marked "Easy"? This is the convolution that I had to go through with Oracle:
(select * from (select city, length(city) from station where length(city) = (select max(length(city)) from station) order by city asc) where rownum = 1) union (select * from (select city, length(city) from station where length(city) = (select min(length(city)) from station) order by city asc) where rownum = 1);
jcaruna select top 1 city,len(city) from station order by len(city),city ;select top 1 city,len(city) from station order by len(city) desc,city asc ;
It worked for me in sqlserver.
Sort 2012 Discussions, By:
Please Login in order to post a comment