Group By

[moondran]

Hi All

 

I have a table containing the id's of items (field name item_id). These items gets booked out and in again and the record stored. So in my table I will have something like this.

Id item_id status date

1 2 in 2012-03-12

2 3 out 2012-04-15

3 2 out 2012-05-12

 

What I want to display is what is the status of the last entry of an item.

 

Currently what I have is

 

SELECT `status` FROM `table` GROUP BY item_id ORDER BY date DESC

But this gives me the first entry of an item and not the last. And even when I change DESC to ASC it still gives me the first entry.

 

Thanks for the help

 

[PFMaBiSmAd]

To get a specific row within a group, see this link - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

 

Otherwise, you get the data from the first row encountered in the table.

[moondran]

Thanks allot!!