Using "ORDER BY" modifies query results

I recently worked on a SQL tutorial for Self Join .

The table definitions provided are pretty simple:

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

      

Here, the attribute stop

refers to the id

stop being given a name in the table stops

.

If you look at problem # 10, I will add the following solution that the site indicates is giving 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 what is id

for Craiglockhart

- 53

and id

for Sighthill

- 213

. Everything is fine so far.

But if I add to query ORDER BY name

or ORDER BY bstops.name

results and the 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

      

In particular, for example, there are now 8 lines for London Road instead of 4.

I have been playing around with different queries trying to understand these results. Is there any reason I ORDER BY

should change the actual query results other than sorting?

+3


source to share


2 answers


You can limit the number of records returned using a keyword LIMIT

in your query. I suppose they add something like

... LIMIT 50 ...

      

to the request you are submitting to avoid too many records to be returned.

Then, the first 50 records that are displayed may be different for different expressions ORDER BY

, as the records are ordered first and then the ordered result is limited.

Let's assume 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

      

and

SELECT * FROM table ORDER BY Name LIMIT 2;

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

      

+1


source


If the Scottish buses do not stop at the same stop regardless of the direction of travel, I think the answer provided by SQLZOO is incorrect.

I think you can mimic your answer with this request ...

 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 ...

 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';

      

+1


source







All Articles