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:

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

CodePudding user response：

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

CodePudding user response：

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.