How to get distinct records from table

• Gaja

  Right there with Babe

  Points: 768

  More actions

  April 14, 2014 at 3:00 am

  #396597

  Suppose

  I have 2 table

  1)Main

  2)History

  Main table maintain all the records having columns MAIN_SKU,DEDUCTIBLE_AMT,model_id,catagory,ModifiedDate

  IF DEDUCTIBLE_AMT is changes it will place entry in history table ,columns are same with history_id

  i want to display distinct main_sku from history table(all columns) with last DEDUCTIBLE_AMT changed from history table

  table structure

  main table

  MAIN_SKUDEDUCTIBLE_amtmodel_idcatagory

  1100100phone

  2150101phone

  3200109smartphone

  4100202smartphone

  History table

  History_idMAIN_SKUDEDUCTIBLE_amtmodel_idcatagoryModifiedDate

  11150100phone4/14/2014

  21200101phone4/13/2014

  34109202smartphone4/14/2014

  44101202smartphone4/13/2014

  52200101phone4/13/2014

  63100109smartphone4/12/2014

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  April 14, 2014 at 3:12 am

  #1705676

  Can you please post the DDLs and expected o/p ? That way it'll be easy to help you...

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 14, 2014 at 3:16 am

  #1705680

  WITH RowNos (MAIN_SKU, DEDUCTIBLE_amt, RowNo) AS (

  SELECT MAIN_SKU, DEDUCTIBLE_amt, ROW_NUMBER() OVER (PARTITION BY MAIN_SKU, ORDER BY History_id DESC)

  FROM [History table]

  )

  SELECT MAIN_SKU, DEDUCTIBLE_amt

  FROM RowNos

  WHERE RowNo = 1

  John

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  April 14, 2014 at 3:56 am

  #1705701

  Nice 😀