CODEWITHSUNDEEP
sql
okorng
okorng

Reputation: 149

How to query to filter unwanted records?

If I have a table with records that match columns n_month and n_year, how can I query to exclude the record with the older date, keeping the record with the latest date? An example table looks like:

n_month n_year      factor      d_expire
===========================================
10      2019        63.2        16-FEB-2018
11      2019        77.6        15-FEB-2018       <-- unwanted
11      2019        82.3        16-FEB-2018
12      2019        58.0        16-FEB-2018

and I want to end up with:

n_month n_year      factor      d_expire
===========================================
10      2019        63.2        16-FEB-2018
11      2019        82.3        16-FEB-2018
12      2019        58.0        16-FEB-2018

The actual table I'm working with is more complex, and I eventually experimented until I had what I think is a very complex query that involves multiple UNION and JOIN elements to end up with the result I want, but I'm hoping that I can replace my initial (large and ugly) solution with a simpler one. SQL is not my strong suit so I'm hoping someone can see the simplest solution to this query.

Thank you.

Upvotes: 0

Views: 172

Answers (2)

Jorge Y.
Jorge Y.

Reputation: 1133

You could rank the rows sorted by date and get rid of the ones with a 1, that would be the ones with older date:

SELECT innerTable.n_month, innerTable.n_year, innerTable.factor, innerTable.d_expire
FROM (
    SELECT YourTable.n_month, YourTable.n_year, YourTable.factor, 
    YourTable.d_expire, RANK() OVER (ORDER BY YourTable.d_expire) AS 
    Numbering
    FROM YourTable
) AS innerTable
WHERE innerTable.Numbering > 1

Edit: if you could have more than one row with the oldest date, but you just one to get rid of one of them, then you change RANK for ROW_NUMBER:

SELECT innerTable.n_month, innerTable.n_year, innerTable.factor, innerTable.d_expire
FROM (
    SELECT YourTable.n_month, YourTable.n_year, YourTable.factor, 
    YourTable.d_expire, ROW_NUMBER() OVER (ORDER BY YourTable.d_expire) AS 
    Numbering
    FROM YourTable
) AS innerTable
WHERE innerTable.Numbering > 1

Upvotes: 1

cyptus
cyptus

Reputation: 3396

you can just put some date calculations in your where condition

WHERE DATEDIFF(STR_TO_DATE('16.2.2018', '%d.%c.%Y'), STR_TO_DATE(d_expire, '%d-%b-%Y')) <= 0

should handle your custom format "DAY-MONTHNAME-YEAR" (see https://www.w3schools.com/sql/func_mysql_str_to_date.asp) DATEDIFF calculates the differnce between your d_expire date and a given date (fixed from a string, or CURDATE() for current date etc.)

Upvotes: 0

Related Questions