Unique data in report

• Randy Bryan

  Newbie

  Points: 3

  More actions

  April 12, 2007 at 7:11 am

  #175852

  I have a SQL 2000, 7 database with the following fields

  TagName Char

  Comment Char

  Date Date/Time

  Operator Char

  The TagName field may have one tagname entered several times.

  I want to build a crystal report which will:

  I want to pull data from this database on an ongoing basis.

  List all fields.

  Index on Date, Tag.

  List records with ONLY the latest, unique TagName.

  Not sure how to do this?

  Thanks,

  Randy

• Ken McKelvey

  SSCoach

  Points: 18942

  More actions

  April 12, 2007 at 8:11 am

  #700084

  Maybe:

  SELECT T1.*

  FROM YourTable T1

      JOIN (

          SELECT T2.TagName, MAX(T2.[Date]) AS [Date]

          FROM YourTable T2

          GROUP BY T2.TagName

      ) D ON T1.TagName = D.TagName

              AND T1.[Date] = D.[Date]

  ORDER BY T1.[Date] -- DESC