Star79
asked on
Row_number in SQL
Hello,
I have the below query:
,45166943 data
But when I run the same query with x.rn=1 commented , it returns all the 8 Po's.Not understanding the purpose of x.rn=1
I have the below query:
SELECT x.*
FROM (
SELECT V.CREATED as "CREATEDDATE",
LTRIM(RTRIM(V.value)) as "VENDORCODE",
LTRIM(RTRIM(V.NAME)) AS "VENDOR_NAME",
LTRIM(RTRIM(CO.DOCUMENTNO)) AS PO_NUM,
LTRIM(RTRIM(CO.EM_SC_PROGRAM_CODE)) AS "Program",
CO.DATEPRINTED AS "PO_CREATEDATE",
UPPER(LTRIM(RTRIM(u.name))) AS "BUYER",
--row_number() over(partition by V.value order by CO.DOCUMENTNO) as rn
CAST(rank() over(partition by V.value order by CO.DOCUMENTNO)as int) as rn FROM c_bpartner V
inner join c_order CO on co.c_bpartner_id = V.C_BPARTNER_ID
left join ad_user u on co.SALESREP_ID = u.AD_USER_ID
WHERE LTRIM(RTRIM(CO.DOCUMENTNO)) in ('45166940','45166941','45166942','45166943','45166944',
'45166945','45166946','45166947','45166948','45166949')
and CO.ISSOTRX='N' AND V.created > '01-NOV-2014'
)x
--and V.value in (SELECT VEND FROM DVTB)) x
where x.rn = 1
order by x.po_num;
When I run this it returns only 45166940,45166941,45166942But when I run the same query with x.rn=1 commented , it returns all the 8 Po's.Not understanding the purpose of x.rn=1
ASKER
So when there is no v.value row, it will not return anything?
yes
ASKER
Sorry not sure if iam getting this concept correct.Going back to my query:
If I comment the line where x.rn = 1 in the query
It returns rows for all the PO's in the where condition and when I uncomment it it doesnt return all the PO's except 4 of them
What does rn=1 signify.
If I comment the line where x.rn = 1 in the query
It returns rows for all the PO's in the where condition and when I uncomment it it doesnt return all the PO's except 4 of them
What does rn=1 signify.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>So when there is no v.value row, it will not return anything?<<
And, even if there is a v.value, it would only return the lowest documentno using rownumber() if there are more than one for that value. And more than one if there are multiple documentno's with the lowest rank using rank() but still not all of them. Change your order by to documentno DESC and you will likely see numbers like 25166949, 25166948, 25166947, etc. You also might try partitioning by v.value, documentno to see what returns.
And, even if there is a v.value, it would only return the lowest documentno using rownumber() if there are more than one for that value. And more than one if there are multiple documentno's with the lowest rank using rank() but still not all of them. Change your order by to documentno DESC and you will likely see numbers like 25166949, 25166948, 25166947, etc. You also might try partitioning by v.value, documentno to see what returns.
for each v.value, return the first row ordered by co.documentno
if there is a tie (two or more rows for one v.value that have the same first co.documentno) then you will get all of those rows. but no other document numbers