Select one row with distinct value of one column

Question

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

Answer 1

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.