CODEWITHSUNDEEP
mysqlsqlrow-number
anonymous
anonymous

Reputation: 101

MYSQL query with ROW_NUMBER and RIGHT JOIN

I need to fetch the data according to row number , for eg : If I pass ROW_NUM=2, it should fetch me the second row data alone. Can someone please help me with a MYSQL query for this. I tried with ROW_NUMBER(), but its not working

Select arh.ROUTE_NUM,ard.ACTIVE_ROUTE_DETAILS_ID,ROUTE_NUM
from HEADER as arh right join DETAILS ard on HEADER_ID =
ard.HEADER_ID where arh.INFOPRO_DIV IN (050) and  arh.ROUTE_DATE = '2020-11-25'

enter image description here

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL tables represent unordered sets. So, to define the second row, you need an ORDER BY. The canonical way to do this in MySQL uses LIMIT/OFFSET:

select arh.ROUTE_NUM, ard.ACTIVE_ROUTE_DETAILS_ID, ROUTE_NUM
from HEADER arh join
     DETAILS ard
     on HEADER_ID = ard.HEADER_ID
where arh.INFOPRO_DIV IN (050) and
      arh.ROUTE_DATE = '2020-11-25'
order by arh.ROUTE_NUM, ard.ACTIVE_ROUTE_DETAILS_ID
limit 1 offset 1;   -- "offset 1" corresponds to the second row

Note: Most folks prefer LEFT JOIN to RIGHT JOIN. That said, the WHERE clause turns the outer join into an inner join anyway, so I think a regular join would work. Plus, it makes sense that a join from headers to details would just work.

Upvotes: 2

Related Questions