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 an error. Let us see how we can retrieve top and bottom rows together.
Incorrect T-SQL Script which will give an error.
-- This script will give you 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 a rush, I wrote something very simple with using UNION code, which will give me similar results.
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 (https://blog.sqlauthority.com)
94 Comments. Leave new
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
Hi All
i have writen query below,
(SELECT TOP (1) DefaultValueList
FROM OrderItemParameters AS oip
WHERE (OrderItemId = oi.Id) AND (Name LIKE ‘B%’)) AS B,
(SELECT TOP (2) DefaultValueList
FROM OrderItemParameters AS oip
WHERE (OrderItemId = oi.Id) AND (Name LIKE ‘B%’)) AS B1,
but i got error “Subquery returned more than 1 value”
but i need parameter will be read like Buch and bracket both ,
how i should read like this
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
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
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
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?
Use this ORDER by clause
order by case
when dayname='Monday' then 1
when dayname='Tuesday' then 2
.
.
when dayname='Saturday' then 7
end
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
Hi Jagadish,,
your query is really good, it is working well but not getting the full meaning of the code can you explain it a little.
Thanks…
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