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
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
I think that UNION (or UNION ALL) only allow one order by. You cannot put one order by to each select statement.
Good point Martin.
Yes , we can only keep order by in last query when we use union or union all.We can implement it in this way.
SELECT TOP 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
UNION ALL
SELECT SalesOrderDetailID from( select top 1 SalesOrderDetailID
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID DESC ) as Std
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?