3

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

5 Answers 5

2

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
1
  • With this format Select MAX(ID) As "Maxid" FROM #One GROUP BY bbid would be more efficient
    – paparazzo
    Jun 16, 2016 at 13:42
2

Keeping it simple

select * from mytable where id = (select max(id) from mytable)
1
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
0

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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.