1. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161

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

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by anjanesh
Though I was wondering if theres a way to get it done this way
no, not that way

3. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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.
Last edited by anjanesh; 12-19-07 at 03:46.

4. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Take a look at GROUP BY

5. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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?

6. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

7. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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```

8. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
okay, you are right, you would use DISTINCT this way

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

9. Registered User
Join Date
Nov 2006
Location
Quebec
Posts
172
Code:
```SELECT `Item`, MAX(Price)
FROM table1
GROUP BY Item```
Simple enough ? Think that's what you want!

10. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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```

11. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

12. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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```

13. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
what indexes have you defined?

14. Registered User
Join Date
Feb 2005
Location
Mumbai, India
Posts
161
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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•