MYSQL Last distinct row

[michaeljohn02]

I've got a table with some duplicate entries, I've been trying different query combinations but I've been unable to solve

this problem.

<snip>

Basically I would like to get the last distinct value (entry id: 9634), I've got many item_id's with duplicate field_value's,

what would be the best query to display this?

Here is my query
SELECT DISTINCT `field_id` , `field_value` , `item_id` , `entry_id`
FROM `ibf_registry_field_entries`
ORDER BY `entry_id` DESC

[edited by: engine at 10:54 am (utc) on Oct. 21, 2008]
[edit reason] No urls, please post code, thanks [/edit]

[phranque]

welcome to WebmasterWorld [webmasterworld.com], michaeljohn02!

adding a " LIMIT 1" to that query should solve it...

[michaeljohn02]

Thanks, I've always came across WebmasterWorld, but never actually registered until yet.

Problem is that I've got many different sets of item_id's I want to get the unique value This query does what I want except that for the fact that I want to get the last distinct rows not the first one.

SELECT DISTINCT field_id, item_id, MIN(entry_id) as entry_id, MIN(field_value) as field_value FROM values2 WHERE item_id!='0' GROUP BY field_id, item_id ORDER BY item_id, field_id ASC

[michaeljohn02]

Here is my table layout.

entry_id field_id item_id field_value
1 1 1 55
2 1 1 424
3 2 1 33
4 2 1 432434
5 1 2 32432
6 1 2 4324
7 2 2 23423
8 2 2 4324324

And I want the query to display the results like this

entry_id field_id item_id field_value
2 1 1 424
4 2 1 432434
6 1 2 4324
8 2 2 4324324

So far I'm using this query
SELECT DISTINCT field_id, item_id, MIN(entry_id) as entry_id, MIN(field_value) as field_value FROM values2 WHERE item_id!='0' GROUP BY field_id, item_id ORDER BY item_id, field_id ASC

but that is pulling the first distinct values not the latest/last distinct values like I want.

Michael

[michaeljohn02]

Solved... Don't know if this is the most efficient way to do it but this query seems to do the trick.

SELECT DISTINCT field_id, item_id, MAX(entry_id) as entry_id, MAX(field_value) as field_value FROM entries WHERE item_id!='0' GROUP BY field_id, item_id ORDER BY item_id, field_id ASC