How to apply a LEFT() or TRUNCATE to this SELECT query which uses a DISTINCT?-CodePudding

How do I apply a trunc or left to this query?

My current Query;

SELECT DISTINCT processname
FROM table
WHERE status = 'Aborted' 
    AND logdate > 1671981354000 
GROUP BY processname
ORDER BY processname ASC

I've tried the following;

SELECT DISTINCT LEFT(processname, 5)
FROM table
WHERE status = 'Aborted' 
    AND logdate > 1671981354000 
GROUP BY processname
ORDER BY processname ASC

However this results in the following error code;

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

I am not quite sure how I would fix this query, do any of you folks have an idea? Thank you very much!

CodePudding user response：

Reading the error, you can see that the ORDER BY clause must match the SELECT clause. You are altering the processname column in the SELECT clause but not carrying that alteration to the ORDER BY thus they are not the same.

Remember that the ORDER BY clause is executed AFTER the SELECT clause, thus you can reference aliased columns in the ORDER BY clause:

SELECT DISTINCT LEFT(a.processname, 5) AS processname_alias
FROM (SELECT '123456789' AS processname) a
ORDER BY processname_alias ASC

This is effectively the same as:

SELECT DISTINCT LEFT(a.processname, 5) AS processname_alias
FROM (SELECT '123456789' AS processname) a
ORDER BY LEFT(a.processname, 5) ASC

I also removed your GROUP BY because it is not needed.

Final SQL:

SELECT DISTINCT LEFT(processname, 5) AS processname
FROM table
WHERE status = 'Aborted' 
    AND logdate > 1671981354000 
ORDER BY processname

CodePudding user response：

You can use a subquery to first select the distinct processname, and then in the outer query, apply the LEFT() function to the result of the subquery.

SELECT DISTINCT LEFT(processname, 5)
FROM (
    SELECT DISTINCT processname
    FROM table
    WHERE status = 'Aborted' 
        AND logdate > 1671981354000 
    GROUP BY processname
) subquery
ORDER BY processname ASC

The subquery retrieves the distinct processname values that meet the specified conditions, and the outer query applies the LEFT() function to the result of the subquery. The subquery alias is used to give a name to the subquery so that it can be referred to in the outer query.

Alternatively, you can use a common table expression (CTE) to accomplish the same thing. A CTE is similar to a subquery but is defined before the main query and can be referred to multiple times in the main query. Here's an example of how to use CTE in the above query:

WITH cte AS (
    SELECT DISTINCT processname
    FROM table
    WHERE status = 'Aborted' 
        AND logdate > 1671981354000 
    GROUP BY processname
) 
SELECT DISTINCT LEFT(processname, 5)
FROM cte
ORDER BY processname ASC;