Select one row with distinct value of one column

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 = 1ORDER 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.

Hi something like this could work:

select id,    name,     start_date,    end_date from (select id,    name,     start_date,    end_date, ROW_NUMBER()   OVER (PARTITION BY merchant_id ORDER BY merchant_id) AS rnum from your_table   where start_date<=trunc(sysdate) and end_date>=trunc(sysdate))   where rnum=1order by start_date desc

if you provide a simple test script for creating and populating the table I can give you the correct query.

Depending on what type of sql you are running (mysql, sql server, etc) there will be alternate answers.

For example, in sql server you could say "Assign each row a number, starting at 1 for each merchant", and always pick number 1.

In generic sql, you have to be a little less direct. It appears your id column is guaranteed to be unique? If so one way of phrasing the question is "get records inside the date range WHERE there is no higher id in the same date range for the same merchant"

Do you know already how to filter by > and < today? I think the answer to how to do that is specific to what type of sql you're using:

So try:

SELECT * from myFavoriteTable  WHERE today() < end_date and today > start_date -- for this date criteria use whatever works  AND NOT EXISTS ( SELECT * from myFavoriteTable as TooLow      WHERE today() < end_date and today() > start_date -- as above      AND TooLow.Merchant = myFavoriteTable.merchant      AND TooLow.id > myFavoriteTable.id)