sql query execution order question for group by and select

Issue

the table structure:

create table t_hr_ship (
shipment_id int,
shipper_id int,
date_time date,
pickup_state varchar(20),
dropoff_state varchar(20));

Here are some data in this table:

insert into t_hr_ship values
(1, 1, "2018-01-01", "WA", "OR"),
(2, 1, "2018-01-02", "WA", "OR"),
(3, 1, "2018-01-03", "WA", "OR"),
(4, 1, "2018-01-04", "WA", "OR"),
(5, 2, "2018-01-05", "WA", "OR"),
(6, 3, "2018-01-06", "WA", "OR"),
(7, 2, "2018-02-01", "OR", "WA"),
(8, 4, "2018-02-02", "OR", "WA"),
(9, 3, "2018-02-03", "WA", "CA"),
(10, 5, "2018-02-04", "CA", "OR"),
(11, 2, "2018-03-05", "WA", "TX"),
(12, 3, "2018-01-06", "OR", "CA");

the question is to get top 3 busiest routes in Jan and Feb. note that the route is same for “WA” to “OR” and “OR” to “WA” (the order of two end points don’t matter as long as they are the same two end ports).
the solution is as below:

select case when s.pickup_state < s.dropoff_state then s.pickup_state else s.dropoff_state end as pickup, 
       case when s.pickup_state > s.dropoff_state then s.pickup_state else s.dropoff_state end as dropoff,
       count(s.shipment_id) as no_of_shipment
from t_hr_ship s
where month(s.date_time) in ("01","02")
group by pickup, dropoff
order by no_of_shipment desc
limit 3;

this does get what I expect. my question is: I read from online resource that the sql query execution order is from -> where -> group -> having -> select -> order by -> limit", if this is true than this solution should not work because thepickupanddropoffdefined inselectcan't be used ingroup by`. am I missing anything?

Solution

You can use least() and greatest() to group by consistently:

select 
    least(pickup_state, dropoff_state) pickup,
    greatest(pickup_state, dropoff_state) dropoff,
    count(*) as no_of_shipment
from t_hr_ship s
where month(date_time) in (1, 2)
group by pickup, dropoff
order by no_of_shipment desc
limit 3;

Note that, unlike other RDBMS, MySQL allows the use of column aliases in the GROUP BY clause (and as well in the ORDER BY clause, but this is common in most RDMS).

Demo on DB Fiddle:

pickup | dropoff | no_of_shipment
:----- | :------ | -------------:
OR     | WA      |              8
CA     | OR      |              2
CA     | WA      |              1

Answered By – GMB

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published