DISTINCT with MAX

anjanesh · #1 (**permalink**) 12-18-07, 23:40

Hi

I want to get the maximum Price for every Item. There can be many same Items with different price.

This works :

Code:

SELECT `Item`, `Price`
FROM `table1` t1
WHERE
`Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
ORDER BY `Price` DESC
LIMIT 0,10;

Though I was wondering if theres a way to get it done this way

Code:

SELECT `Item`, (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`) AS `Price`
FROM `table` t1
WHERE `Position` = '1 - 3'
ORDER BY Price DESC
LIMIT 0,10;

Problem is, this 2nd one returns duplicate Items - how do I get DISTINCT `Items` ?
I understand DINSTINCT a, b, c is equivalent to DINSTINCT a, DINSTINCT b, DINSTINCT c

Thanks

r937 · #2 (**permalink**) 12-18-07, 23:54

Quote:

Originally Posted by anjanesh

Though I was wondering if theres a way to get it done this way

no, not that way

anjanesh · #3 (**permalink**) 12-19-07, 01:40

Code:

SELECT DISTINCT `Item`,
(SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`) AS `MaxPrice`
FROM `table` t1
ORDER BY `MaxPrice` DESC
LIMIT 0,10;

did seem to return the same result set - except it took 4 times longer !
Price is of type float(5,2) I normally dont create an index on non-textual columns. Will creating an index on Price speed up the process ?

Also, (Item, Price) isnt unique.
So what if there are same Items with same Price ?

Code:

SELECT `Item`, `Price`
FROM `table1` t1
WHERE
`Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
ORDER BY `Price` DESC
LIMIT 0,10;

can return duplicate rows.

gvee · #4 (**permalink**) 12-19-07, 08:19

Take a look at GROUP BY

anjanesh · #5 (**permalink**) 12-19-07, 08:34

I did. Im aware of the consequences when using GROUP BY for LARGE sets of data.
Is this equivalent to GROUP BY ?

Code:

SELECT DISTINCT `Item`, `Price`
FROM `table1` t1
WHERE
`Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
ORDER BY `Price` DESC

How is DISTINCT `Item`, `Price` interpreted as?

r937 · #6 (**permalink**) 12-19-07, 09:11

if an item can have multiple prices, and you want only the maximum price for each item, abandon all hope of getting DISTINCT to provide your solution

DISTINCT item,price will provide one row for every item,price combination

GROUP BY is a good solution, but then you will get only the maximum price per item, and no other columns

anjanesh · #7 (**permalink**) 12-19-07, 10:50

Code:

a,4
a,5
a,8
a,4
b,2
b,9
b,8
b,5
b,8
b,1
c,3
c,9
c,4
c,3
c,5

SELECT DISTINCT `Item`, `Price` should return

Code:

a,4
a,5
a,8
b,2
b,9
b,8
b,5
b,1
c,3
c,9
c,4
c,5

SELECT `Item`, `Price` ...
WHERE `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
should return

Code:

a,8
b,8
b,8
c,9

Its the duplicate (b,8) pair that I want to rid off. Hence the addition of DISTINCT with MAX in where

SELECT DISTINCT `Item`, `Price` ...
WHERE `Price` = (SELECT MAX(`Price`) FROM `table1` WHERE `Item` = t1.`Item`)
should return

Code:

a,8
b,8
c,9

r937 · #8 (**permalink**) 12-19-07, 10:53

okay, you are right, you would use DISTINCT this way

the real problem here is why {b,8} occurs more than once

ortho · #9 (**permalink**) 12-19-07, 11:01

Code:

SELECT `Item`, MAX(Price)
FROM table1
GROUP BY Item

Simple enough ? Think that's what you want!

anjanesh · #10 (**permalink**) 12-19-07, 11:18

True, except when an ORDER BY in included, its taking far more time.

Code:

SELECT `Item`, MAX(`Price`)
FROM `table1`
GROUP BY `Item`
ORDER BY `Price` DESC

r937 · #11 (**permalink**) 12-19-07, 11:21

you should always only ever ORDER BY one of the columns in the SELECT

SELECT `Item`, MAX(`Price`) as maxprice
FROM `table1`
GROUP BY `Item`
ORDER BY maxprice DESC

anjanesh · #12 (**permalink**) 12-19-07, 11:24

Sorry - thats what I meant - forgot to add that here.

Code:

SELECT `Item`, MAX(`Price`)
FROM `table1`
GROUP BY `Item`
ORDER BY MAX(`Price`) DESC

r937 · #13 (**permalink**) 12-19-07, 11:53

what indexes have you defined?

please do a SHOW CREATE TABLE for your table

anjanesh · #14 (**permalink**) 12-19-07, 14:27

Code:

CREATE TABLE `test` (
`ID` bigint(20) NOT NULL auto_increment,
`ShopID` bigint(20) NOT NULL,
`Item` varchar(255) NOT NULL,
`Price` float(5,2) NOT NULL,
`Position` varchar(10) NOT NULL,
PRIMARY KEY  (`ID`),
UNIQUE KEY `ShopID` (`ShopID`,`Item`),
KEY `Item` (`Item`(100)),
KEY `Price` (`Price`)
ENGINE=MyISAM AUTO_INCREMENT=5001 DEFAULT CHARSET=latin1

It may be worth mentioning that I had a Position = 'some-text' in my WHERE clause of my queries. Just didnt mention it here.

Internet Services

Web Development

Digital Marketing

Consumer Tech