CODEWITHSUNDEEP
sqloracle-databaseoracle11g
J145
J145

Reputation: 699

Oracle - Select distinct on multiple columns where count = 1

I was wondering if anyone could help me out. I am wanting to write a query which identifies the manufacturer and model of cars in which only 1 was sold on the current day if that particular manufacturer/model combination had not been sold before

lets say I have a table called carsSold which has 4 columns: ID, manufacturer, model and dateCreated and has the following data: Table Data

What I want to do is only return the manufacturer and model rows where the entry was created on the current day and there is only 1 of that type of combination (from any date in history). If the currentDate was 08-08-2016 from my data set I would expect to see id’s 1, 2, 4 and 7.

Is it possible to do all this? It seems like quite a lot to achieve from a select statement. I assume it would start something similar to below only it would contain a distinct and count for the two rows manufacturer and model or something along those lines? Im just not sure how to achieve exactly what I need.

 select * 
 from carsSold
 where dateCreated > trunc(sysdate)

Thanks in advance for taking the time to look at this and any help I may receive.

Upvotes: 3

Views: 3177

Answers (1)

shawnt00
shawnt00

Reputation: 17935

select min(Id), Manufacturer, Model, min(dateCreated)
from carsSold
group by Manufacturer, Model
having count(*) = 1 and min(dateCreated) = trunc(sysdate);

This is a pretty standard group by query. The having guarantees that we only get groups with a single row. The condition against dateCreated must use an aggregate but since there's only one row in the group then min() is really the same thing.

Paraphrasing: Return all groups where the combination of manufacturer and model is counted once and the earliest date of those is the current day (or any date of your choosing.) The id and created date values are recovered as dummy aggregates.

EDIT: It's pretty clear to me that you don't intend to run this query retrospectively and that you'll only be interested in using a date of current day. So I didn't feel the need to make this comment earlier. But if you did need to look back in time then it's quite trivial to add where dateCreated <= <some date> and substitute the same date in the having clause so that all later-created rows are not considered.

Edit 2: To simply get the earliest row for each combination you can use not exists. There are actually multiple ways to express this query but here is a simple one. It's really not even related to the query above.

select * from carsSold c
where not exists (
    select 1 from carsSold c2
    where
            c2.Manufacturer = c.Manufacturer
        and c2.Model = c.Model
        and c2.dateCreated < c.dateCreated
)

Upvotes: 3

Related Questions