Solved

oracle sql greatest/least question.

Posted on 2003-12-01
4
1,416 Views
Last Modified: 2008-03-03
Hi..
I'm trying to return in a single row 2 values which are in 2 different rows but the same column


SELECT
c.CUSTOMER_NO,
GREATEST (hw.hardware_no) AS item1,
LEAST (hw.hardware_no) AS item2
FROM
jan.customer c,
jan.hardware hw
WHERE
hw.customer_no = c.customer_no And
c.customer_no = '1234567'



Table view
CUSTOMER NO hardware_no            serial_no
1234567          899999999999999    NULL
1234567          3333333333333       12344
1234567          877777777777777    NULL
1234567          2222222222222       12345


returns
CUSTOMER NO ITEM1 ITEM2
1234567 899999999999999 899999999999999
1234567 333333333333333 333333333333333
1234567 877777777777777 877777777777777
1234567 222222222222222 222222222222222


Where what I would want is...
CUSTOMER NO ITEM1           ITEM2
1234567     899999999999999 333333333333333
1234567     877777777777777 222222222222222


item1 is always larger that item2
serial_no which will always be null for item 1,
and is always present for item 2, though I doubt that this is necessary to solve this problem.

Thanks
0
Comment
Question by:Fylar
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 100 total points
ID: 9850091
don't use greatest and least,
use max and min instead:

try this:

SELECT
c.CUSTOMER_NO,
max (hw.hardware_no) AS item1,
min(hw.hardware_no) AS item2
FROM
jan.customer c,
jan.hardware hw
WHERE
hw.customer_no = c.customer_no And
c.customer_no = '1234567'
group by c.customer_no;

0
 
LVL 1

Author Comment

by:Fylar
ID: 9854860
Thanks seazodiac.
that works well.
I realised though, that my query was flawed.

CUSTOMER NO ITEM1           ITEM2
1234567     899999999999999 333333333333333
1234567     877777777777777 222222222222222

 will never be returned as this will only return a single row.
(which was my question, but not exactly what I meant) for each customer number, there will be an equal  number of item 1's & item 2's but I realised that I'm better getting this data using 2 sub-queries.

if you've got a quick fix, feel free to post it, but I'm awarding the points anyway. :)

Cheers,
Fylar
0
 

Expert Comment

by:sagarsingh
ID: 9865580
Hi Fyalr,

  You probably wanted your output in this format:

Customer_No        Item 1                    Item 2
1234567         899999999999999  222222222222222                  

that is output in one row with both Max and Min values. For this you need to use Virtual Views. I tried it out by making table like:

CUSTOMER (customer_No Number); And
Hardware (Customer_No Number, Hardware_No)

QUERY to be used:

select distinct a.customer_no,
(select max(hardware_no)  from hardware where customer_no=a.customer_no group by customer_no)item1 ,
(select min(hardware_no)  from hardware where customer_no=a.customer_no group by customer_no)item2
from customer a
/

bye
Sagar Singh

0
 
LVL 1

Author Comment

by:Fylar
ID: 9865806
Sagar.

Thanks for taking the time to reply.

I managed to work through my problem using a couple of sub-queries (which seems to be a little quicker than seazodiac's response and my initail idea - partly because these tables are huge).
The biggest issue is also that a customer may have any number of items (though generally between 1 - 4 of each, and equal numbers of 1's and 2's)

I've just about finished putting the final wrappers on this project now, though I'll likely have a play with the sql you've provided to try and broaden my understanding.


Thanks again.

Fylar
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question