SQLTeam.com | Weblogs | Forums

How to choose the event with the max id

sql2012
sql2008
sql2008r2

#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 ?