CODEWITHSUNDEEP
sqlmariadbgroupwise-maximum
code gig
code gig

Reputation: 67

Select distinct on one column and return all other columns

I'm trying to find distinct orders based on the modified date. I want to sort it and find the latest modified order.

I tried to do as in postgreSQL. select distinct on (orders) * from the table; But this throws an error in case of mariaDB

Orders          Modified Date
------          ---------------
23031185    2019-09-24 19:36:51
23031185    2019-09-24 22:01:57
23031185    2019-09-24 19:32:28
23031185    2019-09-24 19:33:25
23031185    2019-09-24 19:33:18



The expected output should be the latest one 

Orders          Modified Date
------          ---------------
23031185    2019-09-24 22:01:57

I also have other columns. I need to get all distinct rows based on orders column with all other columns.

Upvotes: 1

Views: 103

Answers (2)

Rick James
Rick James

Reputation: 142298

Is it simply

SELECT  Orders,
        MAX(Modified Date) AS LatestDate
    FROM table
    GROUP BY Orders;

Upvotes: 0

GMB
GMB

Reputation: 222462

In recent versions of MariaDB, you can use ROW_NUMBER() to rank the records by descending modified_date within groups sharing the same orders, and then filter on the top record per group:

SELECT *
FROM (
    SELECT
        t.*,
        ROW_NUMBER() OVER(PARTITION BY orders ORDER BY modified_date DESC) rn
    FROM mytable t
) x
WHERE rn = 1

In earlier versions, you can use a correlated subquery with a NOT EXISTS condition:

SELECT *
FROM mytable t
WHERE NOT EXISTS (
    SELECT 1
    FROM mytable t1
    WHERE t1.orders = t.orders AND t1.modified_date > t.modified_date
)

Or you can use a subquery to return the max date per group and join on it:

SELECT *
FROM mytable t
WHERE modified_date = (
    SELECT MAX(t1.modified_date)
    FROM mytable t1
    WHERE t1.orders = t.orders
)

Upvotes: 1

Related Questions