I would like to do a group by whereby I am going to join a table that is in the many side to a table on the one side so I will need to do a group by to get the right result. the problem I have is when I do a group by using one column then I am still get duplicates in the many side table so I have to do another group by using another field but not sure if I am getting the right result can someone see if I am doing this the right way

select max(id),
sale_id,
max(date)
from sales
group by sale_id

So what I want to happen is for the max date to calculate first and then the max id to be calculated I don't want them to be calculated at the same time as that may cause the data to not appear as expected

so ill give an example

i have the three columns

id       date                 sale_id
1        01-01-2022            5675
2        02-01-2022            5675
3        05-01-2022            5675
4        02-01-2022            5676
5        03-01-2022            5676
6        03-01-2022            5676

so as we can see the sale_id 5676 has two sales on the 03-01-2022. so i want to group by date first to return the following

id         date           sale_id
3          05-01-2022       5675
5          03-01-2022       5676
6          03-01-2022       5676

once i get back this result set I then want to group by id to get back my desired result set

id         date             sale_id
3          05-01-2022         5675
6          03-01-2022         5676

CodePudding user response：

You could always partition your rows:

SELECT id, [date], sale_id
FROM
(
     SELECT MAX(Id) AS Id, [date], sale_id, ROW_NUMBER() OVER(PARTITION BY sale_id ORDER BY [date] DESC) AS RowNum
     FROM Sales
     GROUP BY [Date], sale_id
)x
WHERE RowNum = 1

CodePudding user response：

You can use the aggregate max function with the last function to get both values with one aggregation (group-by):

select max(id) keep (dense_rank last order by sale_date, id) as id,
  max(sale_date) as sale_date,
  sale_id
from your_table
group by sale_id;

('date' is a reserved word so I've assumed the column is actually called something more meaningful like 'sale_date'.)

db<>fiddle

You can use the same technique after you join two tables together.