select the latest entry for each item 2

[Luzian]

Suppose the two tables "Items" and "ItemModifications":
Items
[blue]id[/blue] (int) PK
[blue]name[/blue] (who cares)

ItemModifications
[blue]item_id[/blue] (int) FK on Items.id
[blue]modification[/blue] (who cares)
[blue]when_modified[/blue] (datetime or timestamp)

In the "ItemModifications" table, I can have multiple modifications (entries in this table) on one item. What I want to do is select the latest entry for each item, as illustraed below:

[green][b][Items][/b][/green] table
[b]id[/b]  [b]name[/b]
1   pen
2   rock
3   rubber band

[green][b][ItemModifications][/b][/green] table
[b]item_id[/b]  [b]modification[/b]  [b]when_modified[/b]
1        broken        1:00  PM
1        fixed         3:00  PM
[red]1        melted        3:30  PM[/red]
2        thrown        1:00  PM
[red]2        thrown        1:15  PM[/red]
3        cut           10:00 AM
3        eaten         5:00  PM
[red]3        puked         8:00  PM[/red]

I want the rows in [red]red[/red] as they are the latest.

 

[r937]

select item_id  
     , modification  
     , when_modified
  from ItemModifications as T
 where when_modified
     = ( select max(when_modified)
           from ItemModifications 
          where item_id = T.item_id )

r937.com | rudy.ca

 

[Luzian]

Thanx, but now I have a new problem.

Instead of selecting the latest post, what about the latest post before a suplied date?

 

[PHV]

SELECT A.item_id, A.modification, A.when_modified
FROM ItemModifications A INNER JOIN (
SELECT item_id, MAX(when_modified) last_modified FROM ItemModifications
WHERE when_modified < yourSuppliedDateHere GROUP BY item_id
) L ON A.item_id = L.item_id AND A.when_modified = L.last_modified


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Luzian]

Thanx, It seems like that query gets the latest items only if it is before the supplied date. Sorry if I wasn't clear.

The query you gave seems it would return items whose latest date is before a supplied time:

The supplied time is 2PM:

[b]item_id[/b]  [b]modification[/b]  [b]when_modified[/b]
1        broken        1:00  PM
1        fixed         3:00  PM
1        melted        3:30  PM
2        thrown        1:00  PM
[red]2        thrown        1:15  PM[/red]
3        cut           10:00 AM
3        eaten         5:00  PM
3        puked         8:00  PM

What I want is to select items whose date is before the supplied date, then the latest items are determined after that filter.

The supplied time is 2PM:

[b]item_id[/b]  [b]modification[/b]  [b]when_modified[/b]
[red]1        broken        1:00  PM[/red]
1        fixed         3:00  PM
1        melted        3:30  PM
2        thrown        1:00  PM
[red]2        thrown        1:15  PM[/red]
[red]3        cut           10:00 AM[/red]
3        eaten         5:00  PM
3        puked         8:00  PM

 

[PHV]

Where is the supplied date in your table ?

 

[Luzian]

Does this look right?:

SELECT im.item_id
FROM ItemModifications AS im
WHERE when_modified =
(
	SELECT Max(when_modified)
	FROM
	(
		SELECT *
		FROM ItemModifications
		WHERE when_modified < @supplied_date
	)
	WHERE item_id = im.item_id
)

 

[PHV]

So, what is wrong with my previous suggestion ?
SELECT A.item_id, A.modification, A.when_modified
FROM ItemModifications A INNER JOIN (
SELECT item_id, MAX(when_modified) last_modified FROM ItemModifications
WHERE when_modified < @supplied_date GROUP BY item_id
) L ON A.item_id = L.item_id AND A.when_modified = L.last_modified

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Luzian]

So, what is wrong with my previous suggestion ?

SELECT item_id, MAX(when_modified) last_modified FROM ItemModifications
WHERE when_modified < @supplied_date GROUP BY item_id

selection comes after filtering (WHERE clause)
In my case, I need the reverse of that order.

Nothing is wrong with your suggestion when basing it on my first and unclear emxplaination of my problem. For that I'm sorry. My revised explaination is the post where I have compared our two illustrated solutions.

 

[PHV]

Be honest, have you tried it ?

 

[Luzian]

I'm embarrassed. Yours works perfectly.