Problem with using Max in sql server

Transact-SQL
1

I tried this query, but it gives the output as "L3" for the [person id] "101" instead of L2.

select [person id], Name, Nationality, Organization, max(end date), max(Level) from table
group by [person id], Name, Nationality, Organization

2

As per your requirement, you have to include start date and end date in your query to filter valid records.

Try this!
select [person id], Name, Nationality, Organization, max(end date), max(Level) from table
Where [start date] <= getdate()
And [end date] >= getdate()
group by [person id], Name, Nationality, Organization

3

Please provide DLL and consumable test data, with dates in ISO format, in future:

CREATE TABLE #t
(
    Person_id int NOT NULL
    ,Name varchar(20) NOT NULL
    ,Nationality char(3) NOT NULL
    ,Organization char(2) NOT NULL
    ,StartDate date NOT NULL
    ,EndDate date NOT NULL
    ,Level char(2) NOT NULL
);
INSERT INTO #t
VALUES (101, 'Tomi', 'XYZ', 'PE', '20161212', '20170102', 'L3')
,(101, 'Tomi', 'XYZ', 'PE', '20160102', '20170815', 'L2')
,(102, 'Vino', 'XYZ', 'PE', '20160102', '20170815', 'L2')
,(108, 'Ranpu', 'ABC', 'PE', '20170101', '20170313', 'L1')
,(108, 'Ranpu', 'ABC', 'PE', '20170313', '20170831', 'L3');
--select * from #t

A common approach is to use the windowed function, ROW_NUMBER():

WITH PersonOrder
AS
(
    SELECT * -- list required columns
        ,ROW_NUMBER() OVER (PARTITION BY Person_id ORDER BY EndDate DESC) AS rn

    FROM #t
)
SELECT * -- list required columns
FROM PersonOrder
WHERE rn = 1;
1 Like
4

Thanks mmkrishna1919.

But this query doesn't work either because some of the end dates(both) are less than getdate ().

5 
;WITH Person_Current_Level
AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Personid ORDER BY [End Date] DESC) AS row_num
    FROM #t
    WHERE [start date] <= GETDATE()
      AND [end date] >= GETDATE()
)
SELECT Personid, Name, Nationality, Organization, [End Date], Level
FROM Person_Current_Level
WHERE row_num = 1;