Order By and Aliases (As)-CodePudding

I was solving this PostreSQL excercise (in the link you can find the statement and the database diagram) and I came out with this solution:

SELECT mem.firstname || ' ' || mem.surname AS member, fac.name AS facility
FROM 
    cd.members mem
    INNER JOIN cd.bookings book
        ON mem.memid = book.memid
    INNER JOIN cd.facilities fac
        ON book.facid = fac.facid
WHERE
    fac.facid IN (0,1)
ORDER BY mem.firstname, fac.name

Which actually didn't work because I would still get duplicates, so I decided to check out the provided solution:

select distinct mems.firstname || ' ' || mems.surname as member, facs.name as facility
from 
    cd.members mems
    inner join cd.bookings bks
        on mems.memid = bks.memid
    inner join cd.facilities facs
        on bks.facid = facs.facid
where
    facs.name in ('Tennis Court 2','Tennis Court 1')
order by member, facility

With that information, I decided to add the DISTINCT clause to my solution so it looked like this:

SELECT DISTINCT(mem.firstname || ' ' || mem.surname) AS member, fac.name AS facility
FROM 
    cd.members mem
    INNER JOIN cd.bookings book
        ON mem.memid = book.memid
    INNER JOIN cd.facilities fac
        ON book.facid = fac.facid
WHERE
    fac.facid IN (0,1)
ORDER BY mem.firstname, fac.name

However, I got the following error

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 313

Given that, as you can see, the provided solution is not very different from my second take on the problem except for the last line: while I ask SQL to order by the columns referencing their tables (mem.firstname, fac.name), the solution only references their aliases (member, facility), I have some questions:

How is that the ORDER BY clause works different when I reference the tables vs when I only use the aliases? I thought that using columns' aliases didn't work on queries as table aliases did.
How is the DISTINCT clause working in each case?

CodePudding user response：

You ORDER BY mem.firstname, while the solution uses member. What is important here is that, member not only contains that firstname, but actually that exact thing the error message hints at:

ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list

Interpretation: When selecting a distinct value, all expressions in your GROUP BY must be distinct.

So, the reason for the error, is because you tell it to order by only the first name, and that attribute must be what you said to be DISTINCT in the SELECT clause. But it isn't, it is partially there, but there's also: ' ' || mems.surname