Can I user LOWER and MIN in the same SQL query?-CodePudding

I am getting an issue with my query written, am I able to use a LOWER and a MIN In the same query?

SELECT DISTINCT
    LOWER(id_5) AS "ID"
    ,MIN(visittime) AS "First Date"
FROM table
WHERE (date >= '2022-11-21')
    AND (
        server = 'pets.com' AND page = 'pets:dogs:puppy')

CodePudding user response：

MIN is aggregation function which can be used only with aggregation or as window function or scenarios reducible to this like in queries when only one row is expected:

select max(LOWER(id_5)) AS "ID" -- or any other aggregation  function
   , min(Status) AS "First Date"
from dataset;

which is equivalent to group by true.

Note that DISTINCT works on all values in the select. If you want to find first visit per unique id_5 then just use group by:

SELECT LOWER(id_5) AS "ID"
    , MIN(visittime) AS "First Date"
FROM table
WHERE (date >= '2022-11-21')
    AND (
        server = 'pets.com' AND page = 'pets:dogs:puppy')
group by LOWER(id_5)