Member Avatar for matthew1471

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
  • 130 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for smantscheff

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.