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
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.