Search code examples
c#sql-server-ce

SQL Server CE 3.5 'select distinct' probleme

I have a problem with a query using SQL Server CE 3.5 and c#

Here is what my table look like:

start      | end        | matricul
-----------+------------+---------
29/01/2012 | 29/01/2013 | 22453
29/01/2012 | 29/01/2013 | 22454
29/01/2012 | 29/01/2013 | 22455
29/01/2011 | 29/01/2012 | 22458
29/02/2012 | 29/02/2013 | 22440

This is what I'd like to have:

start      | end        | matricul
-----------+------------+---------
29/01/2012 | 29/01/2013 | 22453
29/01/2011 | 29/01/2012 | 22458
29/02/2012 | 29/02/2013 | 22440

When I execute this query :

string query = "SELECT DISTINCT(start) FROM mytable "

I get the same result (same as the first table).

I try using the group by close like this: 

string query = "SELECT COUNT(start),end,matricul FROM mytable GROUP BY start";

but it gave me an error.

Please any help would be appreciated

Solution

  • If you group only by "start", you cannot return "end" and "matricul" - as there could be multiple rows for each. You can return aggrgates of those (the min or max, for example) - however, I expect you just need:

    select distinct start, end, matricul
from mytable

    You can also write this as a group:

    select start, end, matricul
from mytable
group by start, end, matricul

    Or maybe:

    select start, end, sum(matricul)
from mytable
group by start, end

    Depending on wht "matricul" means :p