Unable to get only first occurrence of each job

Question

I am trying to query some jobs from a repo, however I only need the job with the latest start time. I have tried using ROW_NUMBER for this and select only row number 1 for each job, however it doesn't seem to fall through:

SELECT a.jobname||','||a.projectname||','||a.startdate||','||a.enddate||','||
  ROW_NUMBER() OVER ( PARTITION BY a.jobname ORDER BY a.startdate DESC ) AS "rowID"
FROM taskhistory a
WHERE a.jobname IS NOT NULL AND a.startdate >= (SYSDATE-1))LIMIT 1 AND rowID = 1;


ERROR at line 7:
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER

Can I please ask for some assistance?

Answer 1

You have aliased your concatenated string "rowID" which is a mistake because it clashes with the Oracle keyword rowid. This is a special datatype, which allows us to identify table rows by their physical location. Find out more.

When you reference the column alias you omitted the fouble quotes. Oracle therefore interprets it as the keyword, rowid, and expects an expression which can be converted to the ROWID datatype.

Double-quoted identifiers are always a bad idea. Avoid them unless truly necessary.

Fixing the column alias will reveal the logic bug in your code. You are concatenating a whole slew of columns together, including the ROW_NUMBER() function, and calling that string "rowID". Clearly that string is never going to equal one, so this will filter out all rows:

and "rowID" = 1

Also LIMIT is not valid in Oracle.

What you need to do is use a sub-query, like this

SELECT a.jobname||','
       ||a.projectname||','
       ||a.startdate||','
       ||a.enddate||','
       ||to_char(a.rn) as "rowID"
FROM (
    SELECT jobname
            , projectname
            , startdatem 
            , enddate, 
            , ROW_NUMBER() OVER ( PARTITION BY jobname 
                                  ORDER BY startdate DESC ) AS RN
    FROM taskhistory 
    WHERE jobname IS NOT NULL 
    AND a.startdate >= (SYSDATE-1)
    ) a
where a.RN = 1;

Concatenating the projection like that seems an odd thing to do but I don't understand your business requirements.