I have a SQL query written by someone else and I'm trying to figure out what it does. Can someone please explain what the Partition By
and Row_Number
keywords does here and give a simple example of it in action, as well as why one would want to use it?
An example of partition by:
(SELECT cdt.*,
ROW_NUMBER ()
OVER (PARTITION BY cdt.country_code, cdt.account, cdt.currency
ORDER BY cdt.country_code, cdt.account, cdt.currency)
seq_no
FROM CUSTOMER_DETAILS cdt);
I've seen some examples online, they are in bit too depth.
Thanks in advance!
This question is related to
sql
oracle
partition
row-number
analytic-functions
I know this is an old thread but PARTITION is the equiv of GROUP BY not ORDER BY. ORDER BY in this function is . . . ORDER BY. It's just a way to create uniqueness out of redundancy by adding a sequence number. Or you may eliminate the other redundant records by the WHERE clause when referencing the aliased column for the function. However, DISTINCT in the SELECT statement would probably accomplish the same thing in that regard.
That selects the row number per country code, account, and currency. So, the rows with country code "US", account "XYZ" and currency "$USD" will each get a row number assigned from 1-n; the same goes for every other combination of those columns in the result set.
This query is kind of funny, because the order by clause does absolutely nothing. All the rows in each partition have the same country code, account, and currency, so there's no point ordering by those columns. The ultimate row numbers assigned in this particular query will therefore be unpredictable.
Hope that helps...
I often use row_number() as a quick way to discard duplicate records from my select statements. Just add a where clause. Something like...
select a,b,rn
from (select a, b, row_number() over (partition by a,b order by a,b) as rn
from table)
where rn=1;
Source: Stackoverflow.com