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
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”
Thanks this helps me a lot…
can u please help to find the details of top 5 employees how changes alot of depatments
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 ;
You can do it this way.
select top 20 * from Customers
union all
select * from ( select top 230 * from customers order by CustomerID desc) as Cust
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!
ALL RECORDS PICTURE! and I want this picture in selected records !
I need first N rows and last N rows for all doctors ! Not use where condition !
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
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
HI this question was asked in interview and could not answer it but I came home and worked on it.
What are the top three highest salaries?
Select *
From SalariedEmployees
WHERE EmployeeID IN (Select top 3 EmployeeID
From SalariedEmployees
Group by MonthlySalary, EmployeeID
Having MonthlySalary= Max(MonthlySalary) );
Good one Marlo!
Hi Marlo,
You can try… “Row_Number” with “PARTITION BY” Clause, this will give you correct and better performance result too, for large data. Because functionally your query will give correct answer for Emp with same salary.