Query Help (Access or SQL)

[theknight571]

I have a table that consists of:

Unique ID, Date, PersonID, lots of misc fields that may or may not be different with each record.

1,201006,0001,...
2,201001,0001,...
3,201006,0002,...
4,201006,0002,...
5,201001,0003,...
6.201006,0004,...
7.201001,0004,...

What I need is a query that gives me just the most recent entry for each PersonID

1,201006,0001,...
3,201006,0002,...
5,201001,0003,...
6,201006,0004,,,,

The SQL I've found so far uses SELECT UID, MAX(Date), PersonID but groups on each field... which still gives me all the records, since each one has a unique ID.

What am I missing? I can't group all the fields since I'll still get all the records since each is unique.

Any suggestions would be helpful.

I'm also searching so if I find something that works I'll post back.

Thanks.

 

[Markbnj]

Try select top(1) id, date, personid from table where personid = @id order by date desc

 

[GeekDrew]

Depends what your criteria is for "most recent". Are you talking date-wise, highest UID, or what? If your criteria is newest by date, and each PersonID can only have one entry per day, the following might work:

SELECT t1.*
FROM (
SELECT PersonID, MAX(Date) AS date_max
FROM mytable
GROUP BY PersonID
) t0
INNER JOIN mytable AS t1 ON t0.PersonID = t1.PersonID AND t0.date_max = t1.Date

Or if you need to use UID as the most recent indicator, you could just swap it instead of the date references.

 

[lykaon78]

Dirty but simple Access solution:
Build a query that is the Max Of each unique records numbering. Then link that query back to the original table with your link being the MaxOf field from the new query linked to the same field in the main table.

That will pull only the data from the main table that matches the max of data from the query.

Edit: I think this is the not so eloquent way of saying what GeekDrew illustrated in the post above.

 

[theknight571]

Thanks all.

GeekDrew suggestion worked like a charm.