How to get the MAX value of unique column in sql and aggregate other?-CodePudding

I want get the row with max 'date', groupy just by unique 'id' but without considering another columns.

I tried this query: (But don't work cause modify anothers columns)

SELECT id,
       MAX(num),
       MAX(date),-- I just want the max of this column
       MAX(product_name),
       MAX(other_columns)
FROM TB
GROUP BY id

Table:

id    num      date      product_name   other_columns
123   0001   2021-12-01      exit          12315413
123   0002   2021-12-02      entry         65481328
333   0001   2021-12-03      entry         13848136
333   ASDV   2021-12-04      exit           1325165

Expected Result:

id    num      date      product_name
123   0002   2021-12-02      entry
333   ASDV   2021-12-04      exit

How to do that?

CodePudding user response：

Sub-query with an inner join can take care of this pretty DBMS agnostically.

SELECT
      t.ID
      ,t.date
      ,t.product_name
      ,t.other_columns
    FROM tb as t
    INNER JOIN (
       SELECT
          id
          ,MAX(date) as date
       FROM tb
       GROUP BY id 
    ) as s on t.id = s.id and t.date = s.date