Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Getting the last date for in history table

So i have a history table that keeps track of all the approvals on a request. I need to find out who did the last approval for the record. so for instance

Item                                       Who Approved      Date
1                                            person X                 06/11/08
1                                            person Y                  06/08/08
1                                           person b                   06/05/08

2                                            person r                 06/11/08
2                                            person g                  06/08/08
2                                            person b                   06/05/08

so i would want my result set to be
1                                            person X                 06/11/08
2                                            person r                 06/11/08

i tried to group by the ID and getting the max date but then i lose who did the last approval
0
Steege
Asked:
Steege
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello Steege,

SELECT a.* FROM urTable a
inner join (SELECT Item, MAX(Date) maxDate from urTable GROUP BY Item ) b
ON a.Item = b.MaxDate

GOD BLESS,

Aneesh R.
0
 
SteegeAuthor Commented:
i am assuming the on statement should be a.item = b.item
0
 
SteegeAuthor Commented:
sorry a.date = b.maxdate
0
 
SteegeAuthor Commented:
thanks for the help!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now