Query To Return Data From MAX(ID)

Question

I need to return only the 1 row of the MAX(ID), but my query is not returning such.

This is the result set that I get returned

id bbid indate st val
200817 16543 01/01/16 1 65.4
2017 16543 01/01/16 1 36.52
1995 16543 01/01/16 1 18.51
20932 1234 03/01/16 1 55.3
22324 1234 03/01/16 1 16.52
55 8232 05/01/16 2 100

This is what I WANT returned

id bbid indate st val
200817 16543 01/01/16 1 65.4
22324 1234 03/01/16 1 16.52
55 8232 05/01/16 2 100

(Only one row per bbid, and if bbid has multiple entries then pull the data from MAX(ID))

Here is sample DDL to help troubleshoot

Create Table #One
(
 id int
 ,bbid int
 ,val varchar(100)
)
Create Table #Two
(
 bbid int
 ,st int
 ,indate datetime
)
Insert Into #One Values
('200817','16543','65.40')
,('2017','16543','36.52')
,('1995','16543','18.51')
,('20932', '1234', '55.30')
,('22324', '1234', '16.52')
,('55', '8232', '100')
Insert Into #Two Values
('16543', '1', '2016-01-01')
,('1234', '1', '2016-03-01')
,('8232','2', '2016-05-01')

And this is the query I wrote that produced the incorrect results...

Select 
t1.id
,t3.bbid
,t3.indate
,t3.st
,t1.val
FROM #One t1
JOIN 
(
 Select
 id
 ,MAX(ID) As "Maxid"
 FROM #One
 GROUP BY ID
 ) t2
ON t1.id=t2.Maxid
INNER JOIN #Two t3
ON t1.bbid = t3.bbid

How should this query be altered to return my desired result set'?

Answer 1

You can use a common table expression (cte).

;with cte as (
select
 row_number() over(partition by bbid order by #one.id desc) as rn ,id from #one
)
select 
 t1.id
 ,t3.bbid
 ,t3.indate
 ,t3.st
 ,t1.val
from
 #One as t1
join #Two as t3
 on t1.bbid = t3.bbid
join cte
 on cte.id = t1.id
 and cte.rn = 1
order by t1.id desc

• paparazzo: With this format Select MAX(ID) As "Maxid" FROM #One GROUP BY bbid would be more efficient

Answer 2

Keeping it simple

select * from mytable where id = (select max(id) from mytable)

Answer 3

select * 
from 
( select t1.id
 , t3.bbid
 , t3.indate
 , t3.st
 , t1.val
 , row_number() over (partition by bbid order by id desc) as rn 
 FROM #One t1
 INNER JOIN #Two t3
 ON t1.bbid = t3.bbid
) tt
where tt.rn = 1

or just put this in your query as t2

Select MAX(ID) As "Maxid" 
FROM #One 
GROUP BY bbid

Answer 4

Here you go!

select t1.id, t1.mmbid, t3.indate, t3.st, t1.val from #one t1
(select bbid, MAX(id) mbbid from #one ) t2
on t1.id=t2.id and t1.bbid=t2.mbbid
join #two t3 
on t1.mbbid=t3.bbid

Answer 5

This one uses a CTE, but does not rely on row_number (just in case you find the syntax overly complicated)

with MaxIDs as
(
 select max(id) as 'MaxID',
 bbid
 from #One
 group by bbid
)
select o.id, o.bbid, t.indate, t.st, o.val 
 from MaxIDs m
 join #Two t on m.bbid = t.bbid
 join #One o on o.id = m.maxid 
order by 1 desc