Returning MAX row

[luvmy2Js]

I have a query that at times may have 2 rows with the same ID, but only one of those rows needs to be returned based on the date stamp on the file (only when there are multiple rows with the same id). Below is sample data:

A B C D E F G H I J K L M N O
2741 501304 Joe Spellman 32.38 % Monthly USD Rice CO01 LA 9/24/2010 O
2741 501304 Joe Spellman 45.15 % Monthly COP Travel CO01 LA Change Correction 9/25/2010 N
2742 501304 Dave Jammer 1500.45 Amount Monthly Hotel CO01 AG Change Typo 9/25/2010 N
2742 501304 Dave Jammer Amount Monthly Hotel CO01 AG 9/24/2010 O
2743 501304 Bill Left 1416.67 Amount Monthly USD Expenses CO01 AG 9/24/2010 O

For 2741 I only want the row with the latest date to show.. However if I do a MAX on the ID or date it requires me to group the rest of the columns which in turn leaves both rows in the report?

Any advice on how to accomplish this would be great!!

What I need is to only return one row per ID based on the most recent record. All rows are timestamped as to when the data is imported.. The only columns that will NEVER change are A, B, C, and D.

Thanks a million

 

[r937]

SELECT t.a
     , t.b
     , t.c
     , t.d
     , t.e
     , t.f
     , t.g
     , t.h
     , t.i
     , t.j
     , t.k
     , t.l
  FROM ( SELECT a
              , MAX(l) as latest_date
           FROM daTable
         GROUP
             BY a ) AS m
INNER
  JOIN daTable AS t
    ON t.a = m.a
   AND t.l = m.latest_date

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Take a close look at multiple solutions presented in these two blogs:

Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[luvmy2Js]

This works great! Thanks for the article references as well. I now have another issue with the above dataset. I think ROWNUM "might" get me what I want but I am not sure.

Say I get the same type of data back, but this time it doesn't have an "ID" entered? There is a NULL value where the "ID" value should be.

Is there a way to tell it to get the max(ID) and add "1" to it where the value is NULL? But what I need is if there is more than one NULL values increase in increments of "1".

Is there an easy way to accomplish this? Sample data is below.

Thanks a million!!!

A B C D E F G H I J K L M N O
2741 501304 Joe Spellman 32.38 % Monthly USD Rice CO01 LA 9/24/2010 O
NULL 501304 Joe Spellman 45.15 % Monthly COP Travel CO01 LA Change Correction 9/25/2010 N