Solved

oracle sql greatest/least question.

Posted on 2003-12-01
4
1,412 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now