POSTGRES min/least value

Question

I have a postgres 9.3 table with two columns. The first column has times and the second has routes. A route may have multiple times. I want to list all routes with their most minimum times. My table:

Times      Routes
07:15:00    Route a
09:15:00    Route a
08:15:00    Route b
11:30:00    Route b
09:15:00    Route c
12:00:00    Route c

What i want output:

 Times        Routes
 07:15:00     Route a
 08:15:00     Route b
 09:15:00     Route c

Any help would be appriciated and thanks in advance.

I know a 'least' function exists but each row in times column is always going to be the least across that row. What i am looking for is a combination of a distinct select on routes column and least(time)... — Ang
– Ang, Commented Jan 12, 2016 at 1:33

ajshort · Accepted Answer · 2016-01-12 01:43:49Z

2

This can be done using the MIN aggregate function, and then grouping by the Routes column:

SELECT Routes, MIN(Times) FROM Table GROUP BY Routes

The GROUP BY clause is used to group rows together into a single row which have the same value for the field(s) specified in the GROUP BY clause. You can then use aggregate functions such as MIN, MAX, SUM, AVG, ... to compute values from the rows which have been grouped together.

answered Jan 12, 2016 at 1:43

ajshort

3,7745 gold badges32 silver badges43 bronze badges

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

Comments

Vivek S. · Accepted Answer · 2016-01-12 05:05:32Z

2

select distinct on(routes) routes
      ,times 
from p 
order by routes,times asc

DISTINCT ON

Will return the "first" row of each set of rows where the expression is equal.

As per doc.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. [...] Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. [...] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

edited Jan 12, 2016 at 5:05

answered Jan 12, 2016 at 4:58

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

Comments

aphodius · Accepted Answer · 2016-01-12 02:01:18Z

1

The GROUP BY clause is useful when it is used in conjunction with an aggregate function. For your question, to find minimum value of times across all input values for routes you can use the GROUP BY clause to break into groups and for each group, you will be calculating the time using MIN aggregate function.

You can achieve this using group by select routes, min(times) from your_table group by routes

edited Jan 12, 2016 at 2:01

answered Jan 12, 2016 at 1:36

aphodius

1,24919 silver badges33 bronze badges

Collectives™ on Stack Overflow

POSTGRES min/least value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related