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

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