1

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.

Ehsan Sajjad
  • 61,834
  • 16
  • 105
  • 160
JamieB
  • 247
  • 4
  • 8
  • 17
  • Remove the 2 columns which have different data if that does't matter at the moment and then `Distinct` should give you desired result. – Deepshikha Dec 01 '14 at 10:47
  • That would work, but I still want the show the other 2 columns as the data is important to show, but I want to sort / group / distinct to only base its actions on the one column (train_id) – JamieB Dec 01 '14 at 11:14

1 Answers1

2

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
Dave Ward
  • 97
  • 1
  • 3
  • 12
  • That worked, but I still want to show some of the data that changes from time to time, so currently if any of the above columns have different data the entire row is saved...I want it to ignore that and only group by train_id but when I do that I get an error saying it cant. – JamieB Dec 01 '14 at 11:13
  • It's not clear, what you are trying to archive... If you want to ignore different data, then you have to exlude it from the result list. For example: if you want different data in 'origin' and 'arr' to be ignored, don't write these columns in the SELECT and GROUP BY block. – iris Dec 01 '14 at 11:49
  • Are all of the datatypes string variations? if not then you either need to exclude the numerics or sum them. – Dave Ward Dec 01 '14 at 11:58
  • Sorry for the confusion..I want all the data within the select column to be shown, but it to only remove based on the duplicated within Train_id column even if the data within the other columns on that row are different... – JamieB Dec 01 '14 at 14:37