How to choose the event with the max id

, ,
1

create table #orcohen(event int, id int)

insert into #orcohen
select 1,1 union
select 1,2 union
select 1,3 union
select 2,1 union
select 2,2 union
select 2,3 union
select 2,4

drop table #orcohen

Now i want to choose only the 4 records of event 2 becasue he has the biggest id number - 4 .

How can i do that ?

2

what have you tried?

3

i can choose only the record with max(id) = 4 but it just giving me the last record which is not good , im pretty stuck here .

4

post your code here please

5

select event,max(id) as ix from orcohen group by 1 having max(id) =1;

6

create table #orcohen(event int, id int)

insert into #orcohen
select 1,1 union
select 1,2 union
select 1,3 union
select 2,1 union
select 2,2 union
select 2,3 union
select 2,4

--1.
select * from #orcohen a where event in (select top 1 event from #orcohen mx order by mx.id desc)

drop table #orcohen

7 
select o.*
from #orcohen o
inner join (
    select top (1) event
    from #orcohen
    group by event
    order by max(id) DESC, event
) AS o_max ON o.event = o_max.event
8

SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM ##TestTable

9

Thanks !!

how can i choose only the records that their max id is <= than 4 ?