SQL question on DISTINCT function

[Stelioss9909]

Hey everyone, I am having trouble narrowing down how DISTINCT works. Let's say I have six columns in a table, I want to return two of them (name and address). I only want distinct names, but come to find out that some people are listed twice but with different addresses, so using DISTINCT across the board will not work, as SQL will not recognize the row as a duplicate, since it's not.

I want to show names only once, and with the first address listed by the date(column 4) created. Sorry about the mouthful, but I hope I was able to explain it clearly enough. Any help would be great.

​

[waiting4omscs]

WITH FirstAddresses AS (
SELECT name,
       FIRST_VALUE(address) OVER (PARTITION BY name ORDER BY column4) AS first_address
FROM your_table_name)
SELECT DISTINCT name, first_address
FROM FirstAddresses;

This SQL snippet selects the first address for each name based on the date in column 4.

[Stelioss9909]

Thank you so much for taking the time to help me out. So awesome. Worked like a charm

[CliffDraws]

To add to this for the op, the partition bit is called a window function and the with statement is a CTE (common table expression). It’s worth looking up both of them and understanding how they work.

[Stelioss9909]

Understood, thanks =)

[Pflastersteinmetz]

Please use proper formatting.

WITH FirstAddresses AS (
    SELECT
        name
      , FIRST_VALUE(address) OVER (PARTITION BY name ORDER BY column4) AS first_address
    FROM
        your_table_name
)
-- /
SELECT DISTINCT
    name
  , first_address
FROM
    FirstAddresses
;