Home » SQL & PL/SQL » SQL & PL/SQL » Grouping rows based on value - pl/sql (Oracle 10g,Windows Server 2003)
Grouping rows based on value - pl/sql [message #357397] Wed, 05 November 2008 03:38 Go to next message
nooruls143
Messages: 8
Registered: March 2008
Junior Member
Hi,
I have a table with GPS readings. The columns are

ID (primary)
object_id (number)
reading_time (date)
x_coordinate varchar2
y_coordinate varchar2


I am looking for a query where I group the rows as one row based on co-ordinate reading pair. Lets say, I have first 10 rows with same readings and different reading times. The 11th row has different co-ordinate pair. So, the query should return 2 rows with first row indicating the from reading_time to reading_time.
If the 11th reading is different and 12th reading is the same as the first 10 readings, it should display it as a seperate row (3 rows). Can this be done ? If so, I shall be grateful if anyone can provide me a query.

Ta,
Noorul
Re: Grouping rows based on value - pl/sql [message #357399 is a reply to message #357397] Wed, 05 November 2008 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
yes it is possible, have a look at LAG/LEAD functions.

For more, post a test case: create table and insert statements along with the result you want with these data.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Grouping rows based on value - pl/sql [message #357438 is a reply to message #357397] Wed, 05 November 2008 05:54 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link for more information.

http://www.orafaq.com/forum/m/356100/94420/#msg_356100

Regards

Raj
Previous Topic: table transfer
Next Topic: Urgent help on ORA-06502.
Goto Forum:
  


Current Time: Sat Feb 15 23:51:11 CST 2025