May 19, 2011 at 11:23 pm
Hi Friends,
I am new to SQL 2005,I need small help in writing a query.
I have a table as below
Col1(varchar) Col2 (bit)
Ramu 1
Raju 0
Ravi 1
Mohan 1
Sham 0
Sayed 1
From the above table I need to only those rows which are not in sequence.
Means if the previous row is '1' ,the next row should be '0'.If it is not '0'then I need to get that row.If it is '1' then no need to get that row.
Can any one please help me in this .
Thanks in Advance
May 20, 2011 at 8:13 am
1. In future, please provide data in a consumable format.
eg
CREATE TABLE #t
(
Col1 varchar(30) NOT NULL
,Col2 bit NOT NULL
)
INSERT INTO #t
SELECT 'Ramu', 1
UNION ALL SELECT 'Raju', 0
UNION ALL SELECT 'Ravi', 1
UNION ALL SELECT 'Mohan', 1
UNION ALL SELECT 'Sham', 0
UNION ALL SELECT 'Sayed', 1
2. You should also provide the expected results.
Maybe:
Ravi, 1
Mohan, 1
Unfortunately you seem to have failed to understand a fundamental concept of relational databases.
A relation (table, view etc) is an UNORDERED set.
The data you have provided seems to have no order other than the order it is listed.
You need to provide another column by which the data can be ordered. eg An id or datetime column.
I suggest you post the question again with data that makes sense.
ps SELECT * FROM #t may appear to list the data in the order it was entered for small amounts of data under low loading.
This is just a quirk of the way SQL Server stores data and is not guaranteed.
May 20, 2011 at 8:52 am
As mentioned, you need some way to tell SQL Server what order the rows are in. Is there a column in the real table that has that? Maybe a time stamp, or an identity column?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply