Search code examples
sqlpostgresqlrow-number

JOIN for specific row number

I am using Postgres and would like to do a table join on a specific row number.

I have the following:

LEFT JOIN trip_approver_snapshot tas ON trip.trip_id=tas.trip_id 
and select * from (select row_number() over(order by id) as tas_row_num from trip_approver_snapshot) tasn where tasn.tas_row_num = 1

In order to try get just the first row. (I will also need to get row 2 and row 3, but first just want to get row 1 working).

However, the above query gives the following error:

SQL Error [42601]: ERROR: syntax error at or near "select" Position: 7102

enter image description here

More info:

If I run the following:

select 
  * 
from 
  (
    select 
      row_number() over(
        order by 
          id
      ) as tas_row_num 
    from 
      trip_approver_snapshot
  ) tasn 
where 
  tasn.tas_row_num = 1

enter image description here

Solution

  • You're trying to paginate. The idea you showed is syntactically wrong but even if you fix that, it's logic is still not quite right.

    If your goal was to only use the first row from trip_approver_snapshot, you could replace the table with a lateral subquery taking only that one row from it:

    LEFT JOIN LATERAL (SELECT * FROM rip_approver_snapshot AS tas
                   WHERE trip.trip_id=tas.trip_id
                   ORDER BY tas.id LIMIT 1 OFFSET 0) tas 
     ON TRUE

    To get only the 2nd row, you just increase OFFSET to 1. To get the 3rd bump it up to 2, and so on. The post I linked discusses LIMIT/OFFSET-based pagination as an intuitive but not really optimal method.

    There are better ways to build this join but best choice depends on your table structure, indexes and what your full query is aimed to achieve, none of which you disclosed.

    Right now, in the first query you posted, if you fetched stuff from trip_approver_snapshot, it had no way to correlate to its numbered copy in the subquery. You

    1. Took rows from trip_approver_snapshot
    2. Wiped their contents (you didn't select anything except a row_number())
    3. Asked for all of that (select *)
    4. Discarded all of that except the one with number 1.

    All of that as a join condition. A (select 1) would save you some trouble of getting a single row with a 1 in it.