Solved

oracle sql greatest/least question.

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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…
Suggested Courses

632 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