CODEWITHSUNDEEP
mysqlsql
lurker
lurker

Reputation: 58244

Using "ORDER BY" changes results of query

I was recently working through the SQL tutorial for Self Join.

The table definitions given are quite simple:

stops(id, name)
route(num, company, pos, stop)

Here, the stop attribute refers to the id of the stop which is provided a name in the stops table.

If you look at problem #10, I put in the following solution which the site indicates yields the correct results:

SELECT distinct a.num, a.company, bstops.name, e.num, e.company FROM route a
   JOIN route b ON (a.company = b.company AND a.num = b.num)
   JOIN stops bstops ON bstops.id = b.stop
   JOIN
     (SELECT c.company, c.num, d.stop as d_stop FROM route c
        JOIN route d ON (c.company = d.company AND c.num = d.num)
        WHERE c.stop = 213) e
     ON e.d_stop = b.stop
   WHERE a.stop = 53

Here, I already know that the id for Craiglockhart is 53, and the id for Sighthill is 213. All good so far.

But if I add an ORDER BY name or ORDER BY bstops.name to the query, the results change and more results are found:

SELECT distinct a.num, a.company, bstops.name, e.num, e.company FROM route a
   JOIN route b ON (a.company = b.company AND a.num = b.num)
   JOIN stops bstops ON bstops.id = b.stop
   JOIN
     (SELECT c.company, c.num, d.stop as d_stop FROM route c
        JOIN route d ON (c.company = d.company AND c.num = d.num)
        WHERE c.stop = 213) e
     ON e.d_stop = b.stop
   WHERE a.stop = 53 ORDER BY name

Specifically, for example, there are now 8 rows for London Road instead of just 4.

I've been playing with different queries trying to understand these results. Is there a reason ORDER BY should change the actual results of the query besides sorting?

Upvotes: 3

Views: 273

Answers (2)

Strawberry
Strawberry

Reputation: 33935

Unless Scottish buses stop at the same stop regardless of the direction of travel, then I think the answer provided by SQLZOO is wrong.

I think you can emulate their answer with this query...

 SELECT a.num first_bus
      , a.company first_company
      , s1.name departing_from
      , s2.name interchange
      , d.num second_bus
      , d.company second_company
      , s3.name arriving_at
   FROM route a
   JOIN route b
     ON b.num = a.num

    AND b.pos <> a.pos

   JOIN route c
     ON c.stop = b.stop
   JOIN route d
     ON d.num = c.num

    AND d.pos <> c.pos

   JOIN stops s1
     ON s1.id = a.stop
   JOIN stops s2
     ON s2.id = b.stop
   JOIN stops s3
     ON s3.id = d.stop
  WHERE s1.name = 'Craiglockhart'
    AND s3.name = 'Sighthill';

However, I think the correct answer would be the result of...

 SELECT a.num first_bus
      , a.company first_company
      , s1.name departing_from
      , s2.name interchange
      , d.num second_bus
      , d.company second_company
      , s3.name arriving_at
   FROM route a
   JOIN route b
     ON b.num = a.num

    AND b.pos > a.pos

   JOIN route c
     ON c.stop = b.stop
   JOIN route d
     ON d.num = c.num

    AND d.pos > c.pos

   JOIN stops s1
     ON s1.id = a.stop
   JOIN stops s2
     ON s2.id = b.stop
   JOIN stops s3
     ON s3.id = d.stop
  WHERE s1.name = 'Craiglockhart'
    AND s3.name = 'Sighthill';

Upvotes: 1

Andreas Fester
Andreas Fester

Reputation: 36630

You can limit the number of records returned by using the LIMIT keyword on your query. I suppose that they are adding something like

... LIMIT 50 ...

to the query you are submitting, in order to avoid too many records to be returned.

Then, these first 50 records which are displayed can differ for different ORDER BY expressions, since the records are ordered first and then the ordered result is limited.

Suppose you have this table:

Id Name
--+-------
0 |Andreas
1 |Lurker
2 |Gordon
3 |John

Then,

SELECT * FROM table ORDER BY Id LIMIT 2;

Id Name
--+-------
0 |Andreas
1 |Lurker

while

SELECT * FROM table ORDER BY Name LIMIT 2;

Id Name
--+-------
0 |Andreas
2 |Gordon

Upvotes: 1

Related Questions