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?
source to share
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
source to share
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';
source to share