the record after x directly

akhamis · October 21st, 2004, 09:34 AM

Dear all, i'm using an oracle DB and using toad as a quring program
i need your support in having the following query
i have a column called sales
where there is a record called x what i want is get the next record directly after the x record in this column

taking in consedration that the only unique record in this table is the datetime.
thank
:D

Justin Cave · October 21st, 2004, 02:56 PM

How you define the "next record". Are you doing some sort of ordering of the data in your query, possibly based on the date column?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

akhamis · October 25th, 2004, 12:55 PM

no i don't do any sorts it should happen

Justin Cave · October 25th, 2004, 06:35 PM

If you aren't ordering the data, then there is no such thing as the "next row", at least to a relational database. If I do

Code:

SELECT *
  FROM someTable

a database is free to return the rows in whatever order it desires. The order may change over time. If you are not specifying an order, the database can order your rows arbitrarily. I assume you want a particular "next value" for each row, not an arbitrary value...

Also, what database are you using?

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

akhamis · November 1st, 2004, 02:45 AM

want a particular "next value" for each row,
regarding the DB it's an oracle DB

Justin Cave · November 1st, 2004, 03:00 AM

If you want to get the "next value" you have to define an order for the rows that will allow Oracle to figure out what the next value is. Once you have done that, you can use the lead() analytic function, i.e.

Code:

  1  select ename, sal, lead(sal) over (order by sal) next_sal
  2*   from emp
SCOTT @ HP92 Local> /

ENAME             SAL   NEXT_SAL
---------- ---------- ----------
SMITH             800        950
JAMES             950       1100
ADAMS            1100       1250
WARD             1250       1250
MARTIN           1250       1300
MILLER           1300       1500
TURNER           1500       1600
ALLEN            1600       2450
CLARK            2450       2850
BLAKE            2850       2975
JONES            2975       3000
SCOTT            3000       3000
FORD             3000       5000
KING             5000

14 rows selected.

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC