MYSQL SELECT DISTINCT with additional column in result set

Question

Here's an example table:

CREATE TABLE `deals_unsorted`.`temp_demo` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT ,
`price` INT( 5 ) NOT NULL ,
`name` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM

and some sample data

INSERT INTO `deals_unsorted`.`temp_demo` (
`id` ,
`price` ,
`name`
)
VALUES (
'1', '1300', 'suite'
), (
'2', '1200', 'suite'
), (
'3', '1100', 'standard'
), (
'4', '1000', 'standard'
), (
'5', '800', 'basic'
), (
'6', '900', 'basic'
), (
'7', '500', 'dorn room'
), (
'8', '500', 'dorm room'
), (
'9', '800', 'twin'
), (
'10', '750', 'twin'
)

But how in the world do I find all distinct rooms with the lowest price?

I've tried SELECT DISTINCT(name), MIN(price) FROM temp_demo; and many variations but the computer said no thanks with a:

1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

If anyone can help me out with this simple table I'm sure it'll be a help to others too. I've seen many examples with complicated tables and I just can't get my head around it.

What I'm hoping for is the entire rows:

2, 1200, suite
4, 1000, stanbard
5, 800, basic
7 or 8, 500, dorm room
10, 750, twin

Because these have the lowest price for their distinct name

score 3 · Answer 1 · answered Feb 24 '12 at 02:44

To get the lowest price per distinct name, you don't want DISTINCT, but rather GROUP BY. The name/price pair returned by the inner GROUP BY query is then matched with the id value from the rest of the table with a JOIN.

SELECT id, nmin.name, nmin.price
FROM deals_unsorted JOIN (
  SELECT name, MIN(price) AS price FROM deals_unsorted GROUP BY name
) nmin ON deals_unsorted.name = nmin.name AND deals_unsorted.price = nmin.price

score 1 · Answer 2 · answered Feb 24 '12 at 02:43

Since you also want to return the room number, you'll need a correlated subquery. Note that this will return two records for ties rather than joining the numbers with an or.

   select * from temp_demo t1 where price=(select min(price) 
from temp_demo t2 where t1.name=t2.name)

Anton · Accepted Answer · 2012-02-24T13:12:55.943

1

This does the job:

SELECT id, name, MIN(price)FROM deals_unsorted GROUP BY name

Well, almost. It doesn't return 7 or 8, 500, dorm room. But other than that it works.

edited Feb 24 '12 at 13:12

answered Feb 24 '12 at 02:57

Anton

1,435
2
10
21

Well as I'm querying 11M rows with 64 columns I really need to avoid nesting anything if I can help it so I'm going with your suggestion. There are literally thousands of each room at exactly the same price as an `INT` so I'm changing it to a `float` and randomly adding 0.1 to the rows. I'll `floor` it on output so I still get the duplicate prices. I know it's a horrible hack but speed is the most important thing here and my client is on MYSQL4 so I can't even partition the table. Thanks :) – Andy Gee Feb 24 '12 at 03:22

MYSQL SELECT DISTINCT with additional column in result set

3 Answers3