| Author |
 Topic  |
|
|
xalpha
Starting Member
11 Posts |
 Posted - 06/01/2012 : 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 - 06/01/2012 : 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
India
47157 Posts |
Posted - 06/01/2012 : 11:49:45
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 06/01/2012 : 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
Flowing Fount of Yak Knowledge
3832 Posts |
Posted - 06/01/2012 : 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 - 06/01/2012 : 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
India
47157 Posts |
Posted - 06/01/2012 : 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
India
47157 Posts |
Posted - 06/01/2012 : 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
11 Posts |
Posted - 06/06/2012 : 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
India
47157 Posts |
Posted - 06/07/2012 : 23:03:54
|
welcome
Glad that we could contribute something to enhance your knowledge
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Tricky query
