CODEWITHSUNDEEP
sqloracle-databaseminrank
user1094628
user1094628

Reputation: 239

Select only of MIN values in ORACLE SQL

I want to select the earliest date and time from my data set and only show those row(s) that fit the requirement. And show 3 columns.

I got it to show the data in the right order by date and time. How can I just get it to show the data that have the mininum values? I tried using first, limit, and top x, but they don't work, and aren't exactly what I need since the answer may have more than 1 value.

Here is my example sql:

Select report, date, time
  From events
 order by date, time

Upvotes: 2

Views: 10249

Answers (3)

Alex Poole
Alex Poole

Reputation: 191265

Guessing a bit as the data types aren't clear, but something like this might work (example using a CTE to generate dummy data):

with events as (
select 'report1' as report, '01/01/2012' as date_field, '0800' as time_field
    from dual
union all select 'report1', '01/01/2012', '0900' from dual
union all select 'report1', '01/02/2012', '0930' from dual
union all select 'report2', '01/01/2012', '0900' from dual
union all select 'report2', '01/01/2012', '0900' from dual
union all select 'report2', '01/01/2012', '1000' from dual
)
select report, date_field, time_field
from (
    select report, date_field, time_field,
        row_number() over (partition by report
            order by to_date(date_field, 'MM/DD/YYYY'), time_field) as rn
    from events
)
where rn = 1
order by report;

REPORT  DATE_FIELD TIME
------- ---------- ----
report1 01/01/2012 0800
report2 01/01/2012 0900

You may have a different date format mask; I've assumed US format as you referred to 'military time'.

Depending on how you want to treat ties, you'll want rank or dense_rank instead of row_number. See the documentation of analytic functions for more info. As Justin pointed out you probably want rank, which with the same data gives:

REPORT  DATE_FIELD TIME
------- ---------- ----
report1 01/01/2012 0800
report2 01/01/2012 0900
report2 01/01/2012 0900

The inner select adds an extra rn column that assigns a ranking to each result; each value of report will have at least one row that gets assigned 1 (if using rank, otherwise exactly one), and possibly rows with 2, 3 etc. The one(s) with 1 will have the earliest date/time for that report. The outer query then filters to only show those ranked 1, via the where rn = 1 clause, hence only giving the data with the earliest date/time for each report - the rest is discarded.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231661

Something like this should work assuming that every row has a validly formatted day and time component.

SELECT report,
       dt,
       time
  FROM (SELECT report,
               dt,
               time,
               rank() over (partition by report
                                order by to_date( dt || ' ' || time, 'MM/DD/YYYY HH24MI' ) asc) rnk
          FROM events)
 WHERE rnk = 1

From a data model standpoint, however, you should always store dates in DATE columns rather than trying to store them in VARCHAR2 columns. Since you want date comparison and sorting semantics, you'll have to transform the data into a DATE which is costly at runtime. And there is a great chance that someone will eventually store data in a different format in the column or store an invalid string (i.e. a day of '02/29/2011') which will cause your query to start generating errors.

Upvotes: 1

Wim
Wim

Reputation: 1096

Try this:

 SELECT report, date, time
 FROM (SELECT report, date, time,
         ROW_NUMBER() OVER(PARTITION BY report ORDER BY date ASC, time ASC) AS RowNum
       From events
      ) AS CTE 
 WHERE CTE.RowNum = 1

Upvotes: 4

Related Questions