I have a table which shows data in below format
Now I need that only the max date record should be shown for every student as shown below
Technology Tips and News
I have a table which shows data in below format
Now I need that only the max date record should be shown for every student as shown below
Try this:
SELECT T1.* FROM student t1 INNER JOIN ( SELECT student_name, MAX ( leave_date ) leave_date FROM student GROUP BY student_name ) T2 ON T1.leave_date = T2.leave_date
You may have change name of your table and column names
Hope this helps
another method..
select Id,Student,LeaveDate from (Select *,ROW_NUMBER() over (partition by ID order LeaveDate desc) as RowPosition from StudentTable) where RowPosition=1
SELECT Id, max(student_name) as StudentName, MAX(leave_date) as LeaveDate
FROM dbo.Student GROUP BY Id
Order By ...
... and here is yet another option:
SELECT Id, student_name, MAX(leave_date) as LeaveDateAnother way.
SELECT t2.* FROM @table t1 INNER JOIN @table t2 ON t1.ID = t2.ID AND t2.date = (SELECT MAX(date) FROM @table WHERE ID = ID)
In future please provide example data and DDL, like this:
DECLARE @table TABLE (ID INT, date DATE, Event CHAR(3), CountryCode INT, TxnID INT) INSERT INTO @table (ID, date, event, CountryCode, TxnID) VALUES (1, '2015-05-22', 'ABC', 123, 111), (2, '2015-05-23', 'XYZ', 123, 111), (3, '2015-05-20', 'XYZ', 123, 222), (4, '2015-05-21', 'ABC', 123, 222)