×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-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!

*Tek-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

Quick SQL question

Quick SQL question

Quick SQL question

(OP)
Given

id | create_date             | value
1     06/01/2009 12:36:35            a
2     06/01/2009 12:36:35            a
3     06/01/2009 12:36:35            a
4     07/01/2009 10:36:35            b
5     07/01/2009 10:36:35            b
6     07/01/2009 12:56:35            b
7     08/01/2009 1:56:35              a
8     08/01/2009 1:56:55              a
9     08/01/2009 1:57:00              a
10   09/01/2009 1:57:00              b

How would I return all results for each distinct value on the latest available date for that value?

So it would return

7     08/01/2009 1:56:35              a
8     08/01/2009 1:56:55              a
9     08/01/2009 1:57:00              a
10   09/01/2009 1:57:00              b

As  08/01/2009 is the max date for a and it has 3 corresponding records and 09/01/2009 is the max date for b
with 1 result

RE: Quick SQL question

Try

Select ID, Value, createdate
from table t
inner join
(select ID, Value, max(createdate) as CDate
from table
group by 1d, Value) d
on t.createdate = d.CDate

Ian
 

RE: Quick SQL question

Alternative solution:

select * from thistable as tko
where cast(create_date as date) =
      (select max(cast(create_date as date))
       from thistable where val = tko.val)


Since it seems like you're using SQL Server (or other DBMS without ANSI compliant date handling), I suppose you'll have to use something else than CAST to get extract the date part from the create_date column.)

RE: Quick SQL question

Just realized, switch CAST and MAX in the sub-select and save a few steps when excecuting.

I.e.
select * from thistable as tko
where cast(create_date as date) =
      (select cast(max(create_date) as date)
       from thistable where val = tko.val)

 

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 Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

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

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close