SELECT statement using WHERE that involves value from "next" row?

[Adam Nemitoff]

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

5
6
1
2
1
3

[Aggro]

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

[Aggro]

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

[Adam Nemitoff]

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

Aggro <sp**********@yahoo.com> wrote in message news:<DA************@read3.inet.fi>...

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

[chris]

go****@teamnemitoff.com (Adam Nemitoff) wrote in message news:<b4**************************@posting.google. com>...

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

Aggro <sp**********@yahoo.com> wrote in message news:<DA************@read3.inet.fi>...

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

There is a solution, but you need to think carefully about why you are
having this difficulty - working with databases requires understanding
of a few concepts, eg, that rows are not in a particular order. What
happenes for example, if user A writes a row to the database and gets
id 5, but before he has written his following row, user B writes and
gets id 6 (this may happen in milliseconds). Even transactions will
not ensure that a given user gets sequential id numbers, because you
are bending a database concept. You must link rows by the metadata
that associates them, eg by having a 'ParentId', which is populated in
the second row with the id of the first row.

Anyway, the way I believe you may be able to implement your problem,
is preferably using the ParentId idea before, then joining a table to
itself, as follows:

SELECT fieldlist
FROM tablename t1, tablename t2
WHERE (t1.Id + 1)=t2.Id
AND t1.myField='1'
AND t2.myField='2'

Bear in mind however that this query will link ALL consecutive rows,
not just the pairs as they were created (eg, id 1 will be linked with
id2, and id 2 will be linked with id 3, etc).

[Chuck Gadd]

On 17 Dec 2003 16:42:12 -0800, go****@teamnemitoff.com (Adam Nemitoff)
wrote:

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

If you do a "self join", you might be able to get what you are trying
for.

select * from mytable as table1, mytable as table2 where
table2.id = table1.id+1

Chuck Gadd
http://www.csd.net/~cgadd/aqua

[Adam Nemitoff]

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

Aggro <sp**********@yahoo.com> wrote in message news:<DA************@read3.inet.fi>...

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

[chris]

go****@teamnemitoff.com (Adam Nemitoff) wrote in message news:<b4**************************@posting.google. com>...

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

Aggro <sp**********@yahoo.com> wrote in message news:<DA************@read3.inet.fi>...

Adam Nemitoff wrote:

Is is possible to construct a SELECT statement that contains a WHERE
clause that uses the value from a column in the "next" row?

ie. given a table with a single field named "myField" with the
following values I want a SELECT statement that selects the rows
"WHERE myField='1' and [NEXT ROW]myField = '2' ":

You _do_ know that rows _are not_ in any specific order in the database.
i.e. It is not giaranteed that they are in the same order you inserted
them? And query like that looks very suspicious, which might cause
problems in the near future _if_ you somehow would get it to work (I
don't know any way, except by doing two queries. )

However I might suggest you another way to solve this problem. What if
you would add another column to your table, which would hold that mystic
value from "next" row?

You might also want to add id-field to your table with auto_increment
value, so that you know in what order your rows were inserted, and the
"next row" would have some meaning.

There is a solution, but you need to think carefully about why you are
having this difficulty - working with databases requires understanding
of a few concepts, eg, that rows are not in a particular order. What
happenes for example, if user A writes a row to the database and gets
id 5, but before he has written his following row, user B writes and
gets id 6 (this may happen in milliseconds). Even transactions will
not ensure that a given user gets sequential id numbers, because you
are bending a database concept. You must link rows by the metadata
that associates them, eg by having a 'ParentId', which is populated in
the second row with the id of the first row.

Anyway, the way I believe you may be able to implement your problem,
is preferably using the ParentId idea before, then joining a table to
itself, as follows:

SELECT fieldlist
FROM tablename t1, tablename t2
WHERE (t1.Id + 1)=t2.Id
AND t1.myField='1'
AND t2.myField='2'

Bear in mind however that this query will link ALL consecutive rows,
not just the pairs as they were created (eg, id 1 will be linked with
id2, and id 2 will be linked with id 3, etc).

[Chuck Gadd]

On 17 Dec 2003 16:42:12 -0800, go****@teamnemitoff.com (Adam Nemitoff)
wrote:

I do have an autoincrementing 'id' field

Thereform my query is semantically this:
SELECT ROW(id) WHERE item1='foo' AND ROW(id+1).item1='bar'

If you do a "self join", you might be able to get what you are trying
for.

select * from mytable as table1, mytable as table2 where
table2.id = table1.id+1

Chuck Gadd
http://www.csd.net/~cgadd/aqua