comparing rows of the same table

Question

comparing rows of the same table

ganeshc27

SSC Eights!

Points: 972
More actions
May 31, 2007 at 3:42 am

#117260

Hi,
i've a table with two columns (userid, logintime).
i want to remove the rows if the time difference between them is less than 10 seconds and keep only one row for that userid.
for example,
userid logintime
1234 2007-05-10 17:18:00
1234 2007-05-10 17:18:10
1234 2007-05-10 17:18:12

here, i want to remove the last two rows and keep only the top one.
can anyone help me on this?
Regards,
Ganesh

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Ganesh

Not sure how well this would perform, but one way of doing it would be to create a temp table with an identity column, say tempID. Create an index on the identity column. Then select all your table data into that table, ordered by userid and logintime. Your query would then look something like this:

SELECT userid, logintime

FROM #temptable t1

JOIN #temptable t2

ON t2.tempid = t1.tempid + 1

AND t1.userid = t2.userid

AND DATEDIFF(ss, t1.logintime, t2.logintime) > 10

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

No problem Ganesh -

--===== Create a test table and populate it with test data
 CREATE TABLE #MyHead (userid INT, logintime DATETIME)
 INSERT INTO #MyHead (userid, logintime)

 SELECT 1234, '2007-05-10 17:18:00' UNION ALL
 SELECT 1234, '2007-05-10 17:18:11' UNION ALL 
 SELECT 1234, '2007-05-10 17:18:13' UNION ALL --
 SELECT 1234, '2007-05-10 17:18:15' UNION ALL --

 SELECT 1234, '2007-05-10 18:18:10' UNION ALL 
 SELECT 1234, '2007-05-10 18:18:12' UNION ALL
 SELECT 1234, '2007-05-10 18:18:14' UNION ALL --
 SELECT 1234, '2007-05-10 18:18:16' UNION ALL --

 SELECT 1234, '2007-05-10 19:18:10' UNION ALL
 SELECT 1234, '2007-05-10 19:18:12' UNION ALL --
 SELECT 1234, '2007-05-10 19:18:14' UNION ALL --
 SELECT 1234, '2007-05-10 19:18:16' UNION ALL --

 SELECT 1235, '2007-05-10 17:18:00' UNION ALL
 SELECT 1235, '2007-05-10 17:18:11' UNION ALL 
 SELECT 1235, '2007-05-10 17:18:13' UNION ALL --
 SELECT 1235, '2007-05-10 17:18:15' UNION ALL --

 SELECT 1235, '2007-05-10 18:18:10' UNION ALL 
 SELECT 1235, '2007-05-10 18:18:12' UNION ALL
 SELECT 1235, '2007-05-10 18:18:14' UNION ALL --
 SELECT 1235, '2007-05-10 18:18:16' UNION ALL --

 SELECT 1235, '2007-05-10 19:18:10' UNION ALL
 SELECT 1235, '2007-05-10 19:18:12' UNION ALL --
 SELECT 1235, '2007-05-10 19:18:14' UNION ALL --
 SELECT 1235, '2007-05-10 19:18:16' UNION ALL --

 SELECT 1236, '2007-05-10 17:18:00' UNION ALL
 SELECT 1236, '2007-05-10 17:18:11' UNION ALL 
 SELECT 1236, '2007-05-10 17:18:13' UNION ALL --
 SELECT 1236, '2007-05-10 17:18:15' UNION ALL --

 SELECT 1236, '2007-05-10 18:18:10' UNION ALL 
 SELECT 1236, '2007-05-10 18:18:12' UNION ALL --
 SELECT 1236, '2007-05-10 18:18:14' UNION ALL --
 SELECT 1236, '2007-05-10 18:18:16' UNION ALL --

 SELECT 1236, '2007-05-10 19:18:02' UNION ALL
 SELECT 1236, '2007-05-10 19:18:12' UNION ALL --
 SELECT 1236, '2007-05-10 19:18:14' UNION ALL --
 SELECT 1236, '2007-05-10 19:18:16'  --

-- Delete unwanted rows
DELETE FROM #MyHead 
FROM (SELECT DISTINCT t1.userid, t1.logintime
 FROM #MyHead t1
 INNER JOIN #MyHead t2
 ON t2.userid = t1.userid AND DATEDIFF (ss, t2.logintime, t1.logintime) between 1 and 9
&nbsp d 
WHERE d.userid = #MyHead.userid AND d.logintime = #MyHead.logintime

Cheers

ChrisM

Thanks again Jeff for the test table

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Heh... thanks Chris. And, nice job, by the way

But... try this... one less join = faster performance

DELETE t1

FROM #MyHead t1

INNER JOIN #MyHead t2

ON t1.UserID = t2.UserID

AND DATEDIFF (ss, t2.LoginTime, t1.LoginTime) BETWEEN 1 AND 9

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Lovely! Nice way to end a friday.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ganeshc27 SSC Eights! Points: 972 More actions · Answer 5

thank you all for ur time and help. gr8!

Regards,

Ganesh