Order by and group by

Question

I am trying to show delivery charges for a shop I am building, there are three tables in the database 1 for the service ie Royal Mail, Carrier..., one for the band ie. UK, Europe, Worldwide1 etc.. and one for the charges (qty = weight)

I have a database of three tables that, when joined form the following

+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+
|       name       | qty | serviceID | basis | bandID | initial_charge | chargeID | price | total_price |
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+
| Collect in store |   0 |         3 |       |       1 | 3             | 0.00     | 2     | 0.00        |
| Royal mail       |   0 |         1 |     2 |       4 | 2.00          | 3        | 0.00  | 2.00        |
| Royal mail       |   1 |         1 |     2 |       4 | 2.00          | 4        | 1.00  | 3.00        |
| APC              |   0 |         2 |     1 |       1 | 0.00          | 6        | 5.95  | 5.95        |
+------------------+-----+-----------+-------+---------+---------------+----------+-------+-------------+

Basically what I want to do is (as you can see) Royal Mail has two entries as there are more than one entry in the joined table. What I would like to do is show the highest of the two royal mail entries (I was initially trying to group by service_id) whilst also maintaining the two other services with different service id's

Any assistance would be great as this is driving me mad. I feel like I have tried every combination going!

In the example below the qty (weight) of the items is 3kg

SELECT
    `service`.`name`,
    `charge`.`qty`,
    `service`.`serviceID`,
    `band`.`bandID`,
    `band`.`initial_charge`,
    `charge`.`chargeID`,
    `charge`.`price`,
    `band`.`initial_charge` + `charge`.`price` AS `total_price` 
FROM
    `delivery_band` AS `band` 
LEFT JOIN
    `delivery_charge` AS  `charge`
        ON 
            `charge`.`bandID` =  `band`.`bandID` 
        AND
            `charge`.`qty` <  '3'
LEFT JOIN
    `delivery_service` AS  `service`
        ON
            `service`.`serviceID` =  `band`.`serviceID` 
WHERE
    FIND_IN_SET(  '225',  `band`.`accepted_countries` ) 
AND
(
    `band`.`min_qty` >=  '3'
OR
    `band`.`min_qty` =  '0'
)
AND
(
    `band`.`max_qty` <=  '3'
OR
    `band`.`max_qty` =  '0'
)

delivery_service

+-----------+------------------+
| serviceID |       name       |
+-----------+------------------+
|         1 | Royal mail       |
|         2 | APC              |
|         3 | Collect in store |
+-----------+------------------+

delivery_band

+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+
| bandID | serviceID |      name       | initial_charge | min_qty | max_qty |                  accepted_countries                   |
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+
|      1 |         2 | UK Mainland     | 0.00           |       0 |       0 | 225                                                   |
|      2 |         2 | UK Offshore     | 14.00          |       0 |       0 | 240                                                   |
|      3 |         3 | Bradford Store  | 0.00           |       0 |       0 | 225                                                   |
|      4 |         1 | UK              | 2.00           |       0 |       0 | 225                                                   |
|      5 |         2 | World wide      | 15.00          |       0 |       0 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20... |
|      6 |         1 | World wide Mail | 5.00           |       0 |       0 | 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20... |
+--------+-----------+-----------------+----------------+---------+---------+-------------------------------------------------------+

delivery_charge

+----------+--------+-----+-------+
| chargeID | bandID | qty | price |
+----------+--------+-----+-------+
|        1 |      2 |   0 | 5.00  |
|        2 |      3 |   0 | 0.00  |
|        3 |      4 |   0 | 0.00  |
|        4 |      4 |   1 | 1.00  |
|        5 |      4 |   5 | 3.00  |
|        6 |      1 |   0 | 5.95  |
|        7 |      1 |  10 | 10.95 |
|        8 |      2 |  10 | 14.00 |
|        9 |      5 |   0 | 0.00  |
|       10 |      5 |   3 | 5.00  |
|       11 |      5 |   6 | 10.00 |
|       12 |      5 |   9 | 15.00 |
|       13 |      6 |   0 | 0.00  |
|       14 |      6 |   2 | 5.00  |
|       15 |      6 |   4 | 10.00 |
|       16 |      6 |   6 | 15.00 |
+----------+--------+-----+-------+

When I tried adding the charge table as a sub query and then limiting that query it gave me NULL's for all the charge table fields

If I try the following query:

SELECT
    `service`.`name`,
    `charge`.`qty`,
    `service`.`serviceID`,
    `band`.`bandID`,
    `band`.`initial_charge`,
    `charge`.`chargeID`,
    MAX( `charge`.`price` ) AS `price`,
    `band`.`initial_charge` + `charge`.`price` AS `total_price` 
