How to List TOP 'N' Rows along with a Count using SQL Server

I recently came across a requirement where the user had to list the details of TOP 5 rows that matched a particular critera, but also had to list the total no rows that matched that criteria.

Here's how it can be solved. I am using the Customers table of the Northwind database:

SELECT TOP 5 CustomerID, CompanyName,
COUNT(*) OVER () AS TotalSimilarTitles
FROM Northwind.dbo.Customers
WHERE ContactTitle = 'Owner'

In the query over here, I list the TOP 5 Customers who have the ContactTitle as 'Owner'. The query also counts the total number of Customers that have the ContactTitle as 'Owner'

Comparing Dates In Two Columns using SQL Server

If you have to choose the most recent date kept in two columns, then here's the query to do so:

-- Create Sample Table
DECLARE @TT table
( ID int,
SomeDate1 datetime,
SomeDate2 datetime
)

-- Create Sample Data
INSERT INTO @TT VALUES ( 1, '1/1/2009', '2/1/2009')
INSERT INTO @TT VALUES ( 2, '2/1/2009', '1/1/2009')
INSERT INTO @TT VALUES ( 3, '3/1/2009', '2/1/2009')
INSERT INTO @TT VALUES ( 4, '3/1/2009', '4/1/2009')
INSERT INTO @TT VALUES ( 5, '4/1/2009', '4/1/2009')
INSERT INTO @TT VALUES ( 6, '1/1/2009', '5/1/2009')
INSERT INTO @TT VALUES ( 7, '8/1/2009', '6/1/2009');
INSERT INTO @TT VALUES ( 8, '9/1/2009', '7/1/2009');
INSERT INTO @TT VALUES ( 9, '3/1/2009', '8/1/2009');
INSERT INTO @TT VALUES ( 10, '4/1/2009', '9/1/2009');


QUERY
SELECT ID,
CASE
WHEN SomeDate1 < SomeDate2
THEN SomeDate2
ELSE SomeDate1
END AS LastDate
FROM @TT

Results
ID LastDate
1 2009-02-01 00:00:00.000
2 2009-02-01 00:00:00.000
3 2009-03-01 00:00:00.000
4 2009-04-01 00:00:00.000
5 2009-04-01 00:00:00.000
6 2009-05-01 00:00:00.000
7 2009-08-01 00:00:00.000
8 2009-09-01 00:00:00.000
9 2009-08-01 00:00:00.000
10 2009-09-01 00:00:00.000

Some Common DateTime Formats in SQL Server 2005/2008

One of the most commonly asked question in forums/newsgroups/blogs is to format date and time and show it on the UI. Here's a list of some commonly used DateTime Formats that you can use in your projects

SELECT CONVERT(varchar(30), GETDATE(), 1) --   Result - 03/28/09

SELECT CONVERT(varchar(30), GETDATE(), 2) --   Result - 09.03.28

SELECT CONVERT(varchar(30), GETDATE(), 3) --   Result - 28/03/09

SELECT CONVERT(varchar(30), GETDATE(), 4) --   Result - 28.03.09

SELECT CONVERT(varchar(30), GETDATE(), 5) --   Result - 28-03-09

SELECT CONVERT(varchar(30), GETDATE(), 6) --   Result - 28 Mar 09

SELECT CONVERT(varchar(30), GETDATE(), 7) --   Result - Mar 28, 09

SELECT CONVERT(varchar(30), GETDATE(), 8) --   Result - 14:35:50

SELECT CONVERT(varchar(30), GETDATE(), 9) --   Result - Mar 28 2009 2:35:50:637PM

SELECT CONVERT(varchar(30), GETDATE(), 10) -- Result - 03-28-09

SELECT CONVERT(varchar(30), GETDATE(), 11) -- Result - 09/03/28

SELECT CONVERT(varchar(30), GETDATE(), 12) -- Result - 090328

SELECT CONVERT(varchar(30), GETDATE(), 13) -- Result - 28 Mar 2009 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 14) -- Result - 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 101) -- Result - 03/28/2009

SELECT CONVERT(varchar(30), GETDATE(), 102) -- Result - 2009.03.28

SELECT CONVERT(varchar(30), GETDATE(), 103) -- Result - 28/03/2009

SELECT CONVERT(varchar(30), GETDATE(), 104) -- Result - 28.03.2009

SELECT CONVERT(varchar(30), GETDATE(), 105) -- Result - 28-03-2009

SELECT CONVERT(varchar(30), GETDATE(), 106) -- Result - 28 Mar 2009

SELECT CONVERT(varchar(30), GETDATE(), 107) -- Result - Mar 28, 2009

SELECT CONVERT(varchar(30), GETDATE(), 108) -- Result - 14:35:50

SELECT CONVERT(varchar(30), GETDATE(), 109) -- Result - Mar 28 2009 2:35:50:637PM

SELECT CONVERT(varchar(30), GETDATE(), 110) -- Result - 03-28-2009

SELECT CONVERT(varchar(30), GETDATE(), 111) -- Result - 2009/03/28

SELECT CONVERT(varchar(30), GETDATE(), 112) -- Result - 20090328

SELECT CONVERT(varchar(30), GETDATE(), 113) -- Result - 28 Mar 2009 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 114) -- Result - 14:35:50:637

If you are looking out for UTC/GMT function, check this previous post of mine.