Is it possible to apply "Select Distinct" to any column of the query that isn’t in the fir-CodePudding

For example, like the query below:

WITH T1 AS 
(
    SELECT DISTINCT 
        song_name,
        year_rank AS rank,
        group_name
    FROM 
        billboard_top_100_year_end
    WHERE 
        year = 2010
    ORDER BY 
        rank
    LIMIT 10
)
SELECT
    rank,
    group_name,
    song_name
FROM 
    T1
LIMIT 10

I need to put the column song_name on the top because I didn’t know how to use DISTINCT if the column song_name was in third place.

So, after I needed to query again just to obtain the exactly same result but by another order of visualization.

CodePudding user response：

DISTINCT does not apply to a certain column of the result set, but to all. It just eliminates duplicate result rows.

SELECT DISTINCT a, b, c FROM tab;

is the same as

SELECT a, b, c, FROM tab GROUP BY a, b, c;

Perhaps you are looking for the (non-standard!) PostgreSQL extension DISTINCT ON:

SELECT DISTINCT ON (song_name)
       song_name, col2, col2, ...
FROM tab
ORDER BY song_name, col2;

With the ORDER BY, this will give you for each song_name the result with the smallest col2. If you omit the ORDER BY, you will get a random result row for each song_name.