0

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

+-------+---------------+--------------------------------+------------+
| 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 |

So it returns this

|     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 |
2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by smantscheff
0

Your desired result differs from your statements about it.
Why does (1,query 2,[url]http://yyy.co.uk/,1288051200[/url]) appear twice in your result set? In which respect is it different from the first line? Maybe you are aiming at something like select min(place),query,fullurl,date from places group by query,fullurl,date; But it is really hard to tell without clarification.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.