Question 245740 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Selecting column from a table with different orders

I have a table customer. The table has columns cust_id, member, date. The cust_id is a primary key (auto-increment), member can have either 1 or 0 and date contains the timestamp. I want to run a query on MySQL which let me select the cust_id with the member = 1 and order by date and then select cust_id with member = 0 and order by cust_id. Both should be in decending order (means from latest or biggest to oldest or smallest). I have tried UNION but the SQL gave error as the column date does not exist.

SELECT `cust_id` FROM `customer` WHERE `member` = 1 ORDER BY `date` DESC
SELECT `cust_id` FROM `customer` WHERE `member` = 0 ORDER BY `cust_id` DESC

I want to merge the result of these 2 queries.

I am expecting an output like this:

cust_id member date
25 1 2022-05-01 22:22:22
30 1 2021-01-11 05:23:11
50 0 2023-09-01 22:22:22
49 0 2023-08-21 20:20:20
46 0 2023-08-20 19:20:21

but i think i am missing something.

Plese help.

Tags:

2

Answers


  1. 0

    You can use parthesis in MySQL to get it to work

    CREATE tABLe customer ( `cust_id` int,`member` int,`date` datetime)

    INSERT INTO customer
    (`cust_id`, `member`, `date`)
VALUES
    (25, 1, '2022-05-01 22:22:22'),
    (30, 1, '2021-01-11 05:23:11'),
      (31, 1, '2022-05-01 21:22:22'),
    (32, 1, '2021-01-11 06:23:11'),
    (50, 0, '2023-09-01 22:22:22'),
    (49, 0, '2023-08-21 20:20:20'),
    (46, 0, '2023-08-20 19:20:21'),
      (50, 0, '2023-09-01 22:22:22'),
    (51, 0, '2023-08-21 20:20:20'),
    (52, 0, '2023-08-20 19:20:21')
;


    Records: 10  Duplicates: 0  Warnings: 0

    (SELECT `cust_id`,`member`,`date` FROM `customer` WHERE `member` = 1 ORDER BY `date` DESC LIMIT 10000000)
UNION 
(SELECT `cust_id`,`member`,`date` FROM `customer` WHERE `member` = 0 ORDER BY `cust_id` DESC  LIMIT 10000000)
    cust_id member date
    25 1 2022-05-01 22:22:22
    31 1 2022-05-01 21:22:22
    32 1 2021-01-11 06:23:11
    30 1 2021-01-11 05:23:11
    52 0 2023-08-20 19:20:21
    51 0 2023-08-21 20:20:20
    50 0 2023-09-01 22:22:22
    49 0 2023-08-21 20:20:20
    46 0 2023-08-20 19:20:21

    fiddle

    Login or Signup to reply.
  2. 0

    Select all and sort by member and date:

    SELECT cust_id FROM customer ORDER BY member DESC, date DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search