FROM
    `delivery_band` AS `band` 
LEFT JOIN
    `delivery_charge` AS  `charge`
        ON 
            `charge`.`bandID` =  `band`.`bandID` 
        AND
            `charge`.`qty` <  '3'
LEFT JOIN
    `delivery_service` AS  `service`
        ON
            `service`.`serviceID` =  `band`.`serviceID` 
WHERE
    FIND_IN_SET(  '225',  `band`.`accepted_countries` ) 
AND
(
    `band`.`min_qty` >=  '3'
OR
    `band`.`min_qty` =  '0'
)
AND
(
    `band`.`max_qty` <=  '3'
OR
    `band`.`max_qty` =  '0'
)
GROUP BY
    `service`.`serviceID`

I get this returned:

+------------------+-----+-----------+--------+----------------+----------+-------+-------------+
|       name       | qty | serviceID | bandID | initial_charge | chargeID | price | total_price |
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+
| Royal mail       |   0 |         1 |      4 | 2.00           |        3 | 1.00  | 2.00        |
| APC              |   0 |         2 |      1 | 0.00           |        6 | 5.95  | 5.95        |
| Collect in store |   0 |         3 |      3 | 0.00           |        2 | 0.00  | 0.00        |
+------------------+-----+-----------+--------+----------------+----------+-------+-------------+

Which looks fine in principle until you realise that the chargeID = 3 has a price of 0.00 and yet the table is showing a price of 1.00 so the values seem to have become disassociated

http://www.sqlfiddle.com/#!2/9665c4/4 - can you see what the issue is? - thanks for the link btw - didn't know this existed! — Eth, Jun 13 '13 at 13:51
The fiddle is so that people can toy with your data while coming up with a result. Your original question didn't explicitely state that you not only want the maximum price but also the id to go with it. I guess that should be clearer now. — MvG, Jun 13 '13 at 13:57
Sorry about that :( I guess it was in my head but not conveyed — Eth, Jun 13 '13 at 14:02

score 0 · Accepted Answer · edited May 23 '17 at 12:05

What I would like to do is show the highest of the two royal mail entries

You can use MAX to obtain the maximum of a given column, e.g.

SELECT … MAX(charge.price) … FROM …

If you absolutely need the other columns (like charge.chargeID) to match, things will become a lot more complicated. So make sure you actually need that. For details on the general idea behind this kind of query, have a closer look at Select one value from a group based on order from other columns. Adapting this answer by @RichardTheKiwi, I came up with the following query:

SELECT s.name,
       c.qty,
       s.serviceID,
       b.bandID,
       b.initial_charge,
       c.chargeID,
       c.price,
       b.initial_charge + c.price AS total_price
FROM delivery_band AS b,
     delivery_service AS s,
    (SELECT chargeID, price, qty,
            @rowctr := IF(bandId = @lastBand, @rowctr+1, 1) AS rowNumber,
            @lastBand := bandId AS bandId
     FROM (SELECT @rowctr:=0, @lastBand:=null) init,
          delivery_charge
     WHERE qty < 3
     ORDER BY bandId, price DESC
    ) AS c
WHERE FIND_IN_SET(225, b.accepted_countries)
  AND (b.min_qty >= 3 OR B.min_qty = 0)
  AND (b.max_qty <= 3 OR B.max_qty = 0)
  AND s.serviceID = b.serviceID
  AND c.bandID = b.bandID
  AND c.rowNumber = 1

See this fiddle for the corresponding output. Note that I only do inner queries, not left queries, since that seems sufficient for the query in question, and keeps things a lot more readable so you can concentrate on the important parts, i.e. those involving rowNumber. The idea is that the subquery generates row numbers for the items of the same band, resetting them for the next band. When you select only rows with rowNumber being 1, you only get the highest price, with all other columns associated with that.

Great start. Spotted a slight issue however, MAX( charge.price ) and GROUP BY service.serviceId gives me the right answer. However it looses the association of the charge_id. So it gives me the highest price but not related charge_id that price comes from in the table — Eth, Jun 13 '13 at 13:31
I have added some more to my original question as I cannot reply in full as a comment — Eth, Jun 13 '13 at 13:44
@Eth: my original answer clearly stated that `MAX` will only affect a single column, and not cause other columns to match that. At that time, it wasn't clear whether you'd actually need other columns to match. After your comment I've updated my answer with an elaborate query to get this done. — MvG, Jun 13 '13 at 14:17
Sorry for not being clear enough in the first instance. You have cracked it - Thanks :-) — Eth, Jun 13 '13 at 15:20

Order by and group by

delivery_service

delivery_band

delivery_charge

1 Answers1