How can I display the entire table with applying DISTINCT to only one column ?

CodePudding user response：

You can do it for example, look at this query :

SELECT DISTINCT course_id, exercise_id FROM bugs;

click here https://discuss.codecademy.com/t/can-we-apply-distinct-to-a-select-query-with-multiple-columns/349723

CodePudding user response：

If two rows have the same value in the column you want to distinct on, you must resolve this somehow - which row should be returned? In PostgreSQL, you can use distinct on, which returns the first row as defined by the order by clause. Let's say you have a table of transactions and want only the newest one per account. It would look like

select distinct on (date)
    account,
    date,
    amount
from transactions
order by account, date desc

If two transactions for the same account have the same date, you will only get the latest one. If you do not specify any order by clause, you will get a random one - or rather, whichever one happens to first in the ordering. This is only valid for PostgreSQL however.

In general SQL, this can be accomplished by using a window function. The basic idea is still the exact same - you want the transactions in groups for each account ordered by the descending date such that you can pick up the latest one.

select account, latest_date as date, amount from (
    select 
        account,
        max(date) over (partition by account order by date desc) as latest_date,
        amount
        from transactions
    ) transactions_per_account_latest

I have done some aliasing and naming of things in the above queries to try and make it clearer what is going on - this is not necessarily how I would format or phrase these queries.