ORDER BY MIN date-CodePudding

I need to fetch the number of employees per month, having a first work in a selected period. And I have to display only the month when the employee appears for the first time. My request works fine, but I need to order the result by date. Here is my request:

SELECT TO_CHAR(sub.minStartDate,'mm/YYYY') as date,
       COUNT(DISTINCT sub.id) AS nombre
FROM (
    SELECT MIN(sw.start_date) as minStartDate,
    e.id
    FROM employee e
         INNER JOIN social_work sw ON e.id = sw.employee_id
        GROUP BY e.id
        HAVING MIN(sw.start_date) BETWEEN '2020-01-01' AND '2022-12-31'
    ) sub
GROUP BY date
ORDER BY date

And the result:

date    | nombre
--------------
04/2021 | 2
05/2020 | 1

Excepted output:

date    | nombre
--------------
05/2020 | 1
04/2021 | 2

I've tried to put sub.minStartDate in the ORDER BY clause but then I also have to put it in GROUP BY clause, what gives me this output :

date    | nombre
--------------
05/2020 | 1
04/2021 | 1
04/2021 | 1

And it's not what I want.

CodePudding user response：

You're ordering by date, which is the result of the TO_CHAR() function. The TO_CHAR() function returns a text, so your ORDER BY clause results in an alphanumeric sort.

Since you don't want to ORDER BY sub.minStartDate, you could try changing your format to put the least significant variable of the date (in this case, the month) to the right: TO_CHAR(sub.minStartDate, 'YYYY/mm').

If you can't change your format either, then you'll probably have to resort to grouping and ordering by minStartDate:

SELECT
    TO_CHAR(sub.minStartDate,'mm/YYYY') as date,
    TO_CHAR(sub.minStartDate,'YYYY/mm') sortingDate,
    COUNT(DISTINCT sub.id) AS nombre
FROM
    -- omitted for simplicity
GROUP BY date, sortingDate
ORDER BY sortingDate