Get unique data

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 22, 2016 at 12:03 pm

  #338786

  Hi,

  Below the sample data

  select 'verify1' union all

  select 'verify1' union all

  select 'verify1' union all

  select 'chance1' union all

  select 'chance1' union all

  select 'chance2' union all

  select 'chance2'

  I need to get unique data and order should not change.

  Expected result :

  select 'verify1' union all

  select 'chance1' union all

  select 'chance2'

  how to get distinct data without the order change. Any sample please

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  December 22, 2016 at 1:20 pm

  #1919418

  Is there any other column in the real source data besides this one? It's not clear how to enforce the order of data as specified from just this column.

• Y.B.

  SSChampion

  Points: 11649

  More actions

  December 22, 2016 at 1:33 pm

  #1919423

  With this single column the only thing you could specify is alphabetical order (or reverse). Does the table have any other columns? Identity? Timestamp?

  EDIT: Here is an example of using another column to maintain an order.

  DECLARE @myTable TABLE (ID INT IDENTITY, Column1 VARCHAR(20))

  INSERT INTO @myTable

  VALUES ('verify1'), ('verify1'), ('verify1'), ('chance1'), ('chance1'), ('chance2'), ('chance2')

  SELECT Column1 FROM (

  SELECT DISTINCT

  MAX (ID) OVER (PARTITION BY Column1) AS ID,

  Column1

  FROM @myTable

  ) x

  ORDER BY ID

  Cheers,

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  December 22, 2016 at 2:00 pm

  #1919427

  KGJ-Dev (12/22/2016)

  ---

  Hi,

  Below the sample data

  select 'verify1' union all

  select 'verify1' union all

  select 'verify1' union all

  select 'chance1' union all

  select 'chance1' union all

  select 'chance2' union all

  select 'chance2'

  I need to get unique data and order should not change.

  Expected result :

  select 'verify1' union all

  select 'chance1' union all

  select 'chance2'

  how to get distinct data without the order change. Any sample please

  The ONLY WAY to guarantee an order is to specify it with an ORDER BY clause.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  December 22, 2016 at 2:29 pm

  #1919432

  This is how i have achieved.

  Thanks everyone who tried to help me

  Declare @test-2 table(Id int identity(1,1) primary key, Value nvarchar(100) )

  insert into @test-2(value)

  Select 'verify1' col union all

  select 'verify1' union all

  select 'verify1' union all

  select 'chance1' union all

  select 'chance1' union all

  select 'chance2' union all

  select 'chance2'

  SELECT A.Value

  FROM (

  SELECT t.Value

  ,id

  ,ROW_NUMBER() OVER (

  PARTITION BY Value ORDER BY t.id

  ) seq

  FROM @test-2 t

  ) A

  WHERE seq = 1

  ORDER BY A.id ASC

• drew.allen

  SSC Guru

  Points: 76988

  More actions

  December 22, 2016 at 2:52 pm

  #1919436

  This seems to perform faster.

  SELECT t.Value

  FROM @test-2 t

  GROUP BY t.Value

  ORDER BY MIN(t.Id)

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA