Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle sql greatest/least question.

Posted on 2003-12-01
4
Medium Priority
?
1,425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 400 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

671 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