×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Help Forming SQL Query

Help Forming SQL Query

Help Forming SQL Query

(OP)
Given that I have the generic fields Person, Number, and Date

I would like to create a SQL query that returns for each unique person, the number associated with the most recent date.

E.g.,
PERSON   NUMBER     DATE
1                  3        1/1/2004
1                  5        1/1/2003
1                  7        6/1/2004
2                  5        1/1/2000
1                  10       1/1/2001
2                  54       1/1/2002
2                  32       1/1/1999

This query would return
PERSON   NUMBER
1                7
2               54

Any help would be most appreciated!  Thanks!

RE: Help Forming SQL Query

Try the following:

SELECT PERSON, NUMBER, MAX(DATE)
FROM TABLE
WHERE DATE <= SYSDATE
GROUP BY PERSON, NUMBER

If DATE is always less than the current date, delete that row.

Hope this helps!

RE: Help Forming SQL Query

Suppose your table is named SALES. Then the query is:

select s1.person, s1.number from sales s1 where s1.date = (select max(s2.date) from sales s2 where s2.person=s1.person) order by s1.person

Verified and it works :)

HTH

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources