Choose a non-usercolumn and a minimum date value for each field in usercolumn

Question

I have some data similar to this, but over 1,500,000 entries and over 700 users:

usercolumn , datecolumn\ a1 , 1998/2/11\ a2 , 1998/3/11\ a1 , 1998/2/15\ a4 , 1998/4/14\ a3 , 1999/1/15\ a2 , 1998/11/12\ a2 , 1999/2/11\ a3 , 2000/2/9\ a1 , 1998/6/5\ a3 , 1998/7/7\ a1 , 1998/3/11\ a5 , 1998/3/18\ a2 , 1998/2/8\ a1 , 1998/12/11\ a4 , 1998/12/1\ a5 , 1998/2/11\ .... 

I would like to have separate data from usercolumn and a minimum date value for each user, for example:

 usercolumn , datecolumn \ a1 , 1998/2/11\ a2 , 1998/2/8\ a3 , 1998/7/7\ a4 , 1998/4/14\ a5 , 1998/2/11\ .... 

please help me write an SQL command to do this for an oledb adapter in C #, thanks.

Answer 1

 SELECT usercolumn, MIN(datecolumn) FROM tablename GROUP BY usercolumn; 

Note that if you need other columns, they must either appear in the GROUP BY clause or be row-constant. Otherwise, the result will be non-deterministic.

Answer 2

This will work for SQLServer 2008 and DB2:

 with temp as ( select *, row_number() over (partition by usercolumn order by datecolumn) as rownum from table) select * from temp where rownum = 1 

It will give the correct results, even if you need to include multiple columns in select.

Answer 3

Something like this should tick

  SELECT usercolumn , MIN(datecolumn) FROM YouTable GROUP BY usercolumn , MIN(datecolumn) 

Answer 4

If you have more than just these two columns, the best SQL code depends on which server you have on the other end of this OleDB adapter, but here is something that will work well with many (alas, not all!) possible servers:

 SELECT t.* FROM thetable t LEFT JOIN thetable taux ON(t.usercolumn=taux.usercolumn AND t.datecolumn>taux.datecolumn) WHERE taux.usecolumn IS NULL 

which you could read as "emit these table rows so that there is no other table row with the same user and a strictly lesser date." If the minimum date for this user can occur several times, this will give so many lines for this user - if this is a problem for you, there are solutions for this ... but I will wait until you clarify your question more before I work above this! -)

Answer 5

you can try the following:

 SELECT DISTINCT a.username, a.date FROM tablename AS a INNER JOIN tablename AS b ON(a.username = b.username AND a.date < b.date) 

As for C #, can't help you there

Answer 6

SELECT DISTINCT USERNAME, DATE FROM TABLENAME AS A WHERE A.DATE = (SELECT MIN (DATE) FROM TABLENAME WHERE USERNAME = A.USERNAME)