Just a day ago, while working with some inventory related projects, I faced one interesting situation. I had to find TOP 1 and BOTTOM 1 record together. I right away that I should just do UNION but then I realize that UNION will not work as it will only accept one ORDER BY clause. If you specify more than one ORDER BY clause. It will give error.
Incorrect T-SQL Script which will give error.
USE AdventureWorks GO SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID UNION ALL SELECT TOP 1 SalesOrderDetailID FROM Sales.SalesOrderDetail ORDER BY SalesOrderDetailID DESC GO
ResultSet:
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword ‘UNION’.
As I was in rush, I wrote something very simple with using UNION code, which will give me similar result.
Correct SQL Script which will give correct output.
Correct Script Method 1:
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 MIN(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT TOP 1 MAX(SalesOrderDetailID) SalesOrderDetailID
FROM Sales.SalesOrderDetail)
GO
Correct Script Method 2:
USE AdventureWorks
GO
SELECT *
FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID)
OR
SalesOrderDetailID IN (
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC)
GO
I am sure there are more alternative methods to do the same, I encourage my readers to share their opinion and comments along with code. If your code is good, I will publish on this blog with your name.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
I liked your approach to the problem. I like any solution that works and is very easy to understand by anyone.
Don’t make yourself worry about other possible solutions.
How about this:
SELECT A.* FROM (SELECT TOP 1 * FROM DBO.SomeTable ORDER BY SomeTable.SomeKey ASC) A
UNION ALL
SELECT B.* FROM (SELECT TOP 1 * FROM DBO.SomeTable ORDER BY SomeTable.SomeKey DESC) B
and if you want to order the results, try:
SELECT C.* FROM (
SELECT A.* FROM (SELECT TOP 1 * FROM DBO.SomeTable ORDER BY SomeTable.SomeKey ASC) A
UNION ALL
SELECT B.* FROM (SELECT TOP 1 * FROM DBO.SomeTable ORDER BY SomeTable.SomeKey DESC) B
) C ORDER BY C.SomeKey
–DA
I am a regular reader of your blog and all your articles are excellent with real time scenarios.
I had faced the same issue for displaying top and bottom rows and I used the below one with the help of CTE.
(ofcourse this query will work only in sql2005)
;with MyTab
as
(
select top 1 * from request order by requestid asc
union all
select top 1 * from request order by requestid desc
)
select * from MyTab
Khadar,
Your query generates following error :
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘asc’.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword ‘desc’.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Dave,
Thanks for the response.
After seeing your message, I cross checked the same on sql express , workgroup, dev edition and the enterprise editions to track this error. Everything is working fine and so far I never got this type of error in my application.
Not sure why this error is popping up. I will check with other systems.
Thanks
Khadar
Hello Khadar Khan,
You are correct. I carefully observed my code and there was syntax error. Your code is correct and I like it. I will post it soon on this blog.
Working example with adventureworks is here :
WITH TopBottomRow
AS
(
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
UNION ALL
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC
)
SELECT *
FROM TopBottomRow
Thank you again for your comment.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Pinal Dave,
I really thank you for spending your time on this query.
Regards
Khadar
Performance-wise I like Dave and Khadar’s solutions because they avoid using an IN clause. But as you said, you were in a rush and the code worked just fine so there’s nothing wrong with that!
Hi, Pinal
i likes your blog
and i m giving a different solution, may be u like it
select max(colname) from Table
union
select min(colname) from Table
order by 1 desc
you will also see i have used “order by 1 desc”
a technique to order by column in union
The problem with this is that it will only work if there is one column that is being applied in the sort. If you need multiple columns, then the ROW_NUMBER ranking function is a better idea (and can be used in all cases):
select
t.*
from
(
select
~cast(row_number() over (order by , , … ) - 1 as bit) as _min,
~cast(row_number() over (order by , , … desc) - 1 as bit) as _max,
t.*
from
as t
) as t
where
_min 0 or
_max 0
If you don’t want the addition of the _min and _max fields to the result set, then it would be possible to do so using a number of methods, but the general idea is the same (and it would prevent a duplicate table scan as well due to the union).
Sorry, the query should be:
select
t.*
from
(
select
~cast(row_number() over (order by [field 1], [field 2], … [field n]) - 1 as bit) as _min,
~cast(row_number() over (order by [field 1], [field 2], … [field n] desc) - 1 as bit) as _max,
t.*
from
as t
) as t
where
_min <> 0 or
_max <> 0
[...] 10, 2008 by pinaldave Please read SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with alternate solution of the same [...]
Hello Nicholas Paldino,
I always enjoy your participation in this blog. Really very nice query and quite an enhancement to my original query.
Would you please create one working example using AdventureWorks database and I will post it on my blog with your courtesy.
Kind Regards,
Pinal Dave ( http://www.SQLAuthority.com )
[...] 11, 2008 by pinaldave Please read SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL before continuing this article. I had asked users to come up with alternate solution of the same [...]
Great blog! I totally forgot how to do this with the sub-query. Thanks! :-)
Hi Pinal Dave,
i have one problem to get data from table.
My table name is “WorkingHours” it have three field (attributes) DayName, openhour,closehour(DataType to all is nvarchar(10)).
table records like this
1)”Monday”,”1000″,”2000″
2)”Sunday”,”0930″,”1930″
3)”Wednesday”,”1030″,”2030″
upto 7records, dayname are in disorder.But i want to dispaly the
day name in sequencial order like “Monday”,Tuesday”…..”Sunday”
How can i write query to get day name in sequenciql order?
Hello, i have e problem to translate er diagram for constraint participation in sql statement , how do you have this thing with trigger or something else i am doing this in SQL server 2005 ,if you have any solution please send me in my email for this problem good bye and good lucy for you.
Hi
What TO Do if i want TOP or Bottom In Same Row?
I have this with Case statement But is there is any other Method pls tell me.
Regards
Tarun