CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsgreatest-n-per-group
gameip
gameip

Reputation: 15

Get row with the smallest time interval per value in a table

Here is my table:

Start Time            Stop time                 extension
----------------------------------------------------------
2014-03-03 10:00:00   2014-03-03 11:00:00         100
2014-03-03 10:00:00   2014-03-03 12:00:00         100
2014-03-05 10:00:00   2014-03-05 11:00:00         200
2014-03-03 10:00:00   2014-03-03 13:00:00         100
2014-03-05 10:00:00   2014-03-05 12:00:00         200
2014-03-05 10:00:00   2014-03-05 13:00:00         200

I want to get the smallest time interval for each extension:

Start Time            Stop time                  Extension
-------------------------------------------------------------
2014-03-03 10:00:00   2014-03-03 11:00:00         100
2014-03-05 10:00:00   2014-03-05 11:00:00         200

How can I write the sql?

Upvotes: 0

Views: 330

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659227

To get the row (including all original columns) with the smallest time interval for each extension (according to your updated question) the Postgres specific DISTINCT ON should be most convenient:

SELECT DISTINCT ON (extension)
       start_time, stop_time, extension
FROM   tbl
ORDER  BY extension, (stop_time - start_time);

Details in this related answer:
Select first row in each GROUP BY group?

Upvotes: 0

wildplasser
wildplasser

Reputation: 44250

CREATE TABLE fluff
        ( id SERIAL NOT NULL PRIMARY KEY
        , starttime TIMESTAMP NOT NULL
        , stoptime TIMESTAMP NOT NULL
        );

INSERT INTO fluff(starttime,stoptime) VALUES
  ('2014-03-03 10:00:00' , '2014-03-03 11:00:00' )
, ('2014-03-03 10:00:00' , '2014-03-03 12:00:00' )
, ('2014-03-03 10:00:00' , '2014-03-03 13:00:00' )
        ;

SELECT * FROM fluff fl
WHERE NOT EXISTS (
        SELECT *
        FROM fluff nx
        WHERE AGE(nx.stoptime,nx.starttime) < AGE(fl.stoptime,fl.starttime)
        );

Okay, After the update:

CREATE TABLE fluff2
        ( id SERIAL NOT NULL PRIMARY KEY
        , starttime TIMESTAMP NOT NULL
        , stoptime TIMESTAMP NOT NULL
        , bagger INTEGER NOT NULL
        );
;

INSERT INTO fluff2(starttime,stoptime, bagger) VALUES
  ( '2014-03-03 10:00:00', '2014-03-03 11:00:00',100)
, ( '2014-03-03 10:00:00', '2014-03-03 12:00:00',100)
, ( '2014-03-05 10:00:00', '2014-03-05 11:00:00',200)
, ( '2014-03-03 10:00:00', '2014-03-03 13:00:00',100)
, ( '2014-03-05 10:00:00', '2014-03-05 12:00:00',200)
, ( '2014-03-05 10:00:00', '2014-03-05 13:00:00',200)
        ;

SELECT * FROM fluff2 fl
WHERE NOT EXISTS (
        SELECT *
        FROM fluff2 nx
        WHERE nx.bagger = fl.bagger
        AND AGE(nx.stoptime,nx.starttime) < AGE(fl.stoptime,fl.starttime)
        );

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would use order by and limit:

select t.*
from table t
order by stop_time - start_time asc
limit 1;

This gives you all the columns in the table for the smallest duration.

Upvotes: 0

user330315
user330315

Reputation:

not sure what exactly you are after, but the "smallest interval" would be

select min(stop_time - start_time) 
from the_table

If you also need the two columns with that: 

select start_time, stop_time, duration
from (
   select start_time, 
          stop_time,
          stop_time - start_time as duration,
          min(stop_time - start_time) as min_duration
   from the_table
) t
where duration = min_duration;

The above would yield more than one row if multiple rows have the same duration. If you don't want that you can use:

select start_time, stop_time, duration
from (
   select start_time, 
          stop_time,
          stop_time - start_time as duration,
          row_numer() over (order by stop_time - start_time) as rn
   from the_table
) t
where rn = 1;

Upvotes: 1

Related Questions