| Use DISTINCT on subset of all fields [message #374822] |
Thu, 05 July 2001 01:29  |
Ivo Beckers
Messages: 4
Registered: June 2001
|
Junior Member |
|
|
My table contains customer records with multiple records per customer. As a result of a query, I´m only interested in one record per customer with the highest value of a certain field in the record.
I thought of using DISTINCT, but can I use DISTINCT on a subset of all fields? Or sort the table in a certain way that the query result only shows the first unique records for a customer.
Other ideas are welcome to.
|
|
|
|
|
|
| Re: Use DISTINCT on subset of all fields [message #374831 is a reply to message #374822] |
Thu, 05 July 2001 06:54  |
kavithask
Messages: 34
Registered: March 2001
Location: London
|
Member |
|
|
Hi,
You can acheive this with the help of a correlated sub-query.
SELECT customer.*
FROM customer c1
WHERE (cust_code, transaction_date) IN (
SELECT cust_code, max(transaction_date)
FROM customer c2
WHERE c2.cust_code = c1.cust_code
GROUP BY c2.cust_code)
/
HTH
Kavitha
|
|
|
|
| Re: Use DISTINCT on subset of all fields [message #374856 is a reply to message #374829] |
Fri, 06 July 2001 08:25 |
Ivo Beckers
Messages: 4
Registered: June 2001
|
Junior Member |
|
|
Sorry, I can't get it work. Here's a snapshot of the table and the desired result.
** TABLE **
severity, timestamp, customer, servicetype, service
5 10:00 GETRONICS ACCESS ASAP
5 10:00 GETRONICS SERVICE ASAP
3 10:05 GETRONICS SYSTEM CPUM
3 10:05 GETRONICS SERVICE ASAP
5 10:10 GETRONICS SERVICE ASAP
** DESIRED RESULT **
5 10:00 GETRONICS ACCESS ASAP
5 10:00 GETRONICS SERVICE ASAP
3 10:05 GETRONICS SYSTEM CPUM
** EXPLENATION **
I need all unique combinations of "customer, servicetype, service" with the highest "severity" and oldest "timestamp".
The last record two records from the table are skipped, since the customer, servicetype and service are already within the 2nd record.
|
|
|
|
| Re: Use DISTINCT on subset of all fields [message #374880 is a reply to message #374829] |
Mon, 09 July 2001 12:08 |
kavithask
Messages: 34
Registered: March 2001
Location: London
|
Member |
|
|
Hi,
The following should give you the desired results. I have formed the SQL based on the data given. If it does not work for any other set of data, please let me know and I will change the SQL to fit in your changed requirements.
select customer,
service_type,
service,
max(severity),
to_char(max(timestamp), 'HH24:MI') time
from atemp a
where severity in (
select max(severity)
from atemp b
where a.customer = b.customer
and a.service_type = b.service_type
and a.service = b.service
)
or timestamp in (
select max(timestamp)
from atemp b
where a.customer = b.customer
and a.service_type = b.service_type
and a.service = b.service
)
group by customer, service_type, service
Cheers
Kavitha
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
