Solved

Getting the last date for in history table

Posted on 2008-06-24
4
236 Views
Last Modified: 2010-05-18
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
Comment
Question by:Steege
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 100 total points
ID: 21858205
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
 

Author Comment

by:Steege
ID: 21858346
i am assuming the on statement should be a.item = b.item
0
 

Author Comment

by:Steege
ID: 21858353
sorry a.date = b.maxdate
0
 

Author Closing Comment

by:Steege
ID: 31470289
thanks for the help!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question