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 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.
Hello Sir,
Nice way to find first and last value. I have another way to have it, will work older version too. Check this out..
Declare @TestTab Table (ID TinyInt, Value Decimal(18,2))
INSERT INTO @TestTab (ID, Value)
SELECT 1, 1000
UNION ALL
SELECT 2, 2000
UNION ALL
SELECT 3, 3000
UNION ALL
SELECT 4, 3000
UNION ALL
SELECT 5, 4000
SELECT * FROM @TestTab
;WITH CTE_OUTPUT
AS
(
SELECT *
, ROW_NUMBER() OVER (ORDER BY ID ASC) SrAsc
, ROW_NUMBER() OVER (ORDER BY ID DESC) SrDesc
FROM @TestTab
)
SELECT ID, Value
FROM CTE_OUTPUT
WHERE (SrAsc = 1
OR SrDesc = 1)
Can we select all rows or n rows with TOP Keyword
Hi Himanshu,
If you see my solution, you can see the “n” as well. And if you want to fetch all rows which qualifies the condition then you can use RANK instead of ROW_NUMBER that’s it.
Thanks for your help Shivendra
Its my pleasure sir. Its all learnt from you. :)
Himanshu – Top 100 percent?
I have replied commnets please see my comments its working.
SELECT
COD_COLAB,
PRODUCTO,
VALOR_PLAN,
VALOR_VENTA,
UNIDADES_PLAN ,
UNIDADES_REALES ,
UNIDADES_BONIFICADAS,
UNIDADES_REALES/UNIDADES_PLAN AS CUMPLIMIENTO_UNIDADES,
VALOR_VENTA/VALOR_PLAN AS CUMPLIMIENTO_VALORES,
TOP10.ranking_Mes
FROM
(select –OBTIENE LOS 10 PRIMEROS DATOS DE VENTAS(TOP 10) EN EL QUIEBRE DE CADA COLABORADOR
COD_COLAB,
PRODUCTO,
SUM(VALOR_PLAN) AS VALOR_PLAN,
SUM(VALOR_VENTA) AS VALOR_VENTA,
SUM(UNIDADES_PLAN) AS UNIDADES_PLAN ,
SUM(UNIDADES_REALES) AS UNIDADES_REALES ,
SUM(UNIDADES_BONIFICADAS) AS UNIDADES_BONIFICADAS,
RANK() OVER (PARTITION BY COD_COLAB ORDER BY SUM(VALOR_VENTA) desc) AS ranking_Mes
from mobile.v_DashboardVentasRepresentante_SELLIN0001
group by
COD_COLAB, PRODUCTO
) AS TOP10
WHERE TOP10.ranking_Mes10
GROUP BY
COD_COLAB