Select Question? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select Question?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-05 : 14:31:27

Hello,

I am using this query below to get the highest RecordID from LiveOdds table and join it with DrawNumber table:


select dp.Outcometeams, dp.OutComeGameDesc, dp.OutComeGameCode,
        cte.DrawNumber, cte.EventID, cte.RecordID
    
from DrawProgram as dp left join (SELECT DrawNumber, EventID, MAX(RecordID) as RecordID  FROM LiveOdds group by DrawNumber, EventID) as cte
  
     on (dp.DrawNumber=cte.DrawNumber and dp.EventID=cte.EventID)
 
 
 
) as summary

The requirement is how can I get the max RecordID and max -1 RecordID? Lets say if the max recordID is 6 then I need to get both RecordID 6 and 5. Is there a way to do it?

Best Regards.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-06-05 : 14:43:11

Run just this
SELECT DrawNumber, EventID,RecordID,rank() over(partition by DrawNumber, EventID order by recordid desc as row
and you will see that max recordid is row = 1, then next biggest 2 etc.

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-05 : 14:45:46

[code]
select dp.Outcometeams, dp.OutComeGameDesc, dp.OutComeGameCode,
cte.DrawNumber, cte.EventID, cte.RecordID

from DrawProgram as dp
left join (SELECT *, ROW_NUMBER() OVER (PARTITION BY EventID,DrawNumber ORDER BY recordID DESC)AS Seq
FROM LiveOdds) as cte

on dp.DrawNumber=cte.DrawNumber and dp.EventID=cte.EventID AND Seq IN (1,2)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-06 : 03:04:51

Hi visakh16,

Thanks for your reply. The problem is recordid is NOT unique. There are more than 1 same recordid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 22:58:09

quote:
Originally posted by raysefo

Hi visakh16,

Thanks for your reply. The problem is recordid is NOT unique. There are more than 1 same recordid.

so you want all of them with same max recordid?

show some data and explain what you want is output

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources