List:General Discussion« Previous MessageNext Message »
From:Matt Horrocks Date:October 27 2010 6:34pm
Subject:Selecting all minimum values
View as plain text  
Hi,

I have the following table. How can I select the lowest `place` for each
`query` for each `date` (some queries appear twice as they have a different
`fullurl`).

Thanks for your help.

Matt
[code]
+-------+---------------+--------------------------------+------------+
| place | query         | fullurl                        | date       |
+-------+---------------+--------------------------------+------------+
|     2 | query 1       | http://yyy.co.uk/zzz.html      | 1288051200 |
|     2 | query 2       | http://yyy.co.uk/xxx.html      | 1288051200 |
|     1 | query 2       | http://yyy.co.uk/              | 1288051200 |
|     5 | query 3       | http://yyy.co.uk/              | 1288051200 |
|     3 | query 1       | http://yyy.co.uk/xxx.html      | 1288051200 |
|     3 | query 2       | http://yyy.co.uk/zzz.html      | 1288051200 |
|     1 | query 1       | http://yyy.co.uk/              | 1288051200 |
|     2 | query 2       | http://yyy.co.uk/xxx.html      | 1287964800 |
|     3 | query 2       | http://yyy.co.uk/zzz.html      | 1287964800 |
|     1 | query 1       | http://yyy.co.uk/              | 1287964800 |
|     2 | query 1       | http://yyy.co.uk/zzz.html      | 1287964800 |
|     3 | query 1       | http://yyy.co.uk/xxx.html      | 1287964800 |
|     5 | query 3       | http://yyy.co.uk/              | 1287964800 |
|     1 | query 2       | http://yyy.co.uk/              | 1287964800 |
|     1 | query 1       | http://yyy.co.uk/              | 1287878400 |
|     2 | query 1       | http://yyy.co.uk/zzz.html      | 1287878400 |
|     3 | query 1       | http://yyy.co.uk/xxx.html      | 1287878400 |
|     5 | query 3       | http://yyy.co.uk/              | 1287878400 |
|     1 | query 2       | http://yyy.co.uk/              | 1287878400 |
|     2 | query 2       | http://yyy.co.uk/xxx.html      | 1287878400 |
|     3 | query 2       | http://yyy.co.uk/zzz.html      | 1287878400 |
[/code]


So it returns this
[code]
|     1 | query 2       | http://yyy.co.uk/              | 1288051200 |
|     5 | query 3       | http://yyy.co.uk/              | 1288051200 |
|     1 | query 1       | http://yyy.co.uk/              | 1288051200 |

|     1 | query 1       | http://yyy.co.uk/              | 1287964800 |
|     5 | query 3       | http://yyy.co.uk/              | 1287964800 |
|     1 | query 2       | http://yyy.co.uk/              | 1287964800 |

|     1 | query 1       | http://yyy.co.uk/              | 1287878400 |
|     5 | query 3       | http://yyy.co.uk/              | 1287878400 |
|     1 | query 2       | http://yyy.co.uk/              | 1287878400 |
[/code]

Thread
Selecting all minimum valuesMatt Horrocks27 Oct
  • Re: Selecting all minimum valuesPeter Brawley27 Oct