CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionspostgresql-9.3distinct-on
Ang
Ang

Reputation: 153

POSTGRES min/least value

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.

Upvotes: 2

Views: 5729

Answers (3)

Vivek S.
Vivek S.

Reputation: 21895

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).

Upvotes: 2

zambesianus
zambesianus

Reputation: 1249

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

Upvotes: 1

ajshort
ajshort

Reputation: 3754

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.

Upvotes: 2

Related Questions