Need to write a sql query

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

May 25th, 2015 8:32am

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


Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 8:37am

another method..

select Id,Student,LeaveDate
from (Select *,ROW_NUMBER() over (partition by ID order LeaveDate desc) as RowPosition
from StudentTable)
where RowPosition=1

May 25th, 2015 8:51am


SELECT
Id, max(student_name) as StudentName, MAX(leave_date) as LeaveDate
FROM dbo.Student GROUP BY Id
Order By ...


Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 9:10am

... and here is yet another option:

SELECT  Id, student_name, MAX(leave_date) as LeaveDate 
FROM dbo.Student
GROUP BY Id, student_name
ORDER BY ...
May 25th, 2015 9:19am

Another 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)

Free Windows Admin Tool Kit Click here and download it now
May 25th, 2015 10:40am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics