Whenever there is a requirement to get a top 1 and the bottom 1 records in a single query, we start writing the query as below
SELECT TOP 1 * FROM Customers order by CustomerID ASC UNION ALL SELECT TOP 1 * FROM Customers order by CustomerID DESC
But this will result below error.
Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword ‘UNION’
This will be resolved simply by making use of CTEs which are introduced in SQL2005 and higher
;WITH TopBottomRows AS ( SELECT TOP 1 * FROM Customers order by CustomerID ASC UNION ALL SELECT TOP 1 * FROM Customers order by CustomerID DESC ) SELECT * FROM TopBottomRows
Hope this helps..
Yes..Its been a long time (very very long ) since I posted my blog entries here..I was busy with my job and other activities and hence couldn’t able to visit this space more frequently..I would have written at least one post in a month but it wasn’t happened. From now I will post more technical articles and try to put useful content over here. Keep watching this space..
Method 1:declare @sal int
set @sal=(select top 1 sal from emp where empid not in (select top 0 empid from emp order by
sal desc) order by sal desc)
select * from emp where sal=@salThis will give u the top most salaryMethod 2:select top 1 sal from (select top 1 * from emp order by sal desc) sal
order by salMethod 3: [Better One]Select * From Emp X Where 5 =
Select Count(Distinct Sal) From Emp Where sal >=X.sal