Home » SQL & PL/SQL » SQL & PL/SQL » how can i return only one row of data on this query? (PL/SQL)
how can i return only one row of data on this query? [message #571649] Wed, 28 November 2012 09:01 Go to next message
ricanmix79
Messages: 1
Registered: November 2012
Location: Sacramento
Junior Member
work_order     unitid       frommi      tomi     frompm     topm
2666054111     06-154       77.000      85.000   77.370     null 
2666054111     06-154       77.000      85.000   null       85.370
2666054111     06-154       77.000      85.000   null       null
i used select distinct(work_order) to come up with the three different possible scenarios
the problem is that i need all this information on a single row
work_order     unitid       frommi      tomi     frompm     topm
2666054111     06-154       77.000      85.000   77.370     85.370
this is a conversion for distance. when i get this to work properly, it will
generate reports on thousands of work orders with their converted distance markers.
i am stuck Sad

[EDITED by LF: applied [pre] tags to preserve formatting]

[Updated on: Wed, 28 November 2012 10:29] by Moderator

Report message to a moderator

Re: how can i return only one row of data on this query? [message #571650 is a reply to message #571649] Wed, 28 November 2012 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: how can i return only one row of data on this query? [message #571654 is a reply to message #571649] Wed, 28 November 2012 10:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: how can i return only one row of data on this query? [message #571657 is a reply to message #571654] Wed, 28 November 2012 10:33 Go to previous message
Littlefoot
Messages: 19526
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
An aggregate function (such as MAX) might help. Here's an example; see if you can use it and apply to your own table & data:
This is what you currently have:
SQL> select * from test;

WORK_ORDER     UNITID     FROMPM       TOPM
---------- ---------- ---------- ----------
       266          6      77,37
       266          6                 85,37
       266          6

Now, apply MAX and see what happens:
SQL> select work_order,
  2         unitid,
  3         max(frompm) frompm,
  4         max(topm) topm
  5  from test
  6  group by work_order, unitid;

WORK_ORDER     UNITID     FROMPM       TOPM
---------- ---------- ---------- ----------
       266          6      77,37      85,37

SQL>
Previous Topic: View defination with schema name
Next Topic: Time Comparison
Goto Forum:
  


Current Time: Sat Aug 30 09:51:05 CDT 2014

Total time taken to generate the page: 0.42660 seconds