Equivalent of FIRST in Postgresql

Question

Edit: Answer is to use MIN. it works on both strings & numbers. Credit to @cadet down below.

Original question:

I've been reading through similar questions around this for the last half an hour and cannot understand the responses so let me try to get a simple easy to follow answer.

What is the PostgresSQL equivalent to this code which I would write if I were using SQL Server, to bring back the first value in field2 when aggregating:

Select field1, first(field2) from table group by field1?

I have read that DISTINCT ON is the right thing to use? In that case would it be: Select field1, DISTINCT ON(field2) from table group by field1? because that gives me a syntax error

Edit:

Here is the error stating that the FIRST function does not exist in PostGresSQL:

ERROR:  function first(asset32type) does not exist
LINE 1: Select policy, first (name) from multi_asset group by policy...
                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 16

And in case it isn't already clear when I say that in SQL Server the first() function brings back the first value in field2 when aggregating, I mean if you had data like this:

field1	field2
Tom	32
Tom	53

Then select field1, first(field2) group by field1 would give you back: Tom, 32 - i.e. it picks the first value from field2

What does this `first()` do? Not all of us speak Microsoft SQL Server. — Laurenz Albe, Jun 10 '22 at 09:37
You mean MIN() ? And without the syntax error message, nobody can help you with that one either. — Frank Heikens, Jun 10 '22 at 09:40
@LaurenzAlbe In SQL server first brings back the first value in field2 when aggregating. I mentioned that in the post. — Reddspark, Jun 10 '22 at 10:09
https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564?answertab=modifieddesc#tab-top first() function is from microsoft access? — jian, Jun 10 '22 at 10:09

score 2 · Answer 1 · answered Jun 10 '22 at 10:07

2

Maybe this one, using DISTINCT ON():

SELECT DISTINCT ON (field1)
  field1
, field2
FROM table
ORDER BY
 field1
, field2;

But without any data or any example, it's just a wild guess.

answered Jun 10 '22 at 10:07

Frank Heikens

117,544
24
142
135

score 1 · Accepted Answer · answered Jun 10 '22 at 09:47

1

If first is related with specific order

select distinct field1, 
 first_value(field2) 
 over (partition by field1 order by field2) from 
 
 (
   values (1,10),(1,11),(1,12),(2,23),(2,24)
 ) as a(field1,field2)

If first is just minimum or maximum

select field1, 
 min(field2)
 from 
  (
   values (1,10),(1,11),(1,12),(2,23),(2,24)
 ) as a(field1,field2)
 group by field1

answered Jun 10 '22 at 09:47

Kadet

1,344
3
10

Ah ok. MIN worked. Surprised no one had mentioned that in all the posts I saw where people were asking something similar! – Reddspark Jun 10 '22 at 10:23
Well, the first one is something different from the smallest one. See Frank's answer. – Laurenz Albe Jun 10 '22 at 12:09

Equivalent of FIRST in Postgresql

2 Answers2