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'

Feedback:

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

Feedback:

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.

Feedback: