I have a query that essentially tries to get the max serial value for each distinct `other_field` (simplified example below).

there are a few fields you need to know about

```
    ID text
    ser serial
    other_field text

```

For each distinct `other_field` I want to find the full row with the largest serial value

I basically want to return the whole row but end up having to join on the same table to retrieve what I want (since the group by clause is generally so restrictive). The query I use below

```sql
    SELECT small_q.id FROM (
        SELECT id, MAX(ser) as serial FROM table_name GROUP BY other_field
    ) max_q JOIN table_name full_t ON small_q.serial = max_q.serial

```

Is there any way for me to avoid having to join on the same table?

Many thanks in advance