Help needed on wrting SQL query

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

General SQL Server Forums

New to SQL Server Programming

Help needed on wrting SQL query

Author

Topic

sandeepsangshetty
Starting Member

2 Posts

Posted - 2011-05-20 : 01:07:08

Hi Friends,

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-20 : 01:45:54

There is no reliable "sequence" in a table unless you specify an ORDER BY.
So how should a SELECT get this rows ordered?

No, you're never too old to Yak'n'Roll if you're too young to die.

sandeepsangshetty
Starting Member

2 Posts

Posted - 2011-05-23 : 11:06:53

Sorry Friends in replying lately.
And thanks for ur reply.

I have a column to use order by column 1.e ItemId and Datetime(not shown in the table) also.

The Table like below

ItemId ScanningStationID Direction IsCorrect
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 594329393 1 (Right)
AG185106 594329393 0 (Right)
AG185106 594329393 1 (Wrong)
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 243203943 1 (Wrong)
AG185106 243203943 1 (Wrong)
AG185106 243203943 1 (Wrong)
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 243203943 0 (Wrong)
AG185106 243203943 0 (Wrong)
AG185106 243203943 1 (Right)
AG185106 243203943 0 (Right)
AG185106 243203943 1 (Right)
AG185106 594329393 0 (Wrong)

Here for a particular Item we need to have the scanning details.

Wrong entries will be considered in these below situations

* Logged 'IN' on One scanning station and 'OUT' from other station

* Logged 'IN' on One Floor not logged 'OUT' in that floor

* Consecutive Log 'IN'

* Consecutive Log 'OUT'

So for the above all wrong entries I want insert 'Iscorrect' column with '0'
For the correct entries I want insert 'Is correct column with '1'

How can I make this doable .
Please help me .
Thanks in advance

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources