CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Ang
Ang

Reputation: 153

Finding min + 1 in Postgres

Hi guys i have a postgres table with a column for event and a column for sequence. Every event may have multiple sequences. For ex:

Event  |   Sequence
a      |    1
a      |    4
a      |    5
b      |    1
b      |    2

Now i know that select min(sequence) group by event gives me the minimum sequence. How do i get the very next value after the min value. i hope that makes sense. Thanks in advance. I'm Using Postgres 9.3.

Upvotes: 2

Views: 1212

Answers (2)

Anthony E
Anthony E

Reputation: 11245

EDIT A bit more complicated, but if you need a query that doesn't rely on ROW_NUMBER(), use a subquery with a self-join to exclude rows with minimum sequence for each event:

SELECT outer_query.Event, MIN(outer_query.Sequence) AS SecondMinSeq
FROM Table1 as outer_query
INNER JOIN (
    SELECT Table1.Event, MIN(Sequence) AS MinSeq
    FROM Table1
    GROUP BY Table1.Event
) AS min_sequences
ON outer_query.Event = min_sequences.Event AND outer_query.Sequence <> min_sequences.MinSeq
GROUP BY outer_query.Event

SQL Fiddle: http://sqlfiddle.com/#!15/4438b/7

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 181077

You can use ROW_NUMBER() partitioning by Event and ordering by Sequence to get the second lowest sequence number per Event;

SELECT Event, Sequence 
FROM (
  SELECT Event, Sequence, 
         ROW_NUMBER() OVER (PARTITION BY Event ORDER BY Sequence) rn
  FROM Table1
) z
WHERE rn = 2;

An SQLfiddle to test with.

Upvotes: 2

Related Questions