CODEWITHSUNDEEP
phpmysql
Nate
Nate

Reputation: 28484

How to select rows based on two column values?

I have a MySQL table structured somewhat like this:

type    name    value
=====================
1       item1    1
1       item2    20
2       item3    0
3       item4    5
3       item5    2
3       item6    50

I need to write a query that returns the lowest valued item of each type, sorted by the value. So the result should be:

type    name    value
=====================
2       item3    0
1       item1    1
3       item5    2

I can get this to work, but it's looking really, really ugly right now. Is there an elegant way to do this?

Thanks so much!

Upvotes: 0

Views: 267

Answers (3)

Rob
Rob

Reputation: 12872

SELECT type, name, MIN(value) as value FROM table GROUP BY type

Upvotes: 0

Gohn67
Gohn67

Reputation: 10648

Edit 3: Here are the queries reworked grouping by type instead of name.

select type, name, min(value) from (select, type, name, value from Table order by value asc) group by type

This one may work, but I'm not sure. See below. Man, what a mess.

select type, name, min(value) from Table group by type order by value asc

Edit 2: Sorry, I read the question wrong. You can ignore this answer

I need to double check this. Can't remember if the order by will make it so the correct type will be selected.

select type, name, min(value) from Table group by name order by value asc

Edit: I tested a similar query on one of my datasets and it seemed to work. But I can't say for sure because I just read that order by happens after group by.

So an alternative would be this then:

select type, name, min(value) from (select, type, name, value from Table order by value asc) group by name

Upvotes: 0

mathematical.coffee
mathematical.coffee

Reputation: 56955

This is called a "greatest-n-per-group" query (under that tag on SO you will find many similar questions). (I know you want the "lowest-n-per-group", but it's the same problem).

Usually, you would be able to do:

SELECT type, MIN(value)
FROM mytable
GROUP BY type

But this won't work if you also want the name corresponding to the MIN(value).

To retrieve the minimum value per type and also the corresponding row, you join your table to itself within type (the GROUP BY) variable, and with a sorting condition on value (the MIN) variable:

SELECT t1.type, t1.name, t1.value
FROM mytable t1
LEFT JOIN mytable t2 ON t1.type = t2.type AND t1.value > t2.value
WHERE t2.value IS NULL

Note :

  • we LEFT JOIN mytable to itself, restricting the join such that all the types are the same. This will produce a table with every combination of values for each type.
  • we add a condition to the LEFT JOIN to restrict the combinations of values such that t1.value > t2.value. So now we have a table with every combination of values within each type, but t1's is bigger than t2's
  • since this is a LEFT JOIN, if there is a t1.value for which there is no smaller t2.value, the corresponding t2 columns will be NULL. But this is precisely the smallest t1.value for that type!
  • Add a WHERE t2.value IS NULL condition in to pick out exactly these rows.

Upvotes: 3

Related Questions