help with selecting certain records

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 2000 Forums

Transact-SQL (2000)

help with selecting certain records

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-01-24 : 16:41:22

How do I go about retrieving the records only when a change in the data
column 'Aux1' is detected?

Example, here is a table with some data

ID | Timesecs | Aux1
--------------------
1 | 51111111 | ON
2 | 51111112 | ON
3 | 51111113 | ON
4 | 51111114 | OFF
5 | 51111115 | OFF
6 | 51111116 | ON
7 | 51111117 | ON
8 | 51111118 | ON
9 | 51111119 | OFF
10 | 51111120 | OFF
11 | 51111121 | OFF
etc....

The results from the records I retrieve should look like this:

ID | Timesecs | Aux1
--------------------
1 | 51111111 | ON
4 | 51111114 | OFF
6 | 51111116 | ON
9 | 51111119 | OFF
etc....

CREATE TABLE #temp (id int IDENTITY, timesecs int, Aux1 char(4))
INSERT INTO #temp VALUES ('5111111', 'ON')
INSERT INTO #temp VALUES ('5111112', 'ON')
INSERT INTO #temp VALUES ('5111113', 'ON')
INSERT INTO #temp VALUES ('5111114', 'OFF')
INSERT INTO #temp VALUES ('5111115', 'OFF')
INSERT INTO #temp VALUES ('5111116', 'ON')
INSERT INTO #temp VALUES ('5111117', 'ON')
INSERT INTO #temp VALUES ('5111118', 'ON')
INSERT INTO #temp VALUES ('5111119', 'OFF')
INSERT INTO #temp VALUES ('5111120', 'OFF')
INSERT INTO #temp VALUES ('5111121', 'OFF')

SELECT * FROM #temp
DROP TABLE #temp

Jose

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 16:53:45

[code]SELECT TOP 1 timesecs, Aux1 FROM #temp
UNION
SELECT t2.timesecs, t2.Aux1 FROM #temp t1
JOIN #temp t2 ON t1.id+1 = t2.id
WHERE t1.Aux1 <> t2.Aux1[/code]

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2008-01-24 : 18:33:45

That worked great jdaman

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-24 : 18:58:34

You'll probably want to add an ORDER BY to the TOP 1 query. Otherwise you may get incorrect results.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-24 : 19:25:05

quote:
Originally posted by Lamprey

You'll probably want to add an ORDER BY to the TOP 1 query. Otherwise you may get incorrect results.

Yes, but you will need to wrap it as a derived table. Using ORDER BY and UNION together will generate an error.

SELECT timesecs, Aux1 FROM (
SELECT TOP 1 timesecs, Aux1 FROM #temp
ORDER BY id )a
UNION
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources