CODEWITHSUNDEEP
phpmysql
dm03514
dm03514

Reputation: 55962

Mysql query In QUERY with only 1 result for each in id

Please excuse the vagueness of my title, I really don't know how to succinctly write what I am trying to do.

I have a table with

name - str item_id - int position - int

  name        item_id   position
| 6asd |          17 |        1 |
| asdf |           3 |        1 |
| asdf |           3 |        2 |
| asdf |           3 |        3 |
| asdf |           3 |        4 |

I am trying to use an IN query on a list of item_ids and only return 1 row per item and that should be the row with the lowest position.

select name, item_id, position from my_table 
WHERE item_id IN (17, 3) AND name != '';

This returns all rows for the item_id when I am only trying to return the lowest position. Naturally I looked at ORDER_BY and LIMIT but my query only is done on the whole results set not on each id individually.

ORDER BY position ASC LIMIT 1

Has anyone tried to do this before?? any help would be greatly appreciated. Do I need to use a subselect somehow?

Upvotes: 0

Views: 61

Answers (2)

Fahim Parkar
Fahim Parkar

Reputation: 31637

Use MIN() that is available.

SELECT name, item_id, MIN(position)
FROM my_table 
WHERE item_id IN (17, 3) AND name != ''
GROUP BY item_id;

Upvotes: 4

Bono
Bono

Reputation: 4849

I think you might be looking for the MIN() function.

MySQL MIN function is used to find out the record with minimum value among a record set.

Try something along these lines:

"SELECT name, item_id, MIN(position) 
 FROM my_table 
 WHERE item_id IN (17, 3) AND name != ''
 GROUP BY item_id";

Upvotes: 1

Related Questions