CODEWITHSUNDEEP
sqloracle-databaseoracle10g
kyooryu
kyooryu

Reputation: 1509

Oracle - select rows with minimal value in a subset

I have a following table of dates:

dateID INT (PK),
personID INT (FK),
date DATE,
starttime VARCHAR, --Always in a format of 'HH:MM'

What I want to do is I want to pull rows (all columns, including PK) with lowest date (primary condition) and starttime (secondary condition) for every person. For example, if we have

row1(date = '2013-04-01' and starttime = '14:00')

and

row2(date = '2013-04-02' and starttime = '08:00')

row1 will be retrieved, along with all other columns.

So far I have come up with gradual filtering the table, but it`s quite a mess. Is there more efficient way of doing this?

Here is what I made so far:

    SELECT 
    D.id
    , D.personid
    , D.date        
    , D.starttime 
FROM table D 
JOIN (
        SELECT --Select lowest time from the subset of lowest dates
            A.personid, 
            B.startdate, 
            MIN(A.starttime) AS starttime 
        FROM table A 
        JOIN (
                SELECT --Select lowest date for every person to exclude them from outer table
                    personid
                    , MIN(date) AS startdate
                FROM table
                GROUP BY personid
            ) B
        ON A.personid = B.peronid
        AND A.date = B.startdate
        GROUP BY 
            A.personid, 
            B.startdate
    ) C
ON C.personid = D.personid
AND C.startdate = D.date 
AND C.starttime = D.starttime

It works, but I think there is a more clean/efficient way to do this. Any ideas?

EDIT: Let me expand a question - I also need to extract maximum date (only date, without time) for each person.

The result should look like this:

id
personid
max(date) for each person
min(date) for each person
min(starttime) for min(date) for each person

It is a part of a much larger query (the resulting table is joined with it), and the resulting table must be lightweight enough so that the query won`t execute for too long. With single join with this table (just using min, max for each field I wanted) the query took about 3 seconds, and I would like the resulting query not to take longer than 2-3 times that.

Upvotes: 0

Views: 11208

Answers (3)

Chandra
Chandra

Reputation: 1

select a.id,a.accomp, a.accomp_name, a.start_year,a.end_year, a.company
  from (select t.*, 
               min(t.start_year) over (partition by t.company) min_date,
               max(t.end_year) over (partition by t.company) max_date,
               row_number() over (partition by t.company 
                                  order by t.end_year desc) rn
          from temp_123 t) a
 where a.rn = 1;

Upvotes: 0

DazzaL
DazzaL

Reputation: 21973

you should be able to do this like:

select a.dateID, a.personID, a.date, a.max_date, a.starttime
  from (select t.*, 
               max(t.date) over (partition by t.personID) max_date,
               row_number() over (partition by t.personID 
                                  order by t.date, t.starttime) rn
          from table t) a
 where a.rn = 1;

sample data added to fiddle: http://sqlfiddle.com/#!4/63c45/1

Upvotes: 3

Santhosh
Santhosh

Reputation: 1791

This is the query you can use and no need to incorporate in your query. You can also use @Dazzal's query as stand alone

SELECT ID, PERSONID, DATE, STARTTIME
(
SELECT ID, PERONID, DATE, STARTTIME, ROW_NUMBER() OVER(PARTITION BY personid ORDER BY     STARTTIME, DATE) AS RN
FROM TABLE 
) A
WHERE 
RN = 1

Upvotes: 0

Related Questions