I am trying to do a complex order by or possibly a group by and then order by. The table name is curtomer_updates. What I want it to do is first of all sort by descending order on the last_updated_date and then have a unique cust_id together sorted by last_updated_date. Below is an example of what it is right now and the expected result. Thank you for any help.

Table normally sorted

Expected result

CodePudding user response：

SELECT *
FROM curtomer_updates
ORDER BY 
    MAX(Last_updated_date) OVER (PARTITION BY cust_id) DESC
    ,Last_updated_date DESC

using sample set:

SELECT *
FROM ( VALUES  
        (1,1223,  'Name',           '2021-11-01 12:23', 'Frodo Baggins'),
        (2,9999,  'address',        '2021-12-02 19:23', 'Legolas'),
        (3,2200,  'phone',          '2021-12-03 23:00', 'Bilbo Baggins'),
        (4,2200,  'Name',           '2022-01-04 02:23', 'Bilbo Baggins'),
        (5,9999,  'phone',          '2022-02-05 12:23', 'Golum'),
        (6,9999,  'address',        '2022-03-06 20:00', 'Sauron'),
        (7,1223,  'address',        '2022-04-07 01:24', 'Gandalf'),
        (8,2200,  'email',          '2022-05-08 12:50', 'Some Urkai'),
        (9,3412,  'email, phone',   '2022-06-08 08:45', 'Golum'),
        (10,1223, 'address',        '2022-07-10 00:23', 'Pippin'),
        (11,3412, 'email, address', '2022-09-22 16:48', 'Gandalf')
)sub(Update_ID, Cust_Id , Field_updated , Last_updated_date, Updated_by)
ORDER BY 
    MAX(Last_updated_date) OVER (PARTITION BY cust_id) DESC
    ,Last_updated_date DESC

Returns:

    Update_ID   Cust_Id     Field_updated       Last_updated_date       Updated_by
    ----------------------------------------------------------------------------------
    11          3412        email, address      2022-09-22 16:48        Gandalf
    9           3412        email, phone        2022-06-08 08:45        Golum
    10          1223        address             2022-07-10 00:23        Pippin
    7           1223        address             2022-04-07 01:24        Gandalf
    1           1223        Name                2021-11-01 12:23        Frodo Baggins
    8           2200        email               2022-05-08 12:50        Some Urkai
    4           2200        Name                2022-01-04 02:23        Bilbo Baggins
    3           2200        phone               2021-12-03 23:00        Bilbo Baggins
    6           9999        address             2022-03-06 20:00        Sauron
    5           9999        phone               2022-02-05 12:23        Golum
    2           9999        address             2021-12-02 19:23        Legolas

CodePudding user response：

You can do:

select t.*
from customer_updates t
join (
  select cust_id, dense_rank() over(order by max(last_updated_date)) as rk
  from customer_updates
  group by cust_id
) x on x.cust_id = t.cust_id
order by x.rk desc, t.last_updated_date desc

Result:

 update_id  Cust_Id  Field_updated   Last_updated_date  Updated_by    
 ---------- -------- --------------- ------------------ ------------- 
 11         3412     email, address  2022-09-22 16:48   Gandalf       
 9          3412     email, phone    2022-06-08 08:45   Golum         
 10         1223     address         2022-07-10 00:23   Pippin        
 7          1223     address         2022-04-07 01:24   Gandalf       
 1          1223     Name            2021-11-01 12:23   Frodo Baggins 
 8          2200     email           2022-05-08 12:50   Some Urkai    
 4          2200     Name            2022-01-04 02:23   Bilbo Baggins 
 3          2200     phone           2021-12-03 23:00   Bilbo Baggins 
 6          9999     address         2022-03-06 20:00   Sauron        
 5          9999     phone           2022-02-05 12:23   Golum         
 2          9999     address         2021-12-02 19:23   Legolas       

See running example at db<>fiddle.

CodePudding user response：

SELECT * FROM curtomer_updates ORDER BY last_updated_date, cust_id DISC;