postgresql select minimum from a value pair of two columns

Question

I have a table that looks like this:

id | group | title | description | added | modified
---------------------------------------------------
1  |   1   |  ...  |     ...     |  ...  |   ...
2  |   1   |  ...  |     ...     |  ...  |   ...
3  |   2   |  ...  |     ...     |  ...  |   ...
4  |   2   |  ...  |     ...     |  ...  |   ...
5  |   3   |  ...  |     ...     |  ...  |   ...

Now I need to select the first row of every group value. I think using the DISTINCT keyword is kind of the right direction here. But using:

SELECT DISTINCT group FROM table;

obviously would return only the group values, but not the remaining fields:

group
-----
  1
  2
  3

Does anyone know how to accomplish this?

What I'm looking for is - in regard to the above example - this kind of output:

id | group | title | description | added | modified
---------------------------------------------------
1  |   1   |  ...  |     ...     |  ...  |   ...
3  |   2   |  ...  |     ...     |  ...  |   ...
5  |   3   |  ...  |     ...     |  ...  |   ...

Thanks in advance!!

I changed it to zgroup (to be pronounced with a German accent ;-) — wildplasser
– wildplasser, Commented Jul 17, 2013 at 17:46

Joachim Isaksson · Accepted Answer · 2013-07-17 18:03:03Z

2

You could use something like;

SELECT "id", "group", "title", "description", "added", "modified"
FROM (
  SELECT *,ROW_NUMBER() OVER (PARTITION BY "group" ORDER BY "id") rn
  FROM Table1
) this_could_be_called_anything
WHERE rn=1;

An SQLfiddle to test with.

edited Jul 17, 2013 at 18:03

answered Jul 17, 2013 at 17:38

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

2 Comments

tiiv Over a year ago

If you don't mind, could you elaborate what happens here? Especially what "a" does?

Joachim Isaksson Over a year ago

@tiiv a is just an alias for the sub-expression (one is required, you could call it anything) ROW_NUMBER() assigns a row number sequence (1..n) per partition (group), I just grab all rows that have a row number of 1, ie the first row in every group.

wildplasser · Accepted Answer · 2013-07-17 17:39:34Z

0

SELECT mk.* FROM meuk mk
JOIN ( SELECT zgroup, MIN(id) AS id
        FROM meuk
        GROUP BY zgroup
        ) agg ON agg.id = mk.id
        ;

Or this one:

SELECT * FROM meuk mk
WHERE NOT EXISTS ( SELECT  *
        FROM meuk nx
        WHERE nx.zgroup = mk.zgroup
        AND nx.id < mk.id
        )
        ;

answered Jul 17, 2013 at 17:39

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

2 Comments

tiiv Over a year ago

Your second solution works like a charm. I can't really say that I understand your first solution though =\

wildplasser Over a year ago

Well is is a joined (aggregating) subquery, just like the a in Joachim's solution and the tg in collapsar's answer.

collapsar · Accepted Answer · 2013-07-17 17:39:27Z

0

try this One:

    select t.*
      from table t
inner join (
                select min(te.id)   id_pivot
                     , te.grp
                  from table te
              group by te.grp 
           ) tg
        on tg.id_pivot = t.id
         ;

answered Jul 17, 2013 at 17:39

collapsar

17.4k5 gold badges40 silver badges66 bronze badges

Collectives™ on Stack Overflow

postgresql select minimum from a value pair of two columns

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related