Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Problem distinct results (Oracle 10)
SQL Query Problem distinct results [message #352811] Thu, 09 October 2008 13:10 Go to next message
Ian Beaumont
Messages: 3
Registered: October 2002
Junior Member
Can anyone tell me if this is possible to do in a query.

Given the following data:

Route Cost Count
10 5 3
10 10 7
20 5 8
30 5 12
30 2 5
30 30 10

I want to show only distintive routes. The Cost and Count should return the first values for the appropriate route.

So this is the result I'm after:
10 5 3
20 5 8
30 5 12

TIA
Ian
Re: SQL Query Problem distinct results [message #352812 is a reply to message #352811] Thu, 09 October 2008 13:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions.

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.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: SQL Query Problem distinct results [message #352818 is a reply to message #352812] Thu, 09 October 2008 14:34 Go to previous message
Ian Beaumont
Messages: 3
Registered: October 2002
Junior Member
Thanks for the pointer Michel,

After a few more hours I eventually got there with ROW_NUMBER.

Phew!
Previous Topic: Help Required on SQL tuning
Next Topic: Page Size and Line Size
Goto Forum:
  


Current Time: Sat Dec 10 10:40:33 CST 2016

Total time taken to generate the page: 0.22697 seconds