How to get the first row with dupes and without using the row_number function?

Question

I am running some SQL on a ubuntu machine, and it looks like the Windows Functions are not available. I'm trying to get this to run.

SELECT date, deb, req
FROM   (SELECT  date, deb, req,
               Row_number() OVER(Partition BY date, deb, req  ORDER BY date, deb, req ) rn
        FROM   fsm) t
WHERE  rn = 1
And data_date >= '01/01/2020'

It seems like that doesn't work, so I tested a couple other ideas and couldn't get it running. I am using MySQL. Any thoughts?

Answer 1

Presumably, you are running a 5.x version of MySQL, where window functions are not available.

Your query makes little sense, because it has the same columns in the partition and order by clause of the window function; let me assume that you want one record per date and deb, that has the smallest req.

If so, you can implement the same logic by filtering your dataset with a correlated subquery:

select date, deb, req
from fsm f
where date >= '2020-01-01' and f.req = (
    select min(f1.req)
    from fsm f1
    where f1.date = f.date and f1.deb = f.deb
)

This is equivalent as long as there no duplicate (date, deb, req) in the table; in that case, the subquery solution returns all duplicates, while the window functions returns just one of them. If that's a problem, you can still work around this for this particular query with select distinct in the outer query.

Notes that the date literal in your original query is malformed; MySQL wants date in format YYYY-MM-DD