Postgresql – ORDER BY on field that isn’t in a SELECT using DISTINCT or GROUP BY to get distinct records

postgresql

Migrated from MySQL where this query worked:

SELECT DISTINCT
    code,
    title,
    country
FROM
    data_a
ORDER BY
    sortorder

Basically, get a list and sort it by a user/company defined sortorder that is not based on code, title, or country.

Now that I'm in Postgres, this query does not work and gets a:

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 12:  sortorder
          ^
SQL state: 42P10
Character: 135

Doing this however returns duplicates because there are additional fields not listed here that make that entire record unique. I just want the unique values from code, title, or country in order of the sortorder.

I tried to do a sub-query thinking I could sort it in the sub-query and bring it into the parent query already sorted, but it just comes over in non particular order… well, maybe it is particular, but it isn't the order we want.

SELECT
    DISTINCT s.*
FROM
    (
        SELECT
            code,
            title,
            country
        FROM
            data_a
        GROUP BY
            code,
            title,
            country,
            sortorder
        ORDER BY
            sortorder
    ) s;

Any suggestions for how to do this?

Best Answer

Actually, did manage to figure it out with:

SELECT
    code,
    title,
    country,
    MIN(sortorder)
FROM
    data_a
GROUP BY
    code,
    title,
    country
ORDER BY
    4