This is the data
id name start_date end_date merchant_id
===================================================
111 name1 25-nov-11 31-jan-12 9999
222 name2 23-nov-11 25-dec-11 9999
333 name3 25-nov-11 25-nov-12 9999
444 name4 20-nov-11 20-nov-11 9999
555 name5 25-nov-11 25-dec-11 8888
666 name6 19-oct-11 20-nov-11 8888
777 name7 20-nov-11 20-jun-12 8888
I need to get all rows sorted by start_date
(desc) where start_date<=today
and end_date >=today
but limit 1 per merchant_id
. Which means if the query finds more than one row, then just return the first one.
Test script
CREATE TABLE DEAL
(
ID VARCHAR2(40 BYTE) NOT NULL,
NAME VARCHAR2(255 BYTE),
START_DATE DATE,
END_DATE DATE,
MERCHANT_ID NUMBER(22),
CONSTRAINT DEAL PRIMARY KEY (ID)
);
INSERT ALL
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('111','name1','25-nov-11','31-jan-12','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('222','name2','23-nov-11','25-dec-11','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('333','name3','25-nov-11','25-nov-12','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('444','name4','20-nov-11','20-nov-11','9999')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('555','name5','25-nov-11','25-dec-11','8888')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('666','name6','19-oct-11','20-nov-11','8888')
INTO DEAL (ID,NAME,START_DATE,END_DATE,MERCHANT_ID) VALUES ('777','name7','20-nov-11','20-jun-12','8888')
SELECT * FROM dual;
running this:
SELECT DISTINCT merchant_id, id, name, start_date, end_date FROM deal WHERE start_date <= trunc(sysdate) AND end_date >= trunc(sysdate) ORDER BY start_date DESC;
does not return the desired result as the same merchant id is returned multiple times:
MERCHANT_ID ID NAME START_DATE END_DATE
===========================================
9999 111 name1 25-NOV-11 31-JAN-12
9999 333 name3 25-NOV-11 25-NOV-12
8888 555 name5 25-NOV-11 25-DEC-11
9999 222 name2 23-NOV-11 25-DEC-11
8888 777 name7 20-NOV-11 20-JUN-12
The following Oracle query should do what you need:
SELECT *
FROM (
SELECT TABLE1.*, DENSE_RANK() OVER(PARTITION BY MERCHANT_ID ORDER BY START_DATE DESC, ID) R
FROM TABLE1
WHERE SYSDATE BETWEEN START_DATE AND END_DATE
)
WHERE R = 1
ORDER BY START_DATE DESC
Essentially, it first filters rows by date and then ignores all rows but the first one that share the same MERCHANT_ID.
Please note that the meaning of "first" is defined relative to descending START_DATE order. It two rows have same START_DATE, then the "dispute" is resolved using ID order.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With