Tricky query - 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)

Tricky query

Author

Topic

xalpha
Starting Member

13 Posts

Posted - 2012-06-01 : 06:00:18

Hello,

I have a very special query and hope someone has a good hint for me. I have the following fixes input data:

ID Time Flag
1 14:00:00 1
2 14:00:05 0
3 14:00:08 0
4 14:00:10 1
5 14:00:15 0
6 14:00:18 1
7 14:00:20 1
8 14:00:22 1
9 14:00:25 0

The Output should be alternating like this:

ID Time Flag
1 14:00:00 1
3 14:00:08 0
4 14:00:10 1
5 14:00:15 0
8 14:00:22 1
9 14:00:25 0

Has someone a good idea? I can't believe that it isn't possible without a stored procedure?

Thank you for any idea.

SQL Padawan
Starting Member

4 Posts

Posted - 2012-06-01 : 11:23:52

I'm sure this can be done with a recursive CTE but here is a set-based solution:


DECLARE @sampledata TABLE
(id INT,
time TIME,
flag BIT)

INSERT @sampledata
SELECT 1,'14:00:00',1
UNION ALL
SELECT 2,'14:00:05',0
UNION ALL
SELECT 3,'14:00:08',0
UNION ALL
SELECT 4,'14:00:10',1
UNION ALL
SELECT 5,'14:00:15',0
UNION ALL
SELECT 6,'14:00:18',1
UNION ALL
SELECT 7,'14:00:20',1
UNION ALL
SELECT 8,'14:00:22',1
UNION ALL
SELECT 9,'14:00:25',0

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
	LEFT JOIN @sampledata s2 ON s1.id < s2.id AND s1.flag = s2.flag
		AND	NOT EXISTS(
					SELECT 1 
					FROM @sampledata s3
					WHERE 
						s3.flag != s1.flag AND s3.id BETWEEN s1.id AND s2.id
					)
WHERE s2.id IS NULL
ORDER BY s1.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:49:45

[code]
SELECT t.ID,
t.[Time],
t.Flag
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY PrevTime ORDER BY [Time] DESC) AS Rn,t.*
FROM Table t
OUTER APPLY (SELECT MAX([Time]) AS PrevTime
FROM table
WHERE ID < t.ID
AND Flag <> t.Flag
)t1
)r
WHERE Rn=1
[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 11:52:48

quote:

Originally posted by SQL Padawan

I'm sure this can be done with a recursive CTE but here is a set-based solution:


DECLARE @sampledata TABLE
(id INT,
time TIME,
flag BIT)

INSERT @sampledata
SELECT 1,'14:00:00',1
UNION ALL
SELECT 2,'14:00:05',0
UNION ALL
SELECT 3,'14:00:08',0
UNION ALL
SELECT 4,'14:00:10',1
UNION ALL
SELECT 5,'14:00:15',0
UNION ALL
SELECT 6,'14:00:18',1
UNION ALL
SELECT 7,'14:00:20',1
UNION ALL
SELECT 8,'14:00:22',1
UNION ALL
SELECT 9,'14:00:25',0

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
	LEFT JOIN @sampledata s2 ON s1.id < s2.id AND s1.flag = s2.flag
		AND	NOT EXISTS(
					SELECT 1 
					FROM @sampledata s3
					WHERE 
						s3.flag != s1.flag AND s3.id BETWEEN s1.id AND s2.id
					)
WHERE s2.id IS NULL
ORDER BY s1.id

It involves a hidden RBAR which can hurt performance

see this nice article by Jeff

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-01 : 12:17:00

I was goofing around, and realized I did a 2012 solution. Although, it doesn't help the OP; I'm posting it just incase it helps anyone else (assuming it actually works correctly):

SELECT
	*
FROM
	(
		SELECT 
			*,
			LEAD(flag, 1, NULL) OVER (ORDER BY id) AS NextFlag
		FROM
			@sampledata
	) AS A
WHERE
	flag <> NextFlag
	OR NextFlag IS NULL

SQL Padawan
Starting Member

4 Posts

Posted - 2012-06-01 : 12:23:46

quote:
Originally posted by visakh16

quote:
Originally posted by SQL Padawan

I'm sure this can be done with a recursive CTE but here is a set-based solution:

DECLARE @sampledata TABLE
(id INT,
time TIME,
flag BIT)

INSERT @sampledata
SELECT 1,'14:00:00',1
UNION ALL
SELECT 2,'14:00:05',0
UNION ALL
SELECT 3,'14:00:08',0
UNION ALL
SELECT 4,'14:00:10',1
UNION ALL
SELECT 5,'14:00:15',0
UNION ALL
SELECT 6,'14:00:18',1
UNION ALL
SELECT 7,'14:00:20',1
UNION ALL
SELECT 8,'14:00:22',1
UNION ALL
SELECT 9,'14:00:25',0

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
LEFT JOIN @sampledata s2 ON s1.id < s2.id AND s1.flag = s2.flag
AND NOT EXISTS(
SELECT 1
FROM @sampledata s3
WHERE
s3.flag != s1.flag AND s3.id BETWEEN s1.id AND s2.id
)
WHERE s2.id IS NULL
ORDER BY s1.id

It involves a hidden RBAR which can hurt performance

see this nice article by Jeff

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

He could use this one then

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
LEFT JOIN @sampledata s2 ON s1.id = s2.id - 1 AND s1.flag = s2.flag
WHERE s2.id IS NULL

This builds on Lamprey's post. (thanks!

)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:28:17

