How can I select few columns with MAX(column) from several grouped columns?-CodePudding

My table has a lot of columns, I didn't list them all at here. But the selected columns are like below:

product_line	test_port	rate1	rate2	test_stage	finished_time	update_dt
phone	A	50	50	final	2022-04-20 12:00:00	2022-04-20 10:50:30
phone	A	10	10	final	2022-04-20 12:00:00	2022-04-20 11:51:20
phone	A	80	90	final	2022-04-25 12:00:00	2022-04-25 11:20:11
computer	A	70	75	init	2022-04-25 12:00:00	2022-04-25 11:30:45
computer	B	90	80	init	2022-04-25 12:00:00	2022-04-25 11:56:22

When I select these columns, I need group by "product_line, test_port, test_stage, finished_time", sadly also rate1 rate2.

But all I need unique together is the combination of "product_line, test_port, test_stage, finished_time".

I have to find the latest(max) row of update_dt that is grouped by "product_line, test_port, test_stage, finished_time". The result I need is like:

product_line	test_port	rate1	rate2	test_stage	finished_time	max(update_dt)
phone	A	10	10	final	2022-04-20 12:00:00	2022-04-20 11:51:20
phone	A	80	90	final	2022-04-25 12:00:00	2022-04-25 11:20:11
computer	A	70	75	init	2022-04-25 12:00:00	2022-04-25 11:30:45
computer	B	90	80	init	2022-04-25 12:00:00	2022-04-25 11:56:22

I'm stucking at the part that SQL asking me add rate1, rate2 to the group by.

CodePudding user response：

You can use ROW_NUMBER() to identify the rows you want. Then, filtering is easy. For example:

select *
from (
  select
    t.*,
    row_number() over(
      partition by product_line, test_port, test_stage, finished_time
      order by updated_dt desc
    ) as rn
  from t
) x
where rn = 1

CodePudding user response：

Simply

SELECT product_line, test_port, test_stage, finished_time, max(update_dt)
FROM your_table
GROUP BY product_line, test_port, test_stage, finished_time

If you wanna add the rate columns

with cte as
(
    SELECT product_line, test_port, test_stage, finished_time, max(update_dt) as max_update_dr
    FROM your_table
    GROUP BY product_line, test_port, test_stage, finished_time
)
select cte.product_line, a.rate1, a.rate2, cte.test_port, cte.test_stage, cte.finished_time, cte.max_update_dr
from cte left join your_table a
on cte.product_line = a.product_line, cte.test_port = a.test_port, cte.test_stage = a-test_stage, cte.finished_time = a.finished_time