-1

I have a table A with columns id, age.

Two queries below return different result and I don't know why:

SELECT distinct a.id id FROM A a ORDER BY a.age DESC
SELECT distinct a.id id FROM A a ORDER BY a.age DESC LIMIT 10 OFFSET 0

Any ideas? I would expect the second query to return the first 10 results of the first query.

EDIT:

What I forgot to say is that there are rows with the same age. So I think it has something to do with it.

julius_am
  • 1,332
  • 2
  • 19
  • 38

2 Answers2

0

I am surprised they work. In most databases you would get a syntax error, because a.age is not in the select. I know that MySQL extends the GROUP BY to allow the use of un-aggregated columns. I presume it has the same reasoning for SELECT DISTINCT. That is, age comes from an indeterminate row for each id. It is so indeterminate that it might change from one query to another.

Here are two appropriate ways to write the query:

SELECT distinct a.id, a.age
FROM A a
ORDER BY a.age DESC;

or:

SELECT a.id
FROM A a
ORDER BY MIN(a.age) DESC;   -- Or perhaps MAX(a.age) or AVG(a.age)

These should have more stable results, with or without the LIMIT.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

Found a solution.

I'm not sure why it's happening. I'm using mysql and maybe the implementation of the query when adding limit is different from when without it. Any way, I added in the ORDER BY a.id. This kept the order when adding limit.

So this is how the query looks:

SELECT distinct a.id id FROM A a ORDER BY a.age DESC, a.id LIMIT 10 OFFSET 0
julius_am
  • 1,332
  • 2
  • 19
  • 38
  • id is indexed, right? While it's obviously good practice to order by the columns in the select, I'm surprised that this actually has any impact on the result set. – Strawberry Feb 01 '16 at 15:59