quote:
Originally posted by SQL Padawan

quote:
Originally posted by visakh16

quote:
Originally posted by SQL Padawan

I'm sure this can be done with a recursive CTE but here is a set-based solution:

DECLARE @sampledata TABLE
(id INT,
time TIME,
flag BIT)

INSERT @sampledata
SELECT 1,'14:00:00',1
UNION ALL
SELECT 2,'14:00:05',0
UNION ALL
SELECT 3,'14:00:08',0
UNION ALL
SELECT 4,'14:00:10',1
UNION ALL
SELECT 5,'14:00:15',0
UNION ALL
SELECT 6,'14:00:18',1
UNION ALL
SELECT 7,'14:00:20',1
UNION ALL
SELECT 8,'14:00:22',1
UNION ALL
SELECT 9,'14:00:25',0

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
LEFT JOIN @sampledata s2 ON s1.id < s2.id AND s1.flag = s2.flag
AND NOT EXISTS(
SELECT 1
FROM @sampledata s3
WHERE
s3.flag != s1.flag AND s3.id BETWEEN s1.id AND s2.id
)
WHERE s2.id IS NULL
ORDER BY s1.id

It involves a hidden RBAR which can hurt performance

see this nice article by Jeff

http://www.sqlservercentral.com/articles/T-SQL/61539/

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

He could use this one then

SELECT s1.id,s1.time,s1.flag
FROM @sampledata s1
LEFT JOIN @sampledata s2 ON s1.id = s2.id - 1 AND s1.flag = s2.flag
WHERE s2.id IS NULL

This builds on Lamprey's post. (thanks! )

yep looks fine and works so far as ID values doesnt have gaps

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:29:12

quote:
Originally posted by Lamprey

I was goofing around, and realized I did a 2012 solution. Although, it doesn't help the OP; I'm posting it just incase it helps anyone else (assuming it actually works correctly):
SELECT
*
FROM
(
SELECT
*,
LEAD(flag, 1, NULL) OVER (ORDER BY id) AS NextFlag
FROM
@sampledata
) AS A
WHERE
flag <> NextFlag
OR NextFlag IS NULL

yep...good example of LEAD function
thanks for sharing

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

xalpha
Starting Member

13 Posts

Posted - 2012-06-06 : 10:46:40

Thank you community! I have learned a lot with this topic. This are really genius queries - Thank you very much! I'm looking forward to test 2012 with the new functions. The example with LEAD ist very interesting. As far as I have to use 2008 the other examples are perfect work! Great...

Matthias

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-07 : 23:03:54

welcome
Glad that we could contribute something to enhance your knowledge

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources