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://blog.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
What do i do if i want to retreive the bottom 5 UnitPrice from the products table?
select max(EmpID) as EmpID, ‘Biggest’ as [Level] from employee
union all
select min(EMPID) as EmpID, ‘Lowest’ as [level] from employee order by EmpID asc–’order by’ can be added/changed as required
It’s really helpfull to me. Thanks to all of you including Pinal.
Here’s a another solution to Retrieve TOP and BOTTOM Rows Together using T-SQL
I hope I have done it…
SELECT *
FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID IN
(
SELECT max(SalesOrderDetailID) FROM Sales.SalesOrderDetail
UNION ALL
SELECT min(SalesOrderDetailID) FROM Sales.SalesOrderDetail
)
Hi Dave,
I want to show or retrive data in some range
e.g
top 20, then I will write “select top 20 …”
But what if I want top 30 to 40 records, I want to skip top 1 to 30 and show 30 to 40 records
“select top 30 to 40 …” will not work
one soluntion is I am using inner query,
“select * from(select *,ROW_NUMBER() OVER(Order by FirstName DESC)as ‘ui’ from emp) as temp where ui between 30 to 40″
but I want to do this same using single query, so is it possible?
Please reply
Thanx
Jagadish Mori
It drives me crazy that google search is filled with a zillion responses specific to SQL server and the exact problem noted above — not wanting to have to retrieve 250,000 records in order to just get from 200-300 of a specific sort order — and the solutions posted DO NOT WORK IN SQL SERVER BECAUSE ROWNUM/ROW_NUMBER() IS NOT A RECOGNIZED FUNCTION NAME in that database. There is no equivalent, says hours of searching on that very question.
This page is specific to SQL Server yet there are solutions here that pointedly will not work in SQL Server. Yes, if only we had a rownum function, pagination would be so easy! Instead we have long-lagging queries because they either have to get a ton of records and then use the middleware to restrict output, or they have to make multiple tables and queries and go back&forth trying to “back-end-hack” a solution.
Sorry if I sound annoyed but after wasting my weekend reading search results and STILL not finding an answer, when this has been a question online for literally 9 years now!, is very frustrating!
A nice follow up post to your ‘top and bottom’ entry might be, ‘what if I need to get 10 records from ’somewhere in the middle’?
PJ
OK, apparently it’s merely that my SQL Server 2008 database has some obscure setting I’ve never heard of that is causing every two-dozen-variant experiments on this to fail. Sheesh. On a separate search result I found this note, when people complained of the same error:
“Check the databse properties. DB compatibility might be set to 80. Row_Number function can only be used when compatibility is set to 90. You can check compatibility level by going to db properties and checking option tab.”
I guess this is where being a web coder vs a DBA is a real problem. I just wasted like two days of my life trying to solve this problem as my queries are literally bringing my website down if I offer pagination. So I apologize for ranting in the previous comment. I will go search for whatever setting on my shared server and home dev server might need to change that this will actually work for me.
However, massive google results saying “there is no rownum equiv in sql-server” did not help the confusion.
Best,
PJ
Here’s a another solution to Retrieve TOP and BOTTOM Rows Together using T-SQL
select * from
(
select top 1 * from emp order by sal desc
union all
select top 1 * from emp order by sal asc)
as b
HI,
I have a table where i am retriving the top1 record now i wnat to retrive a single column from the row and assign it to a local variable
declare @empid interger
select @empid top 1 empid from emp
but i am syntax error can anyone help me ove rthis