Logical Ordering Contraint - 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 2000 Forums

SQL Server Development (2000)

Logical Ordering Contraint

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-08-08 : 12:05:28

This question is interesting. Thinking about it I feel that there is probably a clever way to handle the problem in SQL, but for the life of me I can't come up with it.

I have two tables. Table A has two values (aseq and bID). Table B also has two values (bID and bseq). Here is what I need to do.

I need to check that the sequence of aseq logically fits with the sequence of bseq. A little confusing I know, but here is an example.

Table A
(aseq) (bID)
--------------
1 10000
2 10000
3 10001
4 10002

Table B
(bID) (bseq)
---------------
10000 1
10001 2
10002 3

In this example there are no errors. If you were to join the tables on bID the sequences orderd by aseq and bseq would both be in correct order. This is an example of a tables that i'd like to return me an error code.

Table A
(aseq) (bID)
----------------
1 10000
2 10000
3 10002
4 10001

Table B
(bID) (bSeq)
-----------------
10000 1
10001 2
10002 3

So if these tables were joined,

Table AB
(bID) (aSeq) (bSeq)
----------------------------
10000 1 1
10000 2 1
10001 4 2
10002 3 3

bseq is in the correct order, but now aseq is out of order. I need a sql statement that will check for the correct order on both columns, and return -1 or something if either column is out of order when joined.

Is there a logical way to do this, or am I going to have to do something less elegant?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-08-08 : 12:16:52

Is this what you mean?


select
    a.bid,
    a.aSeq,
    b.bSeq
from
    tablea a
    inner join tableb b
        on a.bid = b.bid and
           a.aSeq <> b.bSeq

Jay White
{0}

Nick
Posting Yak Master

155 Posts

Posted - 2003-08-08 : 12:44:38

No, the aSeq and bSeq actualy don't have much to do with each other. I should have made the values different.

Table A is a list of segments. They are in the order of aSeq. Table B is a list of segment sets (can contain one or more segments) These are ordered by bSeq. I basically need to check that no segment set is out of order. You can think of it like a number line.

A
1...2...3...4...5...6...7

B
1 {1, 2}
2 {3, 4}
3 {6}
4 {5}

would be invalid because B4 can't contain a segment that has already been placed in B3.

I think i'm explaining this very poorly, but I don't know how to make it sound any better.

Nick
Posting Yak Master

155 Posts

Posted - 2003-08-08 : 12:56:58

My initial thought on how this could be done (and far from elegantly) would be to join the tables and order them by aSeq, and then use a cursor to go through the recordset. Compare each bSeq to the next, and if the next bSeq was ever less than the first, to throw the error. That is ugly and as you'll all tell me cursors are bad.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 13:07:26

I think that part of the problem is that SQL server is relational and not hierarchacal..meaning the order of data in the database has no (nor should it) significant (or any) meaning.

For you top do what you want to do would require each row to identify it's relationship to other rows. Which you don't have.

In your last example would the rows in table B look like

1 1
1,2
2,3
2,4
3,6
4,5

If you just want to compare the row "before" your row (order by col1), then you can do that. But I'm thinking you're looking for more...

Brett

8-)

SELECT POST=NewId()

Nick
Posting Yak Master

155 Posts

Posted - 2003-08-08 : 13:11:34

quote:
Originally posted by X002548

If you just want to compare the row "before" your row (order by col1), then you can do that. But I'm thinking you're looking for more...

Well technically that is all I need to do. I can order by one of the columns, and then compare the other columns. If the row "before" my row (as it is ordered) is greater than the row I'm currently on, there is a problem.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 13:39:16

It's not there yet...but this is what I'm thinking...



USE Northwind
GO

CREATE TABLE myTable99 (col1 int, col2 int)
GO

INSERT INTO myTable99 (col1, col2)
SELECT 1,1 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,3 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,6 UNION ALL
SELECT 4,5 UNION ALL
SELECT 5,6 UNION ALL
SELECT 6,7
GO

-- Find the Last value for the 1st number

SELECT col1, MAX(col2) FROM myTable99 WHERE col1 IN (
SELECT MIN(Col1) FROM myTable99
)
GROUP BY col1

-- Fin the earliest for the preceding

SELECT col1, MIN(col2) FROM myTable99 WHERE col1 IN (
SELECT MIN(col1) FROM (
SELECT * FROM myTable99 Where Col1 > (
SELECT MIN(Col1) FROM myTable99)
) AS xxx)
GROUP BY col1
GO


DROP TBALE myTable99
GO

Brett

8-)

SELECT POST=NewId()

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-08 : 13:47:14

Nick-
This is a variation of Jeff Smith's ~~rank~~streak problem.

i.e. a sequence is properly ordered if the # of rows less than a.seq exactly equals the # of rows less than b.seq, for each row in the joined table.

X002548
Not Just a Number

15586 Posts

Posted - 2003-08-08 : 13:52:10

Jonathan

Isn't he trying to pull out the out of sequnce groups?

Also were you going to post a link?

Brett

8-)

SELECT POST=NewId()

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-08 : 14:02:06

quote:
Isn't he trying to pull out the out of sequnce groups?

Right, so he'll write a version of Jeff's algorithm that computes 2 streaks, returning some flag (he mentioned -1 as a return value) if they're not the same for every row in the joined table.

Here's Jeff's article:
http://www.sqlteam.com/item.asp?ItemID=12654

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-08-08 : 15:54:01

Isn't this more of a check to perform on each table? Is the join important ??


declare @TableA table (aSeq int, bID char(5))
declare @TableB table (bID char(5), bSeq int)

insert into @TableA
select 1,'10000' union
select 2,'10000' union
select 3,'10002' union
select 4,'10001'

insert into @TableB
select '10000',1 union
select '10001',2 union
select '10002', 3

--check tableA for rows that are out of Order:

select aSeq, bID
from @TableA a1
where exists
  (select 1 from @TableA a2 where a1.aSeq < a2.aSeq and a1.bID > a2.bID)

-- check tableB for rows that are out of Order:
select bSeq, bID
from @TableB b1
where exists
  (select 1 from @TableB b2 where b1.bSeq < b2.bSeq and b1.bID > b2.bID)

If both queries return NO rows, then all is OK ??? Does this work?

I don't know if the join is important because if the following are true:

"column a in Table A is ordered correctly when sorted by column x"

and

"column b in Table B is ordered correctly when sorted by column x"

Then this must be true:

"Table A joined to Table B by column x, and sorted by column x, will results in columns a and b being sorted correclty"

(please don't ask for the proof !!!!)

- Jeff

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-08-08 : 16:31:54

Nick-
After reading Jeff's response I realized I had missed one of your posts in the middle of this thread. After reading it I have no idea what you're after. I started typing a list of questions but they're all just guesses. Tell us again what you're trying to do?

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources