Home » SQL & PL/SQL » SQL & PL/SQL » Need a select query for the following scenario (merged)
icon4.gif  Need a select query for the following scenario (merged) [message #340567] Wed, 13 August 2008 05:30 Go to next message
kathironline2003
Messages: 2
Registered: August 2008
Junior Member
Given A table with 5 columns(c1,c2,c3,c4,c5) and 1...N rows.
Note that c1 is an Integer column.


I need a select query that will give me an output of
c1 of row1, ((c1 of row2 - c1 of row1)

Just these column for all N rows.

Can anyone help
Re: Need a select query for the following scenario. Try it [message #340579 is a reply to message #340567] Wed, 13 August 2008 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Regards
Michel
Re: Need a select query for the following scenario. Try it [message #340581 is a reply to message #340567] Wed, 13 August 2008 06:07 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Use LAG analytical function.
Of course, you shall determine the order of rows first (which row is row1).
Help me design this Oracle query in MySQL [message #340612 is a reply to message #340567] Wed, 13 August 2008 07:36 Go to previous messageGo to next message
kathironline2003
Messages: 2
Registered: August 2008
Junior Member
Given A table with 5 columns(c1,c2,c3,c4,c5) and 1...N rows.
Note that c1 is an Integer column.


I need a select query that will give me an output of
c1 of row1, ((c1 of row2 - c1 of row1)

SELECT c1,
LEAD(c1, 1, 0) OVER (ORDER BY c1) - c1 AS diff
FROM table;

In Oracle LEAD functions solve it well. But is there an equivalent function in My SQL?
Re: Help me design this Oracle query in MySQL [message #340614 is a reply to message #340612] Wed, 13 August 2008 07:39 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
You may better be served by posting this in a MySQL forum? This is an Oracle forum.
Re: Help me design this Oracle query in MySQL [message #340621 is a reply to message #340612] Wed, 13 August 2008 08:03 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please don't start a new topic for the same question.

Regards
Michel
Previous Topic: explain plan for mview...
Next Topic: SUBTOTALS for the repeating row.
Goto Forum:
  


Current Time: Sat Dec 03 22:04:36 CST 2016

Total time taken to generate the page: 0.26098 seconds