Q:

Return rows but filter if there are 1+ rows with the same locationID

Table#Appointments:
ID, Name, LocationID, Created

Currently I have:
SELECT *
FROM Appointments
WHERE Created <= @today
ORDER BY ID 

The problem is, there are times when there are more than 1 rows for a given
LocationID, and in this case, I want to return the one with the most current date.
What would the query look like?

A:

You didn't mention what version of SQL Server you're using - but if you're using 2005 or newer, you can use a CTE (Common Table Expression) with the ROW_NUMBER function:
DECLARE @today DATETIME = .........

;WITH LastAppt AS
(
   SELECT 
       ID, Name, LocationID, Created,
       ROW_NUMBER() OVER(PARTITION BY LocationID ORDER BY Created DESC) AS 'RowNum'
   FROM dbo.Appointments
   WHERE Created <= @today
)
SELECT 
    ID, Name, LocationID, Created
FROM LastAppt
WHERE RowNum = 1
ORDER BY ID

This CTE "partitions" your data by LocationID, and for each partition, the ROW_NUMBER function hands out sequential numbers, starting at 1 and ordered by Created DESC - so the latest row gets RowNum = 1 (for each ID) which is what I select from the CTE in the SELECT statement after it.

A:

The problem you are positing is impossible.
If your WHERE clause specifies:
WHERE Created = @today
Then by definition all  your rows will have IDENTICAL Created fields.
Is there another field you want to use for this?

