Select out rows into a distinct result

• tendo

  SSC Veteran

  Points: 271

  More actions

  August 31, 2004 at 12:15 am

  #152280

  Hi can somebody please help?

  I have a table with 3 columns 'Series' with nvarchar datatype, 'SeriesID' with datetime datatype & SolutionID also with datatime datatype. I want to select DISTINCT nvarchar values from the series column whose respective SolutionID match @SolutionID & incase each selection yields more than 1 option for each SolutionID, the record with the highest SeriesID gets selected. The statement below failed

  --------------------------------------------------------

  SELECT DISTINCT Series WITH MAX(SeriesID)

  FROM         Series

  WHERE     (SolutionID = @SolutionID)

  GROUP BY Series

  --------------------------------------------------------

  Please help thx in advance

• Stephen Norrod

  SSC Veteran

  Points: 280

  More actions

  August 31, 2004 at 8:52 am

  #521239

   

  SELECT DISTINCT Series

  FROM Series T

       INNER JOIN (

  SELECT SolutionID, MAX(SeriesID)

  FROM Series

  WHERE SolutionID = @SolutionID)  B ON B.SolutionID  = T.SolutionID  AND B.SeriesID = T.SeriesID

   

  Give this a try. If you still have problems send me the table definition and a small sample of the data in the form of insert statements.

  Cheers, Stephen