Trying to get rid of certain rows here...

[Caldus]

OK, say I have a result table that looks like this:

1 Item    1084985159 Test 
3 Item3  1084984893  Test2
2 Item2  1084984864  Test3
2 Item2  1084984849  Test4
1 Item    1084984821 Test5

See the column that contains 'Item<something>' (Like Item or Item3, etc.)? What I want to do is only get the first occurrence of any item and just get that row. After that, I don't want to get any more rows that contain that same item value. I tried messing with DISTINCT but couldn't seem to get that to work. Basically, I'm trying to get it to return this instead:

1 Item    1084985159 Test 
3 Item3  1084984893  Test2
2 Item2  1084984864  Test3

My query contains a bunch of LEFT JOIN's (there's actually a lot more data than this from different tables). I just want 1 row for each 'Item<something>' entry.

Thanks for any assistance.

[Amit]

Try this ...

SELECT DISTINCT item_field, number_field, test_field FROM table WHERE item_field LIKE 'Item%' GROUP BY item_field

HTH!

[Caldus]

OK, I guess I didn't describe the problem good enough. What I'm trying to say is that I have all of these left joins and all of this data in these tables. I'm trying to pick out the greatest number out of this one field in one of the joined tables and then delete results which contains a duplicate entry for one of the fields in the entire query. When I try to select that field using DISTINCT, it doesn't work. I think it has to do with the fact that I'm using the GROUP BY clause at the end of all of these joins (since I'm using MAX()). Any help appreciated.

[r937]

what do you mean by "all of these left joins" and "delete results"?