MySQL GROUP BY & aggregate values

Obtaining associated values

[filmoore]

Hi there,

After having searched around quite a bit for a solution, I shall resort to asking the question myself. (Having already read numerous forum posts, this article (Debunking GROUP BY Myths) [dev.mysql.com] and the MySQL manual on aggregate functions [dev.mysql.com].)

Apologies if I've omitted some info, this is my first time posting to such a forum.

I've got a table of 'offers', where I'm trying to find the best (cheapest) offer for each source, so I initially thought of a query along the lines of :

SELECT id, url, source, MIN(price) FROM offers GROUP BY source

The problem I have is that the GROUP BY returns the first corresponding ID for that source, rather than the corresponding ID & URL for the minimum price that is returned. I tried adding ORDER BY price to the end of the query, but it does the ordering post-grouping...

Any help would be gratefully received!

To give you an idea of the data, in it's simplified form:

id ¦ url ¦ source ¦ price
--------------------------------
1 ¦ ... ¦ 1 ¦ 455
2 ¦ ... ¦ 3 ¦ 10
3 ¦ ... ¦ 2 ¦ 103
4 ¦ ... ¦ 2 ¦ 399
5 ¦ ... ¦ 3 ¦ 254
6 ¦ ... ¦ 1 ¦ 178
7 ¦ ... ¦ 1 ¦ 89

...and the data I'd like out is :

id ¦ url ¦ source ¦ price
--------------------------------
2 ¦ ... ¦ 3 ¦ 10
3 ¦ ... ¦ 2 ¦ 103
7 ¦ ... ¦ 1 ¦ 89

Thank you.

[kenchix1]

select id,url source,min(price) from offers where amount in
(select min(amount) from offers group by source) group by source order by source desc

[kenchix1]

select id,url, source,min(price) from offers where amount in
(select min(amount) from offers group by source) group by source order by source desc

I'm not sure if the URL and Id is the same in your table.

(Sorry, I can't edit my previous post)

[kenchix1]

here's another shorter version:

select id,url,source,amount from offers where price in (select min(price) from offers group by source) 

[ashish21cool]

The query written by Kechchix1 is a perfect solution to you problem.

Thanks.

[filmoore]

Splendid, thank you