Databases
StackOverflow2013
Postsdbo
POMySQL: Select N rows, but with only unique values in one column
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Select N rows, but with only unique values in one column
    text
    copied!<p>Given this data set:</p> <pre><code>ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Ripley Nostromo 2092 6 James T. Kirk Riverside 2233 7 Henry Jones Chicago 1899 </code></pre> <p>I need to find the 3 oldest persons, but only one of every city.</p> <p>If it would just be the three oldest, it would be...</p> <ul> <li>Henry Jones / Chicago</li> <li>Mac Taylor / New York</li> <li>Egon Spengler / New York</li> </ul> <p>However since both Egon Spengler and Mac Taylor are located in New York, Egon Spengler would drop out and the next one (Sarah Connor / Los Angeles) would come in instead.</p> <p>Any elegant solutions?</p> <p><strong>Update:</strong></p> <p>Currently a variation of PConroy is the best/fastest solution:</p> <pre><code>SELECT P.*, COUNT(*) AS ct FROM people P JOIN (SELECT MIN(Birthyear) AS Birthyear FROM people GROUP by City) P2 ON P2.Birthyear = P.Birthyear GROUP BY P.City ORDER BY P.Birthyear ASC LIMIT 10; </code></pre> <p>His original query with "IN" is extremly slow with big datasets (aborted after 5 minutes), but moving the subquery to a JOIN will speed it up a lot. It took about 0.15 seconds for approx. 1 mio rows in my test environment. I have an index on "City, Birthyear" and a second one just on "Birthyear".</p> <p>Note: This is related to...</p> <ul> <li><a href="https://stackoverflow.com/questions/150610/selecting-unique-rows-in-a-set-of-two-possibilities">Selecting unique rows in a set of two possibilities</a></li> <li><a href="https://stackoverflow.com/questions/49404/sql-query-to-get-latest-price">SQL Query to get latest price</a></li> </ul>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload