Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 21st, 2004, 09:34 AM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default the record after x directly

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

 
Old October 21st, 2004, 02:56 PM
Authorized User
 
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 25th, 2004, 12:55 PM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

no i don't do any sorts it should happen

 
Old October 25th, 2004, 06:35 PM
Authorized User
 
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old November 1st, 2004, 02:45 AM
Registered User
 
Join Date: Jun 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old November 1st, 2004, 03:00 AM
Authorized User
 
Join Date: Apr 2004
Posts: 70
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
just directly close the window kanoorani Javascript 1 May 5th, 2007 02:44 AM
just directly close the window kanoorani Javascript How-To 0 May 5th, 2007 12:13 AM
Printing directly leo_vinay ASP.NET 1.0 and 1.1 Basics 0 October 26th, 2005 11:17 PM
Directly editing Binary HiddenFire VB.NET 4 July 6th, 2005 12:14 PM
Printing directly to a printer Louisa VB.NET 2002/2003 Basics 4 January 16th, 2004 09:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.