SQL query: get values with latest timestamp from join?

select  Name,Entitlements, max(LastWriteTime)
from table1
join table1 ON table2.Name = table1.Name
group by  Name,Entitlements

Select allOpen in new window

select  Name,Entitlements, LastWriteTime
from (
    select *, row_number() over(partition by name order by lastwritetime desc) as row_num
    from table1
) as t1_last
where row_num = 1

I love the window functions but thought it was overkill in this case.

Sorry, none of the solutions work for me.

First solution I get errors, second solution I only get 1 row.

Please post sample data and the expected resuls from that sample data.

Application    Version Version  DateCreated
----------     ------  -------- -------------
7-Zip           60.0   x   09/25/2019
Z-Zip           60.0   y  12/26/2019

It should only show
7-Zip 60

Those are different columns names from the original post.

The MAX will take the maximum value from all 'distinct' non aggregate columns.

I see an 'x' and a 'y', so the max will return each row if you are grouping by it.

If you want the max based on only two columns, only group by two columns.

I m giving just an example. Not the real data.
I d need application, dateecreated and other values but the filter should happen: if multiple items with same application name then take only the one with the latest Dateecreated .

The rest of your ewplanation I dont get, can you give an example query based on my input?

This is basically what Scott posted:

select Application, Version, something from (
    select Application, Version, something, row_number() over(order by DateCreated desc) rn
    from junk
) x
where rn=1

Select allOpen in new window

Here is the test case:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=257c10a950c071f2d637b3763b33644f


If this isn't right, please add to the test case and post the fiddle or provide more sample data and expected results.

LIke I said, I only get 1 result, whereas I should get 1 result per item. F.e. 7-Zip, Xmind, ... all other applications.
I don't get an error, just one result but there should be multiple.

And if I remove the where rn = 1 I get the result just as before: multiple items (applications) instead of 1 per latest timestamp.

>>just one result but there should be multiple.

Not based on your example above:

Application    Version Version  DateCreated
----------     ------  -------- -------------
7-Zip           60.0   x   09/25/2019
Z-Zip           60.0   y  12/26/2019

It should only show
7-Zip 60

I guess that was a typo?  You have 7-Zip and Z-Zip.  That is two applications and the expected results is only one row.

You aren't giving us what we ask for.
1:  Give us raw sample data.
2:  Post the EXACT results you want from the sample data.

Until we get that, we can only guess and what you are asking for.

Another guess:

select Application, Version from (
   select Application, Version, row_number() over(Partition by Application order by DateCreated desc) rn
   from junk
) x
where rn=1

Select allOpen in new window

Sample fiddle here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=1f0a9c4c35f777078165c02c5691f358 

Ok, sorry for not being complete

Application    Version Version  DateCreated

7-Zip           60.0   x   09/25/2019
Z-Zip           60.0   y  12/26/2019
Xmind        23       x   05/23/2019
Xmind       23      y     01/01/2020

It should only show:
7-Zip 60 12/26/2019
Xmind 01/01/2020


But currently it only shows 7-Zip 60

There are multiple other applications like 7-Zip,Xmind, Visio,TeamViewer etc which all have different timestamps. I would need only the ones with latest timestamps.

-- Test Data:
DECLARE @t TABLE (App VarChar(100),Ver1 VarChar(100),Ver2 VarChar(100),dt Date)
INSERT INTO @t VALUES
('7-Zip','60.0','x','09/25/2019'),
('7-Zip','60.0','y','12/26/2019'),
('Xmind','23','x','05/23/2019'),
('Xmind','23','y','01/01/2020')
SELECT * FROM @t

-- Query:
SELECT DISTINCT t1.App,t1.Ver1,t1.dt FROM @t t1
WHERE t1.dt = (SELECT Max(t2.dt) FROM @t t2 WHERE t2.App=t1.App)

-- Result:
App             Ver1              dt
7-Zip      60.0      2019-12-26
Xmind      23              2020-01-01

Thanks Andrei, that looks like my solution. Will check asap, but can you clarify what you are doing? Dont fully get that.

@slightwv I dont know how a where rn = 1 can have the expected result, it just gives rownumber 1, which is always just row 1(?)

Are you looking at the fiddles I'm posting to see tha actual results?

>>Thanks Andrei, that looks like my solution.

His and mine return the exact same results.  You can see it here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=9b4316728e1f94f067e2855a21457a10 

>>it just gives rownumber 1

It gives "rn" which is the alias from the ROW_NUMBER in the inside query.  The ROW_NUMBER call is groupbing by application and version.  That is what the PARTITION BY does.

Ok, tried Anreis solution but  cant' make that work either

SELECT DISTINCT Application,Date FROM table1
WHERE application = (SELECT Max(Date) FROM table1 WHERE application=Date)
No error, just no output

Not sure what I'm doing wrong here :-(
Your solution, slighwv gives only 1 row for me

Thanks! Typo ... everything fine now :-)