Jump to content

Distint Records


scgibson

Recommended Posts

I have the following 'status' table which gets updated with a new record at each step of the process. I need a view to return all records with the latest transdate for each prno. Status TransDate PRNo1 2012-01-31 16:19:56.000 11 2012-02-01 10:30:36.000 20 2012-02-01 10:45:01.000 31 2012-02-08 12:30:17.000 42 2012-02-09 12:30:17.000 413 2012-02-15 11:47:42.000 43 2012-02-15 12:50:15.000 4 View needs to return the following Status TransDate PRNo1 2012-01-31 16:19:56.000 11 2012-02-01 10:30:36.000 20 2012-02-01 10:45:01.000 33 2012-02-15 12:50:15.000 4 Some wizardry or quidance would be much appreciated.Stu

Link to comment
Share on other sites

This may work: SELECT status, MAX(transdate), prno FROM status GROUP BY prno, status If it doesn't like that then you may need to remove the status column from the select list and get just the prno and max transdate, and then do a second query to get the status that matches that prno and transdate.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...