Select Query Help Please? - 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 Query Help Please?

Author

Topic

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-13 : 09:51:05

Hello,

I have a SQL query as follows:


select DrawProgram.EventID,DrawProgram.OutComeTeams,DrawProgram.OutComeGameDesc,DrawProgram.OutComeGame, DrawProgram.OutComeGameCode,DrawProgram.SpecialCodeStatus,LiveOdds.OutComeOdd,DrawProgram.NevId ,SpecialData.First_Half_Home_Score,SpecialData.First_Half_Visitor_Score,SpecialData.Second_Half_Home_Score,SpecialData.Second_Half_Visitor_Score,SpecialData.First_Half_Completed,SpecialData.Second_Half_Completed,SpecialData.LiveMinute,DrawProgram.EventDate
from 
( 
select dp.OutComeTeams, dp.OutComeGameDesc,dp.OutComeGame, dp.OutComeGameCode,dp.SpecialCodeStatus, 
cte.DrawNumber, cte.EventID, cte.RecordID,dp.NevId ,dp.EventDate
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 DrawProgram 
Left join LiveOdds on (DrawProgram.RecordID=LiveOdds.RecordID and DrawProgram.DrawNumber=LiveOdds.DrawNumber and DrawProgram.EventID=LiveOdds.EventID and DrawProgram.OutComeGameCode = LiveOdds.OutComeGameCode) 

join SpecialData on (DrawProgram.NevId = SpecialData.Nev_Id and DrawProgram.EventID=SpecialData.Id)
order by DrawProgram.EventID asc

It brings values that has MAX recordid from LiveOdds table. I would like to get also the values which has the MAX -1 recordid.

Here are the table structures:

USE [AzerLiveBet]
GO

/****** Object: Table [dbo].[DrawProgram] Script Date: 06/13/2012 16:49:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[DrawProgram](
[DrawNumber] [varchar](20) NULL,
[DataRevision] [varchar](20) NULL,
[EventID] [varchar](20) NULL,
[EventDesc] [nvarchar](100) NULL,
[EventDate] [datetime] NULL,
[EventStartDate] [datetime] NULL,
[SpecialEventStatus] [nvarchar](30) NULL,
[EventType] [varchar](20) NULL,
[NevId] [varchar](30) NULL,
[OutComeID] [varchar](20) NULL,
[OutComeTeams] [nvarchar](100) NULL,
[OutComeGameDesc] [nvarchar](100) NULL,
[OutComeGame] [nvarchar](100) NULL,
[OutComeGameCode] [varchar](50) NULL,
[OutComeDate] [datetime] NULL,
[OutComeStartDate] [datetime] NULL,
[SE] [varchar](10) NULL,
[SpecialGameStatus] [nvarchar](30) NULL,
[SpecialCodeStatus] [nvarchar](30) NULL,
[RecordID] [int] NULL
) ON [PRIMARY]

GO

USE [AzerLiveBet]
GO

/****** Object: Table [dbo].[LiveOdds] Script Date: 06/13/2012 16:49:58 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[LiveOdds](
[DrawNumber] [varchar](20) NULL,
[OddsRevision] [varchar](20) NULL,
[EventID] [varchar](20) NULL,
[EventDesc] [nvarchar](100) NULL,
[EventStatus] [nvarchar](30) NULL,
[OutComeID] [varchar](20) NULL,
[OutComeDesc] [nvarchar](100) NULL,
[OutComeGame] [nvarchar](100) NULL,
[OutComeGameCode] [varchar](50) NULL,
[OutComeOdd] [varchar](10) NULL,
[RecordID] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

USE [AzerLiveBet]
GO

/****** Object: Table [dbo].[SpecialData] Script Date: 06/13/2012 16:50:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[SpecialData](
[Nev_Id] [varchar](30) NULL,
[Sev_Nbr] [varchar](30) NULL,
[Id] [varchar](30) NULL,
[First_Half_Home_Score] [varchar](10) NULL,
[First_Half_Visitor_Score] [varchar](10) NULL,
[Second_Half_Home_Score] [varchar](10) NULL,
[Second_Half_Visitor_Score] [varchar](10) NULL,
[First_Half_Completed] [varchar](10) NULL,
[Second_Half_Completed] [varchar](10) NULL,
[LiveMinute] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SET ANSI_PADDING OFF
GO

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-13 : 09:56:03

DrawProgram Sample Data:

519 126 14 BARUERI-PARANA 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 Active 20 118858 0 BARUERI-PARANA Oyun N?tic?si 1 101 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 1 Active Active 1845
519 126 14 BARUERI-PARANA 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 Active 20 118858 1 BARUERI-PARANA Oyun N?tic?si X 102 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 1 Active Active 1845
519 126 14 BARUERI-PARANA 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 Active 20 118858 2 BARUERI-PARANA Oyun N?tic?si 2 103 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 1 Active Active 1845
519 126 14 BARUERI-PARANA 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 Active 20 118858 3 BARUERI-PARANA Oyun N?tic?si 1X 104 2012-06-13 20:30:00.000 2012-06-13 13:00:00.000 1 Active Active 1845

Live ODDS:
519 NULL 14 BARUERI-PARANA Active 0 1 - 0101 1 0101 1.50 657
519 NULL 14 BARUERI-PARANA Active 1 X - 0102 X 0102 6.00 657
519 NULL 14 BARUERI-PARANA Active 2 2 - 0103 2 0103 4.50 657
519 NULL 14 BARUERI-PARANA Active 3 1X - 0104 1X 0104 2.00 657

Special Data:
118858 4 14 7 0 7 0 1 1 45

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 09:56:45

select DrawProgram.EventID,DrawProgram.OutComeTeams,DrawProgram.OutComeGameDesc,DrawProgram.OutComeGame, DrawProgram.OutComeGameCode,DrawProgram.SpecialCodeStatus,LiveOdds.OutComeOdd,DrawProgram.NevId ,SpecialData.First_Half_Home_Score,SpecialData.First_Half_Visitor_Score,SpecialData.Second_Half_Home_Score,SpecialData.Second_Half_Visitor_Score,SpecialData.First_Half_Completed,SpecialData.Second_Half_Completed,SpecialData.LiveMinute,DrawProgram.EventDate
from
(
select dp.OutComeTeams, dp.OutComeGameDesc,dp.OutComeGame, dp.OutComeGameCode,dp.SpecialCodeStatus,
cte.DrawNumber, cte.EventID, cte.RecordID,dp.NevId ,dp.EventDate
from DrawProgram as dp
left join ( select DrawNumber, EventID, RecordID
from
(
SELECT DrawNumber, EventID, RecordID as RecordID, seq = row_number() over (partition by DrawNumber, EventID order by RecordID desc)
FROM LiveOdds
) a
where seq = 2
) as cte
on (dp.DrawNumber=cte.DrawNumber and dp.EventID=cte.EventID)
) as DrawProgram
Left join LiveOdds on (DrawProgram.RecordID=LiveOdds.RecordID and DrawProgram.DrawNumber=LiveOdds.DrawNumber and DrawProgram.EventID=LiveOdds.EventID and DrawProgram.OutComeGameCode = LiveOdds.OutComeGameCode)

join SpecialData on (DrawProgram.NevId = SpecialData.Nev_Id and DrawProgram.EventID=SpecialData.Id)
order by DrawProgram.EventID asc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-06-13 : 10:10:44

using cte's which you seem to be heading towards

;with cte1 as
(
SELECT DrawNumber, EventID, RecordID as RecordID, seq = row_number() over (partition by DrawNumber, EventID order by RecordID desc)
FROM LiveOdds
),
cte as
(
select DrawNumber, EventID, RecordID
from cte1
where seq = 2
)
select DrawProgram.EventID,DrawProgram.OutComeTeams,DrawProgram.OutComeGameDesc,DrawProgram.OutComeGame, DrawProgram.OutComeGameCode,DrawProgram.SpecialCodeStatus,LiveOdds.OutComeOdd,DrawProgram.NevId ,SpecialData.First_Half_Home_Score,SpecialData.First_Half_Visitor_Score,SpecialData.Second_Half_Home_Score,SpecialData.Second_Half_Visitor_Score,SpecialData.First_Half_Completed,SpecialData.Second_Half_Completed,SpecialData.LiveMinute,DrawProgram.EventDate
from
(
select dp.OutComeTeams, dp.OutComeGameDesc,dp.OutComeGame, dp.OutComeGameCode,dp.SpecialCodeStatus,
cte.DrawNumber, cte.EventID, cte.RecordID,dp.NevId ,dp.EventDate
from DrawProgram as dp
left join cte
on (dp.DrawNumber=cte.DrawNumber and dp.EventID=cte.EventID)
) as DrawProgram
Left join LiveOdds on (DrawProgram.RecordID=LiveOdds.RecordID and DrawProgram.DrawNumber=LiveOdds.DrawNumber and DrawProgram.EventID=LiveOdds.EventID and DrawProgram.OutComeGameCode = LiveOdds.OutComeGameCode)

join SpecialData on (DrawProgram.NevId = SpecialData.Nev_Id and DrawProgram.EventID=SpecialData.Id)
order by DrawProgram.EventID asc

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

raysefo
Constraint Violating Yak Guru

260 Posts

Posted - 2012-06-13 : 13:14:17

Hi,

Thanks for your reply but it does NOT bring the ones MAX(RecordID) -1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources