How to self join the same table by latest id or row number partition by a column
SELECT i.id as id_in, i.city, i.line_number as line_number_in,
o.id as id_out, o.city, o.line_number as line_number_out
FROM (select *, ROW_NUMBER() OVER (partition by line_number order by id) AS row_num from box_weight where city='hamilton' and eid is null order by line_number asc,id asc) i
LEFT JOIN (select *, row_number() over(partition by line_number order by id) as row_num from box_weight where city='auckland' and eid is null order by line_number asc, id asc) o
ON i.row_num = o.row_num and i.line_number = o.line_number
order by i.id asc
The set of rows that the ROW_NUMBER() function operates on is called a window.
The PARTITION BY clause divides the window into smaller sets or partitions. If you specify the PARTITION BY clause, the row number for each partition starts with one and increments by one.
Because the PARTITION BY clause is optional to the ROW_NUMBER() function, therefore you can omit it, and ROW_NUMBER() function will treat the whole window as a partition.
The ORDER BY clause inside the OVER clause determines the order in which the numbers are assigned.