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)
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
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 )
how i get value from bottom of table , not desc function use. please help me
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?
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
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
Thank you! Great article… solved my problem!
I commiserate with all those who feel that if there is a TOP then there ought to be a BOTTOM. If there is a UNION there ought to be a INTERSECTION. Why does SQL have to be so lame? Why can’t I say ‘select name from dog_table where dog_table.name = cat_table.name’?, There is no ambiguity. Why can’t the interpreter convert my ints to strings automatically for a compare and just let me know that it did that if it is so concerned I might not get what I wanted in the first place? Whose bright idea was it to make everything equate to NULL? Null AND (anything) should be FALSE (except NULL and NULL which should be true), and NULL is not LIKE anything (except NULL). What is the difference beteen IS NULL and = NULL? Why do some people still pronounce it Ess Que El, or sequel, when it should be monosyllabic as in what we do when we have to read through these posts in order to do what should be easy to do in the first place.
SQueaL! SQL like a pig!
Penal, your column has been most helpful for those of us who have no choice but to live within the muck that makes us SQL.
possible solution:
change SQL language to support multiple adverbs for select:
select top 1, bottom 1:* from table;
Practical solution for now, read the prior responses, realize and accept that there is no BOTTOM in T-SQL as of this writing.
-Martin
SELECT MIN(SalesOrderDetailID) as TopOne, MAX(SalesOrderDetailID) as BottomOne FROM Sales.SalesOrderDetail
how does retrive date & time wise row from the database using sql quary……….
date & time format like “06/12/2009 1:03:16 PM” and 06/12/2009 2:06:43 PM”
between this period how many data are present in the database.
please write a format ………………….
Hello Sudarshan,
If the column is DATETIME datatype then SQL server automatically compare these values (like “06/12/2009 1:03:16 PM”) correctly otherwise you would have to convert the column into a DATETIME value for comparision.
Regards,
Pinal Dave
If i want to see the bottom records then which command i need to be entered ..?
Can u help me
Nilesh,
this is what i used…. hope it helps
SELECT top 100 *
FROM tablename
order by fieldname DESC
Pinal,
You have a wonderful website and i frequently visit your site whenever in doubt.
My question:
I need to write a query to exclude Top 5 percent and Bottom 5 Percent of the rows and try to get the max and min from the remaining rows.
Thanks,
Suri
When I write a query with Max aggregate function the query performace is very slow on a big table. I have primary key and indexes are in place. When add Top 1 statement in front of Max the query performance improves drastically.
My question:
What is the significance of adding Top 1 in front of Max function like Select Top 1 Max(dt_tran) from Temp
Hi
Hot to get currency symbole from my pc in sql 2008
Thanks
Selva
Hi
Hot to get currency symbole from my pc regional setting in sql 2008
Thanks
Selva
hi
I have writen code below
(SELECT DISTINCT cast(dbo.ParaNu(OrderItemId) AS decimal(38)) AS Expr1
FROM OrderItemParameters AS OrderItemParameters)
but i got error msg “Error converting data type varchar to numeric.”
any one help me what i had mistake ????
Thanks
selva
It means you have data which can’t be converted to decimal
Find out the bottom 8 defaults rows
SELECT * FROM scm_emp_master (NOLOCK)
where emp_emp_code not in
(select top ((SELECT count(*)-8 FROM scm_emp_master )) emp_emp_code ‘emp_code’ from scm_emp_master)
top without order by clause is meaningless
then please tell me how to find out the default records in the table. i need top/bottom inserted records accordingly.
Without an order by caluse it is not possible. If you have any unique column, order by it
Awesome blog. My 2 cents for the problem:
select * from(
SELECT TOP 1 *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID ASC
UNION ALL
SELECT TOP 1 *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID desc
) as a
Have read all comments regarding sql but still cannot find a way locating the Nth record in simple way.
Simple and effective (to Pinal Dave’s solution). I am particularly leaning towards the CTE route, it’s nice, clean and efficient.
Thanks to all! :)
That was fun. I was at my wit’s end when I came upon this article. I was trying this query:
INSERT INTO currenttrans ( transactionnumber, transactionsched, transactionid )
SELECT TOP 1 transactionnumber, transactionsched, transactionid
FROM permdatqueue
ORDER BY transactionsched;
Instead of giving me just one row, it gave me multiple rows. So I modified it after reading this article and came up with:
INSERT INTO currenttrans ( transactionnumber, transactionsched, transactionid )
SELECT transactionnumber, transactionsched, transactionid
FROM permdatqueue
WHERE transactionid in (select top 1 transactionid from permdatqueue
order by transactionid asc)
ORDER BY transactionsched;
…which gave me the result I needed. Many thanks!
Hi, I really enjoy your blog! I am a beginner in MS-SQL and there are many things I still do not know how to do. I have a table where i want to find the winner in a lemonade sale. The table goes sth like this:
first_name sale date
damon 3.50 2007-6-1
damon 6.99 2007-6-2
stefan 1.50 2007-6-1
stefan 2.00 2007-6-2
I want to find the one with the highest sales over the two days. I tried using your code but it doesnt work. I can only use it to find the first n rows in my table. Also, when I try to find the top 3 rows, it returns me 4 rows! Can you plz help me?
Hi
I want to select top 4 valus of sql table unsing vb.net win form.. how to do it?
I have two tables employee table(emp_no,Emp_name,sal)and product table (Prodct_id,prodcut_name) table retrive top 5 redcods from table without duplicate values How………..
Please help me….
select top 5 with ties columns from table order by somecol
Hi,
I am having a 1000 records in a table.I need to select top 100 records first and then again next 100 records and again next 100 records like that.How to write a select query without using cursor or row_number or rank…….?
How to show row wise union Query
how to change this
Select Distinct StdStandard,Count(*)as Cnt1 from Tbl_Students
where StdRegNo not in (Select StdRegisterNo from Tbl_StdFeeAllocation)
group by StdStandard
union all
Select Distinct StdStandard,Count(*)as Cnt1 from Tbl_Students
where StdRegNo in(Select StdRegisterNo from Tbl_StdFeeAllocation)
group by StdStandard
its helpfull for me…. :)
thankss.!!!!1
you can use
set rowcount
May be it helps
USE COMMAND
SET ROWCOUNT 10
Please note that SET ROWCOUNT may not work in future release of the SQL Server. You should try to use TOP operator instead.
WITH SQN(COUNTRY,ROW)
AS
(
SELECT COUNTRY_CODE,ROW_NUMBER() OVER( ORDER BY COUNTRY_CODE) AS ‘ROWNUM’ FROM SET_COUNTRY )
SELECT COUNTRY FROM SQN WHERE ROW = (SELECT MAX(ROW) FROM SQN) OR ROW = (SELECT MIN(ROW) FROM SQN)
I gess in SQL Server 2008 you can use ORDER BY if you use UNION… not UNION ALL.
select top 5 * from Sales.SalesOrderDetail
union
select top 5 * from (select top 5 * from Sales.SalesOrderDetail order by SalesOrderDetailID desc) b
Hai, i need to get the full dynamic query from a stored procedure without using “Print”
[...] How to Retrieve TOP and BOTTOM Rows Together using T-SQL 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. In this blog post, I explain how the same can be achieved with simple T-SQL script. [...]
[...] Part 1 | Part 2 | Part 3 [...]
Thanks this helps me a lot…