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
Messages: 8
Registered: March 2008
Junior Member
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.

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: 65090
Registered: March 2007
Location: Nanterre, France, http://...
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.

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
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link for more information.



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

Current Time: Fri Jul 28 01:50:08 CDT 2017

Total time taken to generate the page: 0.14667 seconds