CODEWITHSUNDEEP
sqlsql-server
kulapo
kulapo

Reputation: 397

Select based on max date from another table

I'm trying to do a simple Select query by getting the country based on the MAX Last update from the other table.

Order#         
1
2
3
4

The other table contains the country and the last update:

Order#    Cntry           Last Update
1                    12/21/2019  9:19 PM
1          US        1/10/2020  1:07 AM
2          JP        7/29/2020  12:15 PM
3          CA        4/12/1992  2:04 PM
3          GB        11/6/2001  9:26 AM
3          DK        2/1/2005  3:04 AM
4          CN        8/20/2013  12:04 AM
4                    10/1/2015  4:04 PM

My desired result:

Order#     Country
1            US
2            JP
3            DK
4

Not sure the right solution for this. So far i'm stuck with this:

SELECT Main.[Order#], tempTable.Cntry
FROM Main 
LEFT JOIN (

SELECT [Order#], Cntry, Max([Last Update]) as LatestDate FROM Country
GROUP BY [Order#], Cntry

) as tempTable ON Main.[Order#] = tempTable.[Order#];

Thanks in advance!

Upvotes: 0

Views: 1024

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

In SQL Server, you can use a correlated subquery:

update main
    set country = (select top (1) s.country
                   from secondtable s
                   where s.order# = main.order#
                   order by s.lastupdate desc
                  );

EDIT:

A select would look quite simimilar:

select m.*,
       (select top (1) country
        from secondtable s
        where s.order# = main.order#
        order by s.lastupdate desc
       )
from main m

Upvotes: 1

Olga Romantsova
Olga Romantsova

Reputation: 1081

If needs only number of order and country,maybe don't need two tables:

SELECT distinct order, country
FROM
(
SELECT  order, LAST_VALUE (country) OVER (PARTITION by [order] order by last_update) country FROM Country
) X

Upvotes: 2

Nissus
Nissus

Reputation: 316

I don't have time to try it with sample data, but is that what you are looking for?

select order orde, cntry
from table
where last_update = 
(select max(last_update) from table where order = orde)

Upvotes: 0

Related Questions