Visual Web Developer + SQL to show only unique rows

Question

Trying to only show unique data from one column (train_id) and limited the date to today but cant seem to get it to work.

Tried Group by and SELECT DISTINCT but cant seem to get Visual Web Dev to accept it... Details below:

Using SQL Server 2008 and Visual Web Dev 2010 Express and connecting via SQLdatasource...my code is as follows:

SELECT train_id, 
       path, 
       dep, 
       origin, 
       arr, 
       tsc, 
       depot_code, 
       diag_no FROM train_summary 
       WHERE (
                date BETWEEN CAST(GETDATE() AS date) 
                     AND CAST(DATEADD(d, 1, GETDATE()) AS date)
            ) 
      ORDER BY arr

This code works, but I each row comes with between 2-8 same data rows which data changing in a 2 columns which doesn't matter at this time..so I would to eliminate the duplicates on 'train_id'

Any Ideas? I'm just learning so I'm googling mostly but my limited knowledge doesn't really help with the wording so sorry if this is an easy one.

Answer 1

if all your records are strings as they would appear to be then you should just be able to group to limit to unique values

      SELECT 
      train_id
      , path, dep
      , origin
      , arr
      , tsc 
      , depot_code
      , diag_no 
      FROM train_summary 
      WHERE (date BETWEEN CAST(GETDATE() AS date) AND CAST(DATEADD(d, 1, GETDATE()) AS date)) 
      GROUP BY
      train_id
      , path, dep
      , origin
      , arr
      , tsc 
      , depot_code
      , diag_no 
      ORDER BY arr