How to get a record base on date from a group using T-SQL

Question

Hi all

Here is the data

Flag      Zone         Info         Date
R         North        AAA          2010-2-14
R         North        AAA          2010-2-24
T         North        AAA          2010-2-4
R         South        AAA          2010-2-23
T         South        AAA          2010-2-14
R         EAST         AAA          2010-2-22
T         EAST         AAA          2010-2-11
T         EAST         AAA          2010-2-1
T         EAST         AAA          2010-2-14
R         WEST         AAA          2010-2-29

Here is a table in the SQL SERVER, now I want to get a record from each group based on Zone column. The Flag field of this record should be R, and the Date should be the closest and after today's date.

Best Regards,

Answer 1

Using ROW_NUMBER you can try

DECLARE @Table TABLE(
        Flag VARChAR(1),
        Zone VARCHAR(10),
        Info VARCHAR(10),
        Date DATETIME
)

INSERT INTO @Table SELECT 'R','North','AAA','2010-2-14' 
INSERT INTO @Table SELECT 'R','North','AAA','2010-2-24' 
INSERT INTO @Table SELECT 'T','North','AAA','2010-2-4' 
INSERT INTO @Table SELECT 'R','South','AAA','2010-2-23' 
INSERT INTO @Table SELECT 'T','South','AAA','2010-2-14' 
INSERT INTO @Table SELECT 'R','EAST',' AAA','2010-2-22' 
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-11' 
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-1' 
INSERT INTO @Table SELECT 'T','EAST',' AAA','2010-2-14' 
INSERT INTO @Table SELECT 'R','WEST',' AAA','2010-2-28' 

;WITH Dates AS(
    SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY Zone ORDER BY Date) ROWID
    FROM    @Table
    WHERE   Flag = 'R'
    AND     Date > GETDATE()
)
SELECT  *
FROM    Dates 
WHERE   ROWID = 1

If you cannot use ROW_NUMBER you could try

SELECT  t.*
FROM    (
            SELECT  Zone,
                    MAX(Date) MaxDate
            FROM    @Table
            WHERE   Flag = 'R'
            AND     Date > GETDATE()
            GROUP BY Zone
        ) Dates INNER JOIN
        @Table t ON Dates.Zone = t.Zone and Dates.MaxDate = t.Date

But this will not exclude duplicates...