Solved

oracle sql greatest/least question.

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to free up undo space? 3 51
Oracle SQL Select unique values from two columns 4 66
Create Index on a Materialized View 5 35
migration MS SQL database to Oracle 30 70
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

828 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