Selecting all minimum values

Selecting all minimum values

am 27.10.2010 20:34:07 von Matt Horrocks

--20cf3054ab757d854104939d78be
Content-Type: text/plain; charset=ISO-8859-1

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]

--20cf3054ab757d854104939d78be--

Re: Selecting all minimum values

am 27.10.2010 20:39:03 von Peter Brawley

Is this what you mean?

select query, date, min(place) from tbl group by query, date;

PB

-----

On 10/27/2010 1:34 PM, Matt Horrocks wrote:
> 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]
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org