RANK() Tiebreaker?

Analytics
N/A

RANK() Tiebreaker?

CREATE VOLATILE TABLE MYTBL  
 (
  PERIOD_START DATE NOT NULL,
  PERIOD_END DATE NOT NULL,
  EMPID INT NOT NULL,
  LOCID INT NOT NULL,
  LOCNME VARCHAR(50) NOT NULL,
  LOC_VST_DT DATE NOT NULL
 ) ON COMMIT PRESERVE ROWS;

INSERT INTO MYTBL VALUES ('2015-01-01', '2015-01-31', 12345, 98765, 'PLACE1', '2015-01-10');
INSERT INTO MYTBL VALUES ('2015-02-01', '2015-02-28', 12345, 98765, 'PLACE1', '2015-01-10');
INSERT INTO MYTBL VALUES ('2015-02-01', '2015-02-28', 12345, 98765, 'PLACE1', '2015-02-04');
INSERT INTO MYTBL VALUES ('2015-02-01', '2015-02-28', 12345, 65432, 'PLACE2', '2015-02-13');
INSERT INTO MYTBL VALUES ('2015-03-01', '2015-03-31', 12345, 98765, 'PLACE1', '2015-01-10');
INSERT INTO MYTBL VALUES ('2015-03-01', '2015-03-31', 12345, 98765, 'PLACE1', '2015-02-04');
INSERT INTO MYTBL VALUES ('2015-03-01', '2015-03-31', 12345, 65432, 'PLACE2', '2015-02-13');
INSERT INTO MYTBL VALUES ('2015-03-01', '2015-03-31', 12345, 65432, 'PLACE2', '2015-03-01');
INSERT INTO MYTBL VALUES ('2015-04-01', '2015-04-30', 12345, 98765, 'PLACE1', '2015-02-04');
INSERT INTO MYTBL VALUES ('2015-04-01', '2015-04-30', 12345, 65432, 'PLACE2', '2015-02-13');
INSERT INTO MYTBL VALUES ('2015-04-01', '2015-04-30', 12345, 65432, 'PLACE2', '2015-04-28');

SEL
 T1.PERIOD_START,
 T1.PERIOD_END,
 T1.EMPID,
 T1.LOCID,
 RANK() OVER (PARTITION BY T1.PERIOD_START, T1.EMPID
  ORDER BY T1.PERIOD_START, T1.CNT DESC)
FROM
 (
  SEL
   A.PERIOD_START,
   A.PERIOD_END,
   A.EMPID,
   A.LOCID,
   COUNT(*) AS CNT
  FROM
   MYTBL A
  GROUP BY 1,2,3,4
 ) T1;

I am aggregating data by rolling three months for each reporting period. When I rank the above dataset I get ties for one month. How would I use the most recent LOC_VST_DT to break the tie? Thank you for the help!

Tags (1)
1 REPLY
N/A

Re: RANK() Tiebreaker?

Add a MAX(LOC_VST_DT) to the RANK:

SEL
T1.PERIOD_START,
T1.PERIOD_END,
T1.EMPID,
T1.LOCID,
RANK() OVER (PARTITION BY T1.PERIOD_START, T1.EMPID ORDER BY T1.PERIOD_START, T1.CNT, MaxDT DESC)
FROM
(
SEL
A.PERIOD_START,
A.PERIOD_END,
A.EMPID,
A.LOCID,
MAX(LOC_VST_DT) AS MaxDt,
COUNT(*) AS CNT
FROM
MYTBL A
GROUP BY 1,2,3,4
) T1;