select rows with condition of date presto-CodePudding

I try to select by hour the number of impression for a particular day :

I try with this code :

SELECT 
    date_trunc('hour', CAST(date_time AS timestamp)) date_time,
    COUNT(impression_id) AS count_impression_id
FROM
    parquet_db.imp_pixel
WHERE
    date_time = '2022-07-27' 
LIMIT 100
GROUP BY 1

But I got this error when I add the "where" clause :

line 5:1: mismatched input 'group'. Expecting:

Can you help me to fix it? thanks

CodePudding user response：

LIMIT usually comes last in a SQL query. Also, you should not be using LIMIT without ORDER BY. Use this version:

SELECT DATE_TRUNC('hour', CAST(date_time AS timestamp)) date_time,
       COUNT(impression_id) AS count_impression_id
FROM parquet_db.imp_pixel
WHERE CAST(date_time AS date) = '2022-07-27'
GROUP BY 1
ORDER BY <something>
LIMIT 100;

Note that the ORDER BY clause determines which 100 records you get in the result set. Your current (intended) query lets Presto decide on its own which 100 records get returned.