I have often wanted to get the actual detail columns in a group by, that weren’t being grouped.. this is how
Here’s a quick summary of OVER and PARTITION BY (new in SQL 2005), for the uninitiated or forgetful…
OVER
OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. For example, this query:
SELECT SUM(Cost) OVER () AS Cost
, OrderNum
FROM Orders
Will return something like this:
Cost OrderNum
10.00 345
10.00 346
10.00 347
10.00 348
Quick translation:
•SUM(cost) – get me the sum of the COST column
•OVER – for the set of rows….
•() – …that encompasses the entire result set.
OVER(PARTITION BY)
OVER, as used in our previous example, exposes the entire resultset to the aggregation…”Cost” was the sum of all [Cost] in the resultset. We can break up that resultset into partitions with the use of PARTITION BY:
SELECT SUM(Cost) OVER (PARTITION BY CustomerNo) AS Cost
, OrderNum
, CustomerNo
FROM Orders
My partition is by CustomerNo – each “window” of a single customer’s orders will be treated separately from each other “window”….I’ll get the sum of cost for Customer 1, and then the sum for Customer 2:
Cost OrderNum CustomerNo
8.00 345 1
8.00 346 1
8.00 347 1
2.00 348 2
The translation here is:
•SUM(cost) – get me the sum of the COST column
•OVER – for the set of rows….
•(PARTITION BY CustomerNo) – …that have the same CustomerNo.
—
Now I also wanted to perform what would be a ‘HAVING’ clause on the group by.. couldn’t find anything yet, so just put a wrapper select statement with the added where clause
It is real handy as well, when trying to:
out of every group of records, take values only where a column equals a certain situation.
In my common case.. Given a key (ie. xref source system, xref source system person id), give me the records for each key value pair, where the date the record was loaded is the most recent.
select *
from
(select cx.*,
max(cx.SRC_LOAD_DATE) over (partition by cx.XREF_PERSON_UID, cx.XREF_SYS_ID) maxload
from ETLADM.T_CDM_PERSON_XREF cx
) xd
where xd.SRC_LOAD_DATE = xd.MAXLOAD –only take the records with the most current load date per 2 key columns
and another great use.. for deleting duplicates in a table, but leaving one behind:
DELETE FROM
(SELECT ROWNUMBER() OVER (PARTITION BY ONE, TWO, THREE) AS RN
FROM SESSION.TEST) AS A
WHERE RN > 1;
here, ONE, TWO and THREE are the three columns used as the uniqueness key. ROWNUMBER is a OLAP function that assigns a row number to each resulting duplicate.. and the where clause deletes all but the 1st one.
and another small add-on.. you can include an ORDER BY clause withiin the OVER, to decide the order the ROWNUMBER is being assigned.
for instance..
I wanted to delete all duplicate addresses. Duplicates defined by nearly all columns being the same values. However, in some cases, 1 of the duplicates had a Primary flag marked. In these cases, I wanted to delete the duplicates, leaving the primary flagged one.
DELETE FROM
(SELECT ROWNUMBER() OVER (PARTITION BY ADDR_LINE_1, ADDR_LINE_2,ADDR_LINE_3,ADDR_LINE_4,ADDR_UID,
SRC_SYS_PERSON_ID, START_DT, STNDRDZTN_STAT, STR_DRCTN,
ORDER BY PR_ADDR_IND DESC ) AS RN
FROM OUTBND.ADDRESS) AS A
WHERE RN > 1;
Note the order by acheives this, since the ones marked ‘Y’ will be the first ones, so the rownumber will be 1 on them.
mySQL doesn’t have this unfortunately.. here is a rough equivalent to get a rownumber at least:
SELECT
t.*,
@cur:= IF(id=@id, @cur+1, 1) AS RowNumber,
@id := id
FROM
t CROSS JOIN
(SELECT @id:=(SELECT MIN(id) FROM t), @cur:=0) AS init
ORDER BY
t.id
actually can get rank and dense rank too..
select pr.chld_prty_id, pr.parnt_prty_id,
@row_num:=IF(@prev_col1=pr.chld_prty_id, @row_num+1,1) as row_num,
@rnk:=IF(@prev_col1=pr.chld_prty_id AND @prev_col2=pr.parnt_prty_id AND @prev_col3=pr.dhc_rec_efftv_dt, @rnk, @row_num) as rnk,
@dense:=IF(@prev_col1=pr.chld_prty_id, IF(@prev_col3=pr.dhc_rec_efftv_dt, @dense, @dense+1),1) as dense,
@prev_col1:=pr.chld_prty_id,
@prev_col2:=pr.parnt_prty_id,
@prev_col3:=dhc_rec_efftv_dt
from d_intgn_md_prty_rel pr,
(SELECT @row_num:=1, @dense:=1, @rnk:=1, @prev_col1:=NULL, @prev_col2:=NULL, @prev_col3:=NULL) var
order by pr.chld_prty_id,pr.dhc_rec_efftv_dt, pr.parnt_prty_id;
from http://kennethxu.blogspot.com/2016/04/analytical-function-in-mysql-rownumber.html