Query with aggregate functions

Question

I have 3 columns in my DB table as follows. 1. START_TIME 2. END_TIME 3. INDEX_NO

Sample records below

INDEX_NO                END_TIME                        START_TIME                      
------------- ------------------------------- -------------------------------
I152         13-JAN-14 02.30.00.000000000 AM 13-JAN-14 12.10.00.000000000 AM 
I151         13-JAN-14 05.30.00.000000000 AM 13-JAN-14 03.15.00.000000000 AM 
I152         20-JAN-14 02.30.00.000000000 AM 20-JAN-14 12.10.00.000000000 AM 
I151         20-JAN-14 05.30.00.000000000 AM 20-JAN-14 03.15.00.000000000 AM

From the above result set i need extract the MIN(START_TIME) and MAX(END_TIME) along with the INDEX_NO of the record with MIN(START_TIME)

To summarize the expected output is as follows.

INDEX_NO                END_TIME                        START_TIME                      
    ------------- ------------------------------- -------------------------------
    I152         20-JAN-14 05.30.00.000000000 AM 13-JAN-14 12.10.00.000000000 AM

Can anyone help me with a query of minimal cost to do the above extraction?

i am using oracle 11g.

Noel · Accepted Answer · 2013-10-23 06:42:55Z

3

FIRST function will come handy here.

select min(index_no) keep(dense_rank first order by start_time),
       max(end_time),
       min(start_time)
from mytab;

Demo here.

answered Oct 23, 2013 at 6:42

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Smeaton Over a year ago

Wow, every time I see queries like this I tell myself I need to read and practise the aggregate functions.

Josh Smeaton · Accepted Answer · 2013-10-23 06:36:27Z

-1

You start by writing the inner query, which finds the min time and the max time. You then write a query that selects the correct fields from the table, join it against the inner query with the min_time field.

This will produce multiple rows if there are several rows with the same minimum start time.

SELECT 
    mt.INDEX_NO,
    mt.START_TIME,
    t."max_time"
FROM MyTable mt
JOIN
(
SELECT
    min(START_TIME) as "min_time",
    max(END_TIME) as "max_time"
FROM MyTable
) t
ON mt.START_TIME = t."min_time"
;

answered Oct 23, 2013 at 6:36

Josh Smeaton

48.8k24 gold badges137 silver badges165 bronze badges

Collectives™ on Stack Overflow

Query with aggregate functions

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related