[SOLVED] Select latest row, per person

[nezbo]

Hi all

 

I have a table that I want to get the latest/last record each particular person on one select string...

 

what is the best way to do this?

 

East person has a number of entry's and a mktime.

 

Cheers,

Nezbo

 

 

 

[revraz]

Which do you want, the latest or the last?

 

I would assume latest means by time stamp, and last means record ID.

[mrmitch]

If you have a distinct person id per row, and they are ordered so that the person's latest entry is the highest id, you can select DISTINCT(person_id) from person order by id desc;

It'll order the table by the latest entries and pull out the first row per id.

 

Mitch

 

[nezbo]

Latest by Time stamp and last by time stamp, per record id(personID)

 

Which do you want, the latest or the last?

 

I would assume latest means by time stamp, and last means record ID.

[nezbo]

I have tryed this and it don't work?

 

$GetAllSites = @mysql_query("SELECT DISTINCT(userID), sitePref, timeStamp, valid FROM dental_emerg WHERE valid = 0 ORDER BY timeStamp DESC");
		while ($GetAllSites2 = @mysql_fetch_array($GetAllSites))
		{
			echo $GetAllSites2['timeStamp'] . "<br>";
		}

 

If you have a distinct person id per row, and they are ordered so that the person's latest entry is the highest id, you can select DISTINCT(person_id) from person order by id desc;

It'll order the table by the latest entries and pull out the first row per id.

 

Mitch

 

[revraz]

You want to order by timestamp, descending, limit 1

[nezbo]

This only shows the very last entry and not the last entry per person...

 

You want to order by timestamp, descending, limit 1

[mrmitch]

What does the other query return?

 

Try it with a MAX on timestamp and grouping by the user id.

 

$GetAllSites = @mysql_query("SELECT userID, sitePref, MAX(timeStamp), valid FROM dental_emerg WHERE valid = 0 GROUP BY userID");
         while ($GetAllSites2 = @mysql_fetch_array($GetAllSites))
         {
            echo $GetAllSites2['timeStamp'] . "<br>";
         }
[\code]

[Barand]

try

SELECT d.userID, d.sitePref, d.timeStamp, d.valid 
FROM dental_emerg
INNER JOIN
(
    SELECT userID, MAX(timestamp) as latest 
    FROM dental_emerg
    GROUP BY userID
) as X
ON d.userID = X.userID AND d.timestamp = X.latest

[nezbo]

That worked a treat with some minor tweaks...

SELECT dental_emerg.userID, dental_emerg.sitePref, dental_emerg.timeStamp, dental_emerg.valid 
									FROM dental_emerg
									INNER JOIN
									(
										SELECT userID, MAX(timeStamp) as latest 
										FROM dental_emerg
										GROUP BY userID
									) as X
									ON dental_emerg.userID = X.userID AND dental_emerg.timeStamp = X.latest WHERE dental_emerg.valid = 0

 

Cheers