selecting dist records

• Jeffrey Kingston

  SSC Rookie

  Points: 31

  More actions

  February 19, 2008 at 1:12 pm

  #183133

  want to select distinct product using maximum value of tran date

  data like:

  Table Product Table 2

  parent product sub product date product descrip

  1 11 01/01/03 1 this is it

  1 45 01/15/04 4 abc

  1 47 09/30/05

  1 62 06/24/06

  1 89 02/04/08

  4 23 02/27/04

  4 09 02/13/05

  The data that I want to retrieve is

  distinct parent_product, sub product, descrip using the max value of date.

  desired results

  1,89,"This Is It'

  4,09,"abc"

  Can anyone give me an idea how to do this?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  February 19, 2008 at 1:19 pm

  #779889

  Select

  parent_product,

  product,

  sub_product

  From

  products P Join

  (select parent_product, product, sub_product, Max(tran_date) as tran_date from products group by parent_product, product, sub_product) AS MP ON

  P.parent_product = MP.parent_product And

  P.product = MP.product And

  P.sub_product = MP.sub_product And

  P.tran_date = MP.tran_date

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question