Join with group by?

Question

Join with group by?

khalprin

Mr or Mrs. 500

Points: 576
More actions
July 14, 2009 at 2:59 pm

#139813

I've got two tables:
CREATE TABLE [dbo].[tblArchiveRecords](
[SequenceID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,
[ArchiveID] [int] NOT NULL
)
CREATE TABLE [dbo].[tblPublishPosition](
[SequenceID] [int] IDENTITY(1,1) NOT NULL,
[ArchiveID] [int] NOT NULL,
[PublishedPosition] [bigint] NOT NULL
)
New records are added to tblArchiveRecords frequently. A processing function needs to compare records that it has already processed against new records that have arrived. When the processing function is done, it updates the tblPublishPosition table (for the specific ArchiveID) with the bigint value from tblArchiveRecords.SequenceID of the last record it processed.
I'm trying to build a query that joins the tables and returns the ArchiveID, PublishedPosition and the max tblArchiveRecords.SequenceID for the ArchiveID
This information will allow the processing function to determine the start and end SequenceID values of new records to process for each 'archive'
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

in addition to your DDL statements please provide sample data together with expected results and what you've tried so far.

For details on how to post sample data please see the link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

khalprin Mr or Mrs. 500 Points: 576 More actions · Answer 2

Table and data:

CREATE TABLE [dbo].[tblArchiveRecords](

[SequenceID] [bigint] IDENTITY(-9223372036854775808,1) NOT NULL,

[ArchiveID] [int] NOT NULL

)

CREATE TABLE [dbo].[tblPublishPosition](

[SequenceID] [int] IDENTITY(1,1) NOT NULL,

[ArchiveID] [int] NOT NULL,

[PublishedPosition] [bigint] NOT NULL

)

insert into [dbo].[tblArchiveRecords] (ArchiveID) values (1)

insert into [dbo].[tblArchiveRecords] (ArchiveID) values (2)

insert into [dbo].[tblArchiveRecords] (ArchiveID) values (3)

insert into [dbo].[tblPublishPosition] (ArchiveID, PublishedPosition) values (1,-9223372036854775808)

insert into [dbo].[tblPublishPosition] (ArchiveID, PublishedPosition) values (2,-9223372036854775808)

insert into [dbo].[tblPublishPosition] (ArchiveID, PublishedPosition) values (3,-9223372036854775808)

desired results:

ArchiveID PublishedPositionSequenceID

1-9223372036854775808-9223372036854775806

2-9223372036854775808-9223372036854775803

3-9223372036854775808-9223372036854775800

The queries I've tried so far always return joined rows that include all occurrences of ArchiveID in tblArchiveRecords. I'm trying to get only one record per ArchiveID

Thanks

LutzM SSC Guru Points: 107049 More actions · Answer 3

Would something along those lines help?

SELECT ar.ArchiveID,

PublishedPosition,

MAX(ar.SequenceID) AS SequenceID

FROM dbo.tblArchiveRecords ar

INNER JOIN dbo.tblPublishPosition pp

ON ar.ArchiveID=pp.ArchiveId

GROUP BY ar.ArchiveID,pp.PublishedPosition

/*

ArchiveIDPublishedPositionSequenceID

1-9223372036854775808-9223372036854775806

2-9223372036854775808-9223372036854775803

3-9223372036854775808-9223372036854775800

*/

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

khalprin Mr or Mrs. 500 Points: 576 More actions · Answer 4

That does it!

I was trying something similar but couldn't quite get it. I think I was using a subquery after the inner join...

Thanks.

LutzM SSC Guru Points: 107049 More actions · Answer 5

You're welcome. Glad I could help. 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]