I would like to select for only the first DATE of an ACCT group and use the associated NUMBER.

Sample Data:

Acct   Date            Number
1      01-01-2016      5
1      03-06-2016      12
1      07-12-2016      2
2      02-15-2016      4
2      05-16-2016      10
3      04-16-2016      25

Of the data set above i am interested in selecting/using only the following data. My particular interest is using the NUMBER field data in a crosstab.

Acct   Date            Number
1      01-01-2016      5
2      02-15-2016      4
3      04-16-2016      25

I do not know if i can accomplish this with my crystal select statement or with a crystal formula or a select statement in SQL?

Any help is appreciated.

Chuck

It is actually a Date/Time field. I used Date for simplicity of concept. I am interested in the NUMBER associated with the minimum DATETIME for the ACCOUNT.

maybe this...?
select t1.* from table1 t1
join (select Acct,MIN(Date) as MinDate from table1 group by Acct) as t2 on t1.acct=t2.Acct and t1.date = t2.MinDate

Edited by DBlank - 31 Mar 2017 at 4:06am

just joining the table to itself but on the 'second version' of the table doing a group by with the min date per acct. this limits the data rows to what you wanted. The join back gets you the unique 'number' value for that date from that acct.

Edited by DBlank - 31 Mar 2017 at 4:17am

what exactly does 'not happy' mean :)