Unique ID for address field

• mjakachira

  SSC Veteran

  Points: 215

  More actions

  November 1, 2018 at 7:25 am

  #409241

  I am creating an export for some address data but I need to create an ID field that would look like the supplied image. 

  
  Each new group (defined by Account) would begin with a 1 and duplicate if the address is the same (other information will be in the table specifically contact) and count by 1 if the address is different but within the same group. I tried Dense_Rank but I dont think that will work for this purpose.

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  November 1, 2018 at 8:38 am

  #2012053

  Try this:
  
DROP TABLE #TEST

  CREATE TABLE #TEST ( Account Varchar(20), [Address] Varchar(50) )

  INSERT INTO #TEST
VALUES
( 'Acme', '123 Acme Rd' ),
( 'Acme', '123 Acme Rd' ),
( 'Acme', '456 W Cartoon Dr' ),
( 'Funima', '909 E Fun Dr' ),
( 'Funima', '909 E Fun Dr' ),
( 'Funima', '909 E Fun Dr' ),
( 'Funima', '910 E Fun Dr' ),
( 'Funima', '910 E Fun Dr' ),
( 'Funima', '911 E Fun Dr' ),
( 'Funima', '912 E Fun Dr' )

  SELECT *,
    ID = DENSE_RANK() OVER (PARTITION BY Account ORDER BY Account, [Address])
FROM #TEST

• drew.allen

  SSC Guru

  Points: 76949

  More actions

  November 1, 2018 at 9:31 am

  #2012065

  laurie-789651 - Thursday, November 1, 2018 8:38 AM

  Try this:
  
  DROP TABLE #TEST

  CREATE TABLE #TEST ( Account Varchar(20), [Address] Varchar(50) )

  INSERT INTO #TEST
  VALUES
  ( 'Acme', '123 Acme Rd' ),
  ( 'Acme', '123 Acme Rd' ),
  ( 'Acme', '456 W Cartoon Dr' ),
  ( 'Funima', '909 E Fun Dr' ),
  ( 'Funima', '909 E Fun Dr' ),
  ( 'Funima', '909 E Fun Dr' ),
  ( 'Funima', '910 E Fun Dr' ),
  ( 'Funima', '910 E Fun Dr' ),
  ( 'Funima', '911 E Fun Dr' ),
  ( 'Funima', '912 E Fun Dr' )

  SELECT *,
      ID = DENSE_RANK() OVER (PARTITION BY Account ORDER BY Account, [Address])
  FROM #TEST

  Ordering by a partition expression is pointless.  By definition, all rows within a partition have the same exact value for the partitioning expression, so ordering by it is fruitless.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• mjakachira

  SSC Veteran

  Points: 215

  More actions

  November 1, 2018 at 10:17 am

  #2012073

  I think that will work now I understand what I was doing wrong with that command.