1

I want to select the rows with unique "created_by" column and picking the row with minimum "distance". Here is my table data:

id  title           created_by  last_used           distance
668 Lorem Ipsum 1   981         2012-00-00 00:00:00     12
2   Lorem Ipsum 2   981         2012-00-00 00:00:00     20
710 Lorem Ipsum 3   120         2013-01-01 00:00:00     1
769 Lorem Ipsum 4   981         2012-00-00 00:00:00     4

So, I would like to get the following rows:

id  title           created_by  last_used           distance
710 Lorem Ipsum 3   120         2013-01-01 00:00:00     1
769 Lorem Ipsum 4   981         2012-00-00 00:00:00     4

How can I achieve this with a SELECT statement?

Cœur
  • 37,241
  • 25
  • 195
  • 267
Mike Alvim
  • 61
  • 5
  • Well, [what have you tried?](http://www.whathaveyoutried.com) – Marty McVry Apr 11 '13 at 16:32
  • How do you plan to handle two rows having the minimum distance? – mellamokb Apr 11 '13 at 16:33
  • This type of question is very common. See for example: [1](http://stackoverflow.com/questions/1799355), [2](http://stackoverflow.com/questions/11683712), [3](http://stackoverflow.com/questions/8677307), [4](http://stackoverflow.com/questions/12378193), [5](http://stackoverflow.com/questions/10700892) – mellamokb Apr 11 '13 at 16:35
  • Using JOINS as stated in the examples I was able to get the results but the performance is not good. – Mike Alvim Apr 12 '13 at 08:07

1 Answers1

0

I was able to get the results without compromising the performance with this query (avoiding JOINS):

SELECT id, title, created_by, last_used, MIN(distance) 
FROM MyTable GROUP BY created_by;
Mike Alvim
  • 61
  • 5
  • As long as you don't mind the values of `id`, `title`, and `last_used` not necessarily being from the same row as the `MIN(distance)`... that's why the joins are necessary. – mellamokb Apr 12 '13 at 13:36