SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

hello,

i have following table

id name value day
1 first 3 2007-12-01
2 secon 6 2007-12-01
3 third 9 2007-12-01
4 first 1 2007-12-03
5 first 3 2007-12-03
6 first 3 2007-12-03
7 fourt 2 2007-12-12
8 third 0 2007-12-12
9 secon 4 2007-12-12
10 first 3 2007-12-12
11 first 3 2007-12-12

What I need is minimum of value per day and the name of minimum value,
if there are two names, then
one of them

if I do

select *, min(value) as minimum from table group by date

then it works fine, but name does not depend on minimum value

with join I cannot use minimum value, because it is calculated later

is there a way to do that in one query?

Thanks!

#2 (**permalink**) 02-28-2008, 10:30 AM

Michael wrote:
> hello,
>
> i have following table
>
> id name value day
> 1 first 3 2007-12-01
> 2 secon 6 2007-12-01
> 3 third 9 2007-12-01
> 4 first 1 2007-12-03
> 5 first 3 2007-12-03
> 6 first 3 2007-12-03
> 7 fourt 2 2007-12-12
> 8 third 0 2007-12-12
> 9 secon 4 2007-12-12
> 10 first 3 2007-12-12
> 11 first 3 2007-12-12
>
> What I need is minimum of value per day and the name of minimum value,
> if there are two names, then
> one of them
>
> if I do
>
> select *, min(value) as minimum from table group by date
>
> then it works fine, but name does not depend on minimum value
>
> with join I cannot use minimum value, because it is calculated later
>
> is there a way to do that in one query?
>
> Thanks!

Wow, it's at least 2 weeks since someone asked this. Seriously this is one
of the ,most common questions on this group.

Search the archives for "strawberry query"

Full explanation of how it works is here:
http://dev.mysql.com/doc/refman/5.0/...group-row.html
(you want the third query on the page, the one with the LEFT JOIN)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

max() and