SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

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)

About these ads

74 thoughts on “SQL SERVER – How to Retrieve TOP and BOTTOM Rows Together using T-SQL

  1. 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.

  2. 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

  3. 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

  4. 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

  5. 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 )

  6. 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!

  7. 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

  8. 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).

  9. 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. Pingback: SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL - Part 2 Journey to SQL Authority with Pinal Dave

  11. 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 )

  12. Pingback: SQL SERVER - How to Retrieve TOP and BOTTOM Rows Together using T-SQL - Part 3 Journey to SQL Authority with Pinal Dave

  13. 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?

  14. 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.

  15. 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

  16. 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

  17. 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
    )

  18. 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

  19. 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

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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 ………………….

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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)

  30. 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

  31. 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! :)

  32. 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!

  33. 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?

  34. 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….

  35. 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…….?

  36. 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

  37. 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)

  38. select top 5 * from Sales.SalesOrderDetail
    union
    select top 5 * from (select top 5 * from Sales.SalesOrderDetail order by SalesOrderDetailID desc) b

  39. Pingback: SQL SERVER – Weekly Series – Memory Lane – #018 | SQL Server Journey with SQL Authority

  40. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

  41. How About This..

    1 > FOR SELECT TOP 20 Row
    SELECT TOP 20 * FROM Customers order by CustomerID;

    2 > FOR SELECT BOTTOM 20 Row
    SELECT * FROM (SELECT TOP 20 * FROM Customers order by CustomerID desc) AS [td] order by [td].CustomerID ;

  42. Hi, I have a problem

    My Query

    SELECT *
    FROM NumberedTable
    WHERE RowNumber <= @firstRowCount
    OR RowNumber IN (SELECT TOP (@lastRowCount) RowNumber
    FROM NumberedTable NUI
    ORDER BY NUI.RowNumber DESC);

    Before this query, another query working. I'm setting row numbers in this query.

    But, this query's result top n row and last n row for all record. I need FIRST N rows and LAST N rows for specific column filter in all records.

    MY QUERY RESULT PICTURE!
    http://imageshack.com/a/img203/4630/5cje.jpg

    ALL RECORDS PICTURE! and I want this picture in selected records !
    http://imageshack.com/a/img23/8771/xjyq.jpg

    I need first N rows and last N rows for all doctors ! Not use where condition !

  43. SELECT *
    FROM Sales.SalesOrderDetail g
    WHERE NOT EXISTS( SELECT 1
    FROM Sales.SalesOrderDetail g2
    WHERE g.SalesOrderDetailID g3.SalesOrderDetailID)

    the above one is taking very long but give the expected
    result
    SELECT *
    FROM Sales.SalesOrderDetail g
    WHERE NOT EXISTS( SELECT 1
    FROM Sales.SalesOrderDetail g2
    WHERE g.SalesOrderDetailID g2.SalesOrderDetailID)
    This will produce the result with in a fraction of a second

  44. I would use this method.
    Firstly I would add a column the has a unique identifier such a a primary key(ID… column name) to the table with a data type being an integer. the apply the Rank() function.
    select top 10 ID, FirstName, LastName, Outstanding_Fees, DueDate, Dense_Rank() over(order by ID asc) as Ranking
    From dbo.RegFee
    go

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s