How to find the maximum value of two interdependent columns

Question

I have a table in an ERP system and I'm trying to find some effective way to make a query to get the maximum values from two columns that are dependent on each other.

For example:

order	date	time
487715	2021-06-08	13:35:52
487715	2021-06-12	08:59:28
487715	2021-06-12	10:06:13
572335	2021-08-06	10:23:15
572335	2021-06-01	06:42:40

And I need result:

order  | date       | time
487715 | 2021-06-12 | 10:06:13
572335 | 2021-08-06 | 10:23:15

One option is to select the max value of date and use the inner join to reattach the table and find the maximum time value, but I'm not sure how it is with query efficiency and speed.

select orderID,
       max(pickTime) pickTime,
       maxDate.pickDate
from (
        select orderID,
        max(pickDate) pickDate
        From TestDB.dbo.stockMovements
        group by orderID
    ) maxDate
    inner join TestDB.dbo.stockMovements movements on movements.orderID = maxDate.orderID and movements.pickDate = maxDate.pickDate
    group by maxDate.orderID, maxDate.pickDate

This does not have to be just about the date and time but only the numbers.

Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Aug 23 '21 at 09:50
In this case you can also do simply `MAX(date + time)` however it is unlikely to be as efficient as the other answers — Charlieface, Aug 23 '21 at 09:51

Stefanov.sm · Accepted Answer · 2021-08-23T08:31:06.617

0

Rank the rows for each order and then select only the topmost ranking ones.

select * from
(
 select "order", "date", "time",
   row_number() over (partition by "order" order by "date" desc, "time" desc) r
 from TestDB.dbo.stockMovements
) t
where r = 1;

db<>fiddle

edited Aug 23 '21 at 08:31

answered Aug 23 '21 at 08:08

Stefanov.sm

11,215
2
21
21

score 0 · Answer 2 · answered Aug 23 '21 at 08:24

You can use row_number to assign a priority to each row and then select rows with the highest priority.

with t as (
    select *, Row_Number() over (partition by [order] order by [date] desc, [time] desc) rn
    from Tablename
)
select [order], [date], [time]
from t
where rn=1;

Your example may be contrived, however if these are your actual column names you should reconsider and choose more meaningful column names that are not the same as reserved keywords - doing so is a well-documented anti-pattern and forces the unecessary use of braces []

How to find the maximum value of two interdependent columns

2 Answers2