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.
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
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;
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.
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!
@@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.
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)
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.
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.
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
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.
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
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?
@@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.
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.
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.
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..
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.
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;
@@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
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....
@@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 :)
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.
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.
Excellent trick , congratulation from Misr (=Egypt)
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 ❤️
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
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.
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.
will this order by case work if we used distinct in select statement
How can i download the example data in the video ? Thank you
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
Thank you for the lesson. I learned something new.
Thank you
Great trick. Now how to sql make coffe please. Thx
I never tought that case clause can be use for ordering. Great tips!
Thank you
Very nice. Good presentation. Thanks.
Thank you
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?
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
You provide amazing content. Many thanks.
Thank you
Really good efforts to share ur knowledge.... ThnQ
Thank you so much
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.
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.
Good information, What about the plan execution?
Thank you. The query is optimized in terms of plan execution.
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
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.
Your voice haaye just went through mind
New subscriber here, please keep uploading sql question like this thank you
Thank you so much for your support!
Thanks.
How Can user display the values from dummy col created by case statement. ?
You can put the case in Select statement.
useful and intresting, thanks for sharing!
Glad it was helpful.
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 !
this just with SQL Server?
when I use MySQL?
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.
Its a good idea, thanks!
Thank you
Thanks Madam
Thank you
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
Its not working in workbench how can i do the same in workbench
What is it? This for teens?
Must be noted very thank full…
Glad it was helpful.
Really helpful.. Thanks!
Thank you
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 ??
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.
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
Great video!
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.
Super Mam 👌👌👌👌
Thank you
where to get real time project on sql ? anyone?
Very useful series
Thank you
Fantastic 👍
Thank you
Nice one
Thank you
Thank you! useful tip.
Glad that it was helpful.
really it is a good stuff !
Thank you
Smartly written query, I was upto case statement but forgot to add country-name in order by clause
Glad it was helpful
Thanks!
Thank you
Very nice 👍
Thank you
please make complete sql video
Sure. Stay tuned for further videos!
amazing
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
case statement are soo underrated
Do a Union
Yes, that is also another solution.
This is a nice trick
Glad it was useful
Did this many years ago.
Nice
Thank you
thanks
Thank you
super queries 👌👌
Thank you so much
its real simply super
Thank you
That was cool 😎
Thank you
Very useful
Thank you
perfect
Thank you
Clever.
Thank you
I want to learn some SQL topics so how could I approach you
Please do post your queries in the comments.
Neat.
Thank you
wow, just wow
Thank you
Unreal!
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.
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
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....
👏🏻
Thank You!
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 👍
Very useful
Thank You!