Getting Top 1 and Bottom 1 results with a simple SQL query

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


Duh..Its been a long time

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

Happy Coding….


SQL Server FAQ

Here are some mostly useful FAQs on SQL Server
==============================
1. For finding the Nth salary
 
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)
print @sal
select * from emp where sal=@sal
         This will give u the top most salary
Method 2:
 select top 1 sal  from (select top 1 * from emp order by sal desc) sal
order by sal
Method 3: [Better One]
Select * From Emp X Where 5 =
(
Select Count(Distinct Sal) From Emp Where sal >=X.sal
)
 

Calling JavaScript from Asp.net

Most of we think that it is very difficult to call a javascript code from
codebehind file in asp.net. But it is possible with a single line of code….
 
Let me explain to display a confirmation dialog box in a page…
 
If we have a button named Button1 then
  in Page_Load  add the following single statement
 
    Button1.attributes.add("onclick","if (confirm(‘Are you sure to proceed?’)) return true;else return false;")
       That’s it. Our job is done..
 
Now we can write the server side coding in Button1_Click that shud be executed if user clicks "yes"
      on the prompt. It won’t do the postback if we go with "No" option
 
=========
Happy  asp.net
=========