Pls HELP - Trouble getting all data selected for max of one field

Pls HELP - Trouble getting all data selected for max of one field

Post by shawnas » Mon, 25 Nov 2002 04:51:03



I have a table that has multiple records for each reference number.  I
am trying to select the max dateUpdated record for each reference
number.  The problem I am having is that my max dateUpdated is different
for each reference number and the other fields within the record may
have unique data so the select statement may pull 2 records for the same
reference number because the whole record is not distinct...
This statement works but only if the data is the same in all records
except referencenum and dateupdated.....
SELECT DISTINCTROW [ReferenceNum], [Description], [Type],
max([DateUpdated]) AS LastUpdated
FROM ProjectTestTable
GROUP BY [ReferenceNum], [Description], [Type]
HAVING (max([DateUpdated]))
WITH OWNERACCESS OPTION;
Someone please HELP.....   All I want to pull is the whole record that
has a the last updated date for each referencenum....  Thanks, Shawnash

--
Posted via http://dbforums.com

 
 
 

Pls HELP - Trouble getting all data selected for max of one field

Post by lindawi » Mon, 25 Nov 2002 15:37:13


shawnash,

Quote:> I have a table that has multiple records for each reference number.
> I am trying to select the max dateUpdated record for each reference
> number.  The problem I am having is that my max dateUpdated is
> different for each reference number and the other fields within the
> record may have unique data so the select statement may pull 2
> records for the same reference number because the whole record is
> not distinct... This statement works but only if the data is the
> same in all records except referencenum and dateupdated.....
> SELECT DISTINCTROW [ReferenceNum], [Description], [Type],
> max([DateUpdated]) AS LastUpdated
> FROM ProjectTestTable
> GROUP BY [ReferenceNum], [Description], [Type]
> HAVING (max([DateUpdated]))
> WITH OWNERACCESS OPTION;
> Someone please HELP.....   All I want to pull is the whole record
> that has a the last updated date for each referencenum....  Thanks,

That looks like Access ... This is how it's done in T-SQL (untested).

select  Test.ReferenceNum, Description, Type, DateUpdated
from    ProjectTestTable Test
join   (select  ReferenceNum, max(DateUpdated) as LastUpdated
        from    ProjectTestTable
        group by ReferenceNum) MaxDate
on      Test.ReferenceNum = MaxDate.ReferenceNum
and     Test.DateUpdated  = MaxDate.LastUpdated

Linda

 
 
 

1. getting more than one field with max

I have a time series of data, I would like to find the maximum
value in the time series along with another field that is in the same
row as that maximum.

if I have 2 columns,

     dte int,
     data numeric(9,5)

I can do a

      SELECT MAX(data) FROM foo

and get the maximum data, but in order to get the dte with it I end up
doing some complicated SQL sybase will do but is slow...

    SELECT data, dte FROM foo WHERE data =
      (SELECT MAX(data) FROM foo)

and this I have to do in a rowcount 1 incase there are multiples at the
max. Is there any better way to do this?

email replies appreciated.

2. images and sybase

3. Getting right Column C data on Max(Col B) select grouped on Col A.

4. Dynamically Generate FullName in SELECT stmt

5. Help!! trouble with Max() on multiple fields

6. OAS 4.07

7. Need Help: search/replace data in one field/one record, by queries or script: PDOX5

8. Upgrade 6.0 ALPHA to 7.0 INTEL

9. Data replication pls pls help

10. Data replication with Access pls pls help

11. insert data from one field in one table to another field in another table

12. PLS help: Change field structure without loosing data?

13. clearing selected data in one field