Mysql - i want to display old orders first from orders table based one company and followed by another company like wise

vaiheesh
July 22, 2024
66 views
0 votes
2 Answers

+----------+------------+
|       id |       name |
+----------+------------+
|        1 |   storeone | 
|        2 |   storetwo | 
|        3 |  storethree| 
+----------+------------+



+----------+------------+------------+
|       id |    storeid | date       | 
+----------+------------+------------+
|        1 |          1 | 2024-07-01 | 
|        2 |          2 | 2024-06-01 | 
|        3 |          3 | 2024-07-22 | 
|        4 |          2 | 2024-06-11 | 
|        5 |          3 | 2024-06-14 | 
+----------+------------+------------+

i have used the following query

SELECT `o`.*,`s`.`name` FROM `orders` as o LEFT JOIN `stores` as s
    ON s.id = o.storeid ORDER BY o.date,s.id;

am getting the following result

+----------+------------+------------+-------------
|       id |    storeid | date       | name
+----------+------------+------------+-------------
|        2 |          2 | 2024-06-01 | storetwo   |
|        4 |          2 | 2024-06-11 | storetwo   |
|        5 |          3 | 2024-06-14 | storethree |
|        1 |          1 | 2024-07-01 | storeone   |
|        3 |          3 | 2024-07-22 | storethree |
+----------+------------+------------+-------------

but i want like this

+----------+------------+------------+-------------
|       id |    storeid | date       | name
+----------+------------+------------+-------------
|        2 |          2 | 2024-06-01 | storetwo   |
|        4 |          2 | 2024-06-11 | storetwo   |
|        5 |          3 | 2024-06-14 | storethree |
|        1 |          1 | 2024-07-22 | storethree |
|        3 |          3 | 2024-07-01 | storeone |
+----------+------------+------------+-------------

Tags: mysql sql

Answers

- C_B
- July 22, 2024 at 3:58 pm
- 0 votes
0
Others have noted that your example data is incongruous so it’s difficult to be sure what you’re looking for. However based on the title of your post it sounds like you simply want to order by company first, and then get the oldest record per company. If that is the case you simply need to reverse your ORDER BY clause:
```
SELECT `o`.*,`s`.`name` FROM `orders` as o LEFT JOIN `stores` as s
    ON s.id = o.storeid ORDER BY s.id,o.date ASC;
```
Login or Signup to reply.

- PSalmon
- July 22, 2024 at 4:13 pm
- 0 votes
0
Derive something else to order by based on the min date for every storeid eg
```
with cte as
(select storeid , min(date) dt from o group by storeid ),
cte1 as
(select storeid,rank() over (order by dt) sortorder from cte)
select * 
from  o
join cte1 on cte1.storeid = o.storeid
join s on s.id = cte1.storeid
order by cte1.sortorder,date;
```
https://dbfiddle.uk/-2M9B5ZZ

IS there a rule if 2 or more stores have the same min date?
Login or Signup to reply.