0

I have a table structure like:

Name    Time

Joe     2012-02-22 22:11:26.0
Joe     2012-02-22 21:01:23.0
Sue     2012-02-22 20:57:10.0
john    2012-02-22 18:13:36.0
Fred    2012-02-22 16:56:57.0
Joe     2012-02-22 14:38:45.0
Joe     2012-02-22 14:38:45.0
Ralph   2012-02-22 14:26:20.0
...     ...
(more Names and Times)
...     ...
john    2010-03-10 15:27:39.0
john    2010-03-10 15:46:59.0

I'm looking for the Top 3 Names Ordered by Time. So the result would be:

Joe, Sue, john

I began with a query like:

SELECT Name FROM table ORDER BY LOWER(TIME) DESC

So I wanted to Limit the result with:

SELECT Name FROM table ORDER BY LOWER(TIME) DESC LIMIT 0 , 3

But the result shows the first 3 rows as aspected: 

Joe, Joe, Sue

Now I'm trying to group the query by Name first:

SELECT Name FROM table GROUP BY Name ORDER BY LOWER(TIME) DESC LIMIT 0 , 3

And yes that gives me 3 different names but in a unreproducible order. Am I missing something here? What would be the correct syntax here?

TNT_Larsn
  • 73
  • 8

1 Answers1

2
SELECT DISTINCT Name
FROM table
ORDER BY Time DESC
LIMIT 3

So basically, you just need to add DISTINCT to your second query - this tells MySQL to select only unique values. There's no need in grouping. UPD: this is wrong, as ORDER BY will be applied after DISTINCT, as in most cases DISTINCT will use GROUP BY.

P.S. I don't see why you use LOWER. This function makes a string lower-case. But you're not operating strings, as the second column is of type DATETIME (I assume)?

UPD: You should use grouping, here's an example:

SELECT Name
FROM table
GROUP BY Name
ORDER BY MAX(Time) DESC
LIMIT 3

Here the important thing is to take the maximum date/time from each group and order by that.

Dmytro Shevchenko
  • 33,431
  • 6
  • 51
  • 67
  • tried this. But also shows not the correct names. And yes I don't need the 'LOWER' function – TNT_Larsn Apr 13 '12 at 23:55
  • This is really strange. What does it show and what would you expect? – Dmytro Shevchenko Apr 13 '12 at 23:56
  • I updated my answer. If it still doesn't work, it means that ordering doesn't work as expected. Check that the column has a type of `DATETIME`. – Dmytro Shevchenko Apr 14 '12 at 00:02
  • Couldn't figure out the logic yet. The result shows name from somewhere in the middle. I would expect `Joe, Sue, john` – TNT_Larsn Apr 14 '12 at 00:02
  • YEPP. Your Updated Query was working as expected. Thank you very much! – TNT_Larsn Apr 14 '12 at 00:06
  • 1
    The first query is wrong because it essentially does: `SELECT Name FROM table GROUP BY Name ORDER BY time`. But there are multiple Times per Name, so becasue the Ordering is done **after** the grouping, a more or less random Time is chosen - and used for ordering. – ypercubeᵀᴹ Apr 14 '12 at 00:10
  • @ypercube I thought the difference was that `ORDER BY` is applied before `DISTINCT`, but after `GROUP BY`. In other words, I thought that when you use `DISTINCT`, the result set is first ordered, and then rows are selected in the same order, but duplicate lines are not returned. Apparently, I was wrong about that. – Dmytro Shevchenko Apr 14 '12 at 00:16
  • No, AFAIK, `ORDER BY` is applied after `DISTINCT` – ypercubeᵀᴹ Apr 14 '12 at 00:26
  • 1
    @ypercube you are right: http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html – Dmytro Shevchenko Apr 14 '12 at 00:30
  • Shameless self-ad: [SELECT: Order of execution](http://stackoverflow.com/questions/6545664/using-case-expression-column-in-where-clause/6545685#6545685) – ypercubeᵀᴹ Apr 14 '12 at 00:35