SELECT DISTINCT on multiple columns along with other columns

• Johnson330

  Old Hand

  Points: 345

  More actions

  March 16, 2023 at 8:24 am

  #4161369

  Hi,

  Can I use distinct on multiple columns WITH other columns ?

  So if my table has Col1, Col2, Col3, Col4,

  Can I say something like this ?

  Select distinct(Col1,Col2,Col3), Col4 FROM myTable

  I'd like to make sure that result contains distinct results of Col1, Col2, & Col3.

  Thank you.

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  March 16, 2023 at 8:35 am

  #4161399

  If you had the following two rows, which value would you select for Col4, and why?

  (1,2,3,4), (1,2,3,5)

  ---

• drew.allen

  SSC Guru

  Points: 77014

  More actions

  March 16, 2023 at 1:14 pm

  #4161472

  There are basically two approaches that you can use ROW_NUMBER() and GROUP BY.

  • In the ROW_NUMBER() method, you would create a row number with a partition on your columns (Col1, Col2, Col3) and define a sort order that would define which row to return.  And then you would filter to only return one row.

    • In this method all of the values come from the same row.

  • In the GROUP BY method, you would group on your columns (Col1, Col2, Col3) and then use aggregates to return values from other columns.

    • In this method, the values may come from different rows.

  If you want tested code, please provide sample data and expected results.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA