Changing ORDER BY caluse with LIMIT in MySQL SELECT gives inconsistent set of rows

Question

I was quite surprised to find inconsistent results today from the following MySQL query:

SELECT Research.Focus, Research.Media, Country.Name, GROUP_CONCAT(DISTINCT AskMethod.Name ORDER BY ResearchAskMethod.MethodID SEPARATOR ', ') as AskMethodName, Research.ResearchDate, Research.ResearchID FROM AskMethod INNER JOIN ((Country INNER JOIN Research ON Country.CountryID = Research.CountryID) INNER JOIN ResearchAskMethod ON Research.ResearchID = ResearchAskMethod.ResearchID) ON AskMethod.MethodID = ResearchAskMethod.MethodID WHERE Research.ResearchID=ResearchAskMethod.ResearchID AND Research.ResearchDate=1996 GROUP BY Research.ResearchID ORDER BY Country.Name, Research.Media, AskMethodName, Research.ResearchDate DESC LIMIT 0, 5;

This query gives me 5 rows: ResearchID: 18, 17, 10, 7, 13.

If I simply add DESC to the first ORDER BY argument so that it becomes:

...ORDER BY Country.Name DESC, Research.Media...,

this query gives me a different set of 5 rows: ResearchID: 8, 14, 9, 13, 7

ResearchID is the primary key for the Research table.

It also gives me a different set if I just change the order of the ORDER BY arguments, like:

... ORDER BY Research.Media, Country.Name, AskMethodName...

Could you please help me understand what's going on?

Answered by @Kiley below, and here's the fixed-up query:

SELECT * 
FROM (
    SELECT Research.Focus, Research.Media, Country.Name, GROUP_CONCAT( DISTINCT ValMethod.Name
    ORDER BY ResearchValMethod.MethodID
    SEPARATOR  ', ' ) AS ValMethodName, Research.ResearchDate, Research.ResearchID
    FROM ValMethod
    INNER JOIN (
        (
        Country
        INNER JOIN Research ON Country.CountryID = Research.CountryID
        )
    INNER JOIN ResearchValMethod ON Research.ResearchID = ResearchValMethod.ResearchID
    ) ON ValMethod.MethodID = ResearchValMethod.MethodID
    WHERE Research.ResearchID = ResearchValMethod.ResearchID
    AND Research.ResearchDate = 1996
    GROUP BY Research.ResearchID
    ORDER BY Country.Name, Research.Media, ValMethodName, Research.ResearchDate
) AS Result
ORDER BY Result.Name, Result.Media, ValMethodName, Result.ResearchDate DESC

Answer 1

My response here is largely geared toward SQL Server, but the concept should hold true for MySQL as well. If you are skeptical, you should run my test and verify for yourself.

I think the problem here is that you want the top five results when ordered by your ResearchDate, and then you want to sort THOSE in descending order. According to PinalDave The SELECT TOP X clause (which is the functional equivalent of a subset of LIMIT's capabilities) is the FINAL step in the logical processing of a SELECT query, so it grabs the first X after the results have already been ORDERed.

In case you're curious about the processing order, I found this on the MySQL Reference Manual:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

Here's a quick example that will both demonstrate the problem I think you are seeing, as well as how to fix it:

CREATE TABLE [Test] (
    Number INT PRIMARY KEY
);

INSERT INTO [Test] VALUES (1);
INSERT INTO [Test] VALUES (2);
INSERT INTO [Test] VALUES (3);
INSERT INTO [Test] VALUES (4);
INSERT INTO [Test] VALUES (5);
INSERT INTO [Test] VALUES (6);
INSERT INTO [Test] VALUES (7);
INSERT INTO [Test] VALUES (8);
INSERT INTO [Test] VALUES (9);
INSERT INTO [Test] VALUES (10);

That will get your data set up. Now run the following queries and examine the output:

SELECT TOP 5 * FROM Test ORDER BY Number;

And in MySQL:

SELECT * FROM Test ORDER BY Number LIMIT 5;

This query will produce the following result set:

1
2
3
4
5

Now, check out the difference here:

SELECT TOP 5 * FROM Test ORDER BY Number DESC;

And in MySQL:

SELECT * FROM Test ORDER BY Number DESC LIMIT 5;

Produces:

10
9
8
7
6

Note the differing result set? This is the problem that you're hitting right now. Instead, what you need to do is select the results you want in a subquery, and then order THOSE in an outer query.

SELECT * FROM (
    SELECT TOP 5 * FROM Test ORDER BY Number
) AS MyTest ORDER BY Number DESC

And finally, in MySQL:

SELECT * FROM (
    SELECT * FROM Test ORDER BY Number LIMIT 5
) AS MyTest ORDER BY Number DESC

The result?

5
4
3
2
1

Which I think is what you're looking for.

I am not a MySQL guy, but I also found some neat functionality of LIMIT that allows you to specify the upper and lower bounds of the result set returned, so if you know exactly how many rows to expect in your table (you probably don't, but I figure this might be worth mentioning for your understanding), you could probably so something like this (MySQL example only:)

SELECT * FROM Test ORDER BY Number DESC LIMIT 6, 10

I will take a swing at fixing your query to follow the example I've provided here, but since it's a bit poorly-formatted it's a bit hard to tell what's totally going on...:

SELECT * FROM (SELECT Research.Focus, Research.Media, Country.Name, GROUP_CONCAT(DISTINCT AskMethod.Name ORDER BY ResearchAskMethod.MethodID SEPARATOR ', ') as AskMethodName, Research.ResearchDate, Research.ResearchID FROM AskMethod INNER JOIN ((Country INNER JOIN Research ON Country.CountryID = Research.CountryID) INNER JOIN ResearchAskMethod ON Research.ResearchID = ResearchAskMethod.ResearchID) ON AskMethod.MethodID = ResearchAskMethod.MethodID WHERE Research.ResearchID=ResearchAskMethod.ResearchID AND Research.ResearchDate=1996 GROUP BY Research.ResearchID ORDER BY Country.Name, Research.Media, AskMethodName, Research.ResearchDate) AS Result ORDER BY Country.Name, Research.Media, AskMethodName, Research.ResearchDate DESC;

And for the record, I would recommend cleaning up the layout of your queries a bit so they are easier to understand. Use break lines, white spacing, etc just like you would in PHP or C# or Java or C++ or any other programming language to improve the readability of your code.