Help required on a self join - 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 2005 Forums

Transact-SQL (2005)

Help required on a self join

Author

Topic

girishatdell
Starting Member

2 Posts

Posted - 2007-05-30 : 12:50:55

I have a table (sample below) with a PK CALLID and other key fields. I am on a task to get the records where CLIP is repeated. I achieved it by self joining the table on CLIP. However, I need only the record that has occured immediately after the first occurance (using CDATESTAMP).

DDL:

CREATE TABLE [#CLIP_TEST] (
[CALLID] bigint NOT NULL,
[CLIP] varchar(255),
[CDATESTAMP] datetime NOT NULL
)

SAMPLE DATA:

INSERT INTO [#CLIP_TEST]
SELECT 1,6468259888,'5/6/07 12:45' UNION ALL
SELECT 2,3026531379,'5/6/07 19:26' UNION ALL
SELECT 3,8172208244,'5/7/07 15:19' UNION ALL
SELECT 4,3026531379,'5/7/07 15:22' UNION ALL
SELECT 5,8172208244,'5/7/07 15:57' UNION ALL
SELECT 6,8172208244,'5/7/07 23:16' UNION ALL
SELECT 7,5124983399,'5/9/07 21:34' UNION ALL
SELECT 8,3026531379,'5/9/07 23:58' UNION ALL
SELECT 9,5124983399,'5/11/07 6:40' UNION ALL
SELECT 10,3345670253,'5/11/07 13:01'

The result i am looking for from the above dataset is as below

CALLID REPEAT_CALLID
2 4
3 5
4 8
5 6
7 9

ALthough CLIP repeats itself several times, I need the record where it repeated first.
Please note that CDATESTAMP may not be ordered

Awaiting the some help from the experts here...

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-30 : 12:55:59

in your desired result set, why is the 4,8 row present? and shouldn't the 7,9 row be 7,8?

EDIT: also, I'd take a stab at this one, but only if you provide DDL for the table, and sample data in the form of INSERT statements, so that I can just copy/paste in to SSMS and run it.

I am far too lazy to type all that out. that's work that you, the question asker, should do.

www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-30 : 14:02:38

this does it with a correlated subquery. NULL is returned in the second column if the clip is never repeated.


select 
    ct1.callid
    ,nextCallID=(select top 1 callid 
                 from clip_test 
                 where clip=ct1.clip and cdatestamp > ct1.cdatestamp 
                 order by cdatestamp asc)
from clip_test ct1

www.elsasoft.org

girishatdell
Starting Member

2 Posts

Posted - 2007-05-31 : 04:20:38

Thanks so much...u made my day...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources