SQL tricky Query | Sort by one country always at top and others in ascending order | Custom sorting
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- In this video, we answer the SQL tricky Query - How to sort country names with one country name always on top.
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Sign up for a free trial of Coupler.io - The No code data integration tool
app.coupler.io...
Get USD 100 off Coursera Plus Annual Subscription
imp.i384100.ne...
Get 50% off Skillshare Annual Subscription with code AFF50.
Dates: 11/24 at midnight - 11/28 at midnight
skillshare.eqc...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.ne...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.ne...
IBM Data Science Professional Certificate
imp.i384100.ne...
Python for Everybody Specialization
imp.i384100.ne...
Google Data Analytics Professional Certificate
imp.i384100.ne...
Coursera Plus - Data Science Career Skills
imp.i384100.ne...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Facebook - / knowstartrainings
Linkedin - www.linkedin.c...
Email - learn@knowstar.org
The first thing came to my mind was doing a union. But I have to do table scan 2 times. Your solution is optimized and will definitely use from next time.
Glad it was useful.
yeah I think everyone's first response to this is going to just do a union but the response in this video is the one the interviewer is expecting you to say in order to get a higher grade
Yhee but sometimes a clear code is better I think anyway, but yhee nice solution anyway.
@Anaeze Somto Can you elaborate your question with an example ?
When you do a union, the order by is applied for the entire result set right. Not sure how you can achieve the desired ordering with union of two queries. I am missing something.
A simpeler solution, just add the different order cases by priority seperated by comma's. ORDER BY Country_Name = 'India' DESC, Country_Name = 'Singapore' DESC, Country_Name;
Or even shorter: ORDER BY Country_Name != 'India' , Country_Name != 'Singapore' , Country_Name;
Thank you for sharing your approach 👍
Does this work on all types of SQL ?
Yes, I think so, because a condition like Country_Name = 'india' resolves to 0 or 1, that is standard sql, and also chaining ordering condition seperated by commas is standard sql.
@@rutgerdekok1324 not working with T-SQL
I've never come across this question before, and my first thought was to add another column with a sub-select, but this solution was beautiful, thank you!
Thank you
Yeah and you are never going to come across it because it's not something you will ever need in reality, it's just done for the sake of it
@@alextiga8166 I wish you were right... I have this kind of sorting in my current project all over the place. It is used in things like getting right setting for something for user based on other settings of that user and some other things... Reading and trying to understand this is a huge pain in the butt.
@@alextiga8166 If you output the query results to csv, and you have a very large number of records that you would have to sort through in the csv to get certain records to be at the top, then this is method is useful
This was a very clever way to solve it. I had done it before using a union and excluding the first record but this is better in every way. Much thanks.
Thank you
Did you take in account that with UNION you are not certain of order of the records in the result set?
select expression from table union select expression from other_table is not granted to return records first from 'table' and then after that records from 'other_table'. In 99% cases it will but is not granted so you shouldn't count on it. If you really need to employ UNION, you should do following:
select 0 as ordby, expression from table
union all
select 1, expression from other_table
order by 1
BTW it is more efficient to use UNION ALL instead of UNION itself every time it is granted that both subsets are exclusive (which is the case)
Haven’t used Case statement in Order By clause, learnt new thing 💯
Glad it was helpful.
Oh yeah I use this method a lot. Good advice. This and ROW_NUMBER() are two of my favorite methods to selectively sort data.
Thank you
Excellent video and thanks for enlarging the font size so that it can be read from a tablet. Liked and subscribed 👍
Thank you so much for your support ❤️
We use techniques like this very often. Depending on SQL language this shorter version works too:
SELECT * FROM Country ORDER BY iif(Country_Name = 'India'),0,1), Country_Name
Since Singapore follows India, you can use this for the second case:
SELECT * FROM Country ORDER BY iif(Country_Name IN ('India', 'Singapore'),0,1), Country_Name
(You could also add DESC after the iif clause if the reverse was needed.)
The CASE statement is needed when the top items order is arbitrary. Using this technique will generate the same query plan in SQL Server as in the video and some languages don't support iif.
Thank you for sharing this.
True
I like this solution a lot! Great job! The only thing i would say is always alias your tables. it creates good habits and demonstrates the importance of it for when the query is more complex.
Thank you.
Excellent trick , congratulation from Misr (=Egypt)
I never tought that case clause can be use for ordering. Great tips!
Thank you
I spent four months telling the client”Sorry, I can’t sort like that.” Then I found this video and solved it in five minutes. I was trying to sort events by date where the soonest events displayed first, up to the future-most event, then show the events that already happened after that.
Everything is possible. Just extra steps. :)
Glad it was useful.
Lol, hope the client never sees this video. Well done on getting it done though.
Here's another way to do it.
select *
from Country
ORDER BY replace(Country_Name, 'India', '0')
Since all the other Country Names begin with a letter replacing India with a zero insures it will always be at the top.
A even simplier solution is to create an INT column (named for instance ITEM_ORDER) and add values to the countries you want to order first, and leave the other NULL. A similar CASE can be applied to guarantee that items NOT NULL appear first. In this way, if you want to change the country order, you don't need to change the query.
Thanks Madam
Thank you
Case statement in order by clause is not optimised way..instead write case in select and then do order by which will give faster result for large tables.
Great trick. Now how to sql make coffe please. Thx
Your voice haaye just went through mind
select country from table order by IF(country='India', 1,0) desc, 1 desc
You can just write "ORDER BY Country = 'India' DESC, Country" with same result.
Why to write desc ??
Thank you for the lesson. I learned something new.
Thank you
Mam, this query approach using CASE , seemed very unfamiliar to me. If you could give some details about it, it would be helpful
Nice video thankyou!
Case statement in this example would create a dummy column. In the end, data will be sorted based on the values of this dummy column along with Country Name column
Case works like an if then else. Will try to post a video explaining how it works. Thanks for posting your queries. Really appreciate it.
You provide amazing content. Many thanks.
Thank you
Hi This is good and really helped me alot, but I have one dobut can we do sorting like india and singapore in asceding order and rest country in desc order. Hope my question is clear to your. I want to output like this-
India
Singapore
Italy
Germany
France
China
Australia
Afghanistan
New subscriber here, please keep uploading sql question like this thank you
Thank you so much for your support!
Its a good idea, thanks!
Thank you
Very nice. Good presentation. Thanks.
Thank you
Can you make similar video in KQL Kusti used in Azure.
Also, a detailed video on joins vs union, when to use what in terms of Kql not SQL.
Sure. Will plan soon.
Interesting. I would have used a union to join the country I want with a list that has that country excluded.
Thank you. That is also another solution.
Super..... One question from my side pls slove it..... How to do sum with out using windows function?
Thank you. You will need to use group by and use subqueries to perform join on the grouped level.
@@LearnatKnowstar we don't want to use any Analytic Function.... Or Group by then?
will this order by case work if we used distinct in select statement
This was really useful. If you could also post an end to end datawarehouse pratical tutorial that would be really helpful
Thank you. Sure, will plan soon
Great video
It really helps, great ... Please share a video of join function, Left join right join.
Thank you
Here is the link for the video tutorial on SQL joins
th-cam.com/video/4HmERpUNjqY/w-d-xo.html
Can you please make a video on ..in stored procedure how o write a select statement in sp
Sure, will plan a video soon. Thanks !
Really good efforts to share ur knowledge.... ThnQ
Thank you so much
Must be noted very thank full…
Glad it was helpful.
How can i download the example data in the video ? Thank you
case statement are soo underrated
useful and intresting, thanks for sharing!
Glad it was helpful.
Nice one
Thank you
Thanks!
Thank you
Smartly written query, I was upto case statement but forgot to add country-name in order by clause
Glad it was helpful
amazing
Thank you
really it is a good stuff !
Thank you
Why you use two captial letters while using WHEN 'CO'untry_Name ?? SQL is non case sensitive for reserved words but it's a coloum name or is it some kind of convention?
It was just a typo.
@@4086pp no if you notice it she did it twice . Why would she make same mistake twice ?
Thanks for noting this. It is just a typo as suggested in other comments. The column names are not case sensitive.
@@LearnatKnowstar I did actually find situations where column names can be case sensitive - I think it is where the default collation of the database is case sensitive. In my case, I was working with a pre-existing database with collation Latin1_General_BIN.
If I want to display Singapore on last then what should I do
Great video!
Really helpful.. Thanks!
Thank you
Good information, What about the plan execution?
Thank you. The query is optimized in terms of plan execution.
Clever.
Thank you
Very useful series
Thank you
Did this many years ago.
Fantastic 👍
Thank you
perfect
Thank you
Thank you! useful tip.
Glad that it was helpful.
Thanks.
How Can user display the values from dummy col created by case statement. ?
You can put the case in Select statement.
Do you have any videos on using openjson
Not at this time. Will surely plan in the future
@@LearnatKnowstar thank you. I just started using them and it's pretty interesting.
Super Mam 👌👌👌👌
Thank you
thanks
Thank you
Very nice 👍
Thank you
Do a Union
Yes, that is also another solution.
Nice
Thank you
its real simply super
Thank you
This is a nice trick
Glad it was useful
super queries 👌👌
Thank you so much
Very useful
Thank you
Its not working in workbench how can i do the same in workbench
What is it? This for teens?
That was cool 😎
Thank you
wow, just wow
Thank you
When we might expect regex videos..other videos what have been doing are also great but i have little confusions over regex videos.. Please do as soon as possible..
Sure. We will plan something for next week. Stay tuned!
If you use regex to solve a problem, you now have two problems
please make complete sql video
Sure. Stay tuned for further videos!
Unreal!
this just with SQL Server?
when I use MySQL?
this is actually one of the worst solutions because you are mixing specific column values into the query. You can already see the complexity of maintaining such query when you extend it for a second country.. Also you have to generate specific "pseudorandom" priority values local to the query making them SPOF risk. Classic solution would be to have a priority table with those extra countries and outer join it with the countries table....
Neat.
Thank you
where to get real time project on sql ? anyone?
👏🏻
Thank You!
Hi Recently I had an SQL interview in which they asked me to find the latest created file per day by giving the sample input as:
Date of Creation FileName as 2 columns with data like
2022-04-08 3:00PM File 1
2022-04-08 3:10PM File 2
2022-04-09 2:00PM File 3
2022-04-09 2:10PM File4
So the required output that we need is on 2022-04-08 3:10PM the last record that created per day is File 2 and on 2022-04-09 2:10PM the last record that created is File 4 so we need both File2 and File 4 as output.
So please help me with the SQL Query how to print the latest created record per day.
select a.create_date,a.fileid from #file a
cross join #file b
where a.create_date > b.create_date and CONVERT(DATE, a.create_date) = CONVERT(DATE, b.create_date)
We can use rank analytical function for this type of queries.
Generate rank partitioning by date(use cast to convert) and ordering by created_datetime in descending order. The rank will always be one for latest record for a day. Then filter using the rank value using sub query.
select created_datetime, FileName from (
select f.*, rank() over (partition by cast(created_datetime as DATE) order by created_datetime desc)rnk from filedetails f)d
where rnk=1;
You can use rank function as suggested in comments below.
@@rajeshtorgal5191 If we cast the Datetime into Date Datatype then the time will be missed right then how will the system know to partition based on time and give it a rank?
@@saikrishnakatepalli8298 Buddy Hope you have run the query, please run it, you'll find the partitioned data and the allocated row numbers or rank please run it
A case statement and then order by two different conditions, no thanks, not very good for big tables. I'd be more inclined to use a replace, case statements get used so often and are rarely particularly efficient
ORDER BY REPLACE(Country_Name, 'India',0)
Thank you for sharing your approach 👍
I want to learn some SQL topics so how could I approach you
Please do post your queries in the comments.
Come on guys, how comes this is considered 'tricky' or 'clever', this is almost straightforward use of SQL
Sure, it’s a simple implementation. It is just an example of a custom sorting scenario !
@@LearnatKnowstar Oh, ok, with that assumption you can actually call it custom. For me and by definition of ORDER BY [expression[, expression[,...] it is just regular, not to surprise when I see ORDER BY (select ... from ... where ...) which is also as an example of SQL expression - still allowed :)
This is not tricky, more for absolute beginners
Tricky???
Select * from Country Order by case when Country.Country_name="Japan" then 1 else 2 end, Country.Country_name
And that's it. How is that tricky?
SELECT * FROM Country LEFT JOIN (
SELECT 'India' as 'Country_Name', 0 as 'order_first'
UNION ALL SELECT 'Singapore', 1 ) subquery
ON Country.Country_Name = subquery.Country_Name
ORDER BY ISNULL( subquery.order_first, 2147483647 ), Country.Country_Name -- TSQL ISNULL(), MySQL IFNULL()
Use a subquery to mimic a sparse column. If you use something like this in a lot of queries (or if you create a view on the ordered records) you can easily move the subquery into its own table.
Thank you for sharing this 👍