Title: SQL First transaction of each day
Tags: sql;date;ms-access;greatest-n-per-group
Question: I have a table that contains data from a number of transactions, and I been trying to obtain the earliest record per day, per client, adjusting other solutions I have seen in this website (such as this one), but they have not worked for me.

The table transactions is


Time                   Id   Client     Price       Quantity
1/2/2013 09:33:20 AM    1   Albert     100.00       5,300
1/2/2013 10:34:20 AM    2   Albert     100.90       4,800
1/2/2013 10:34:20 AM    3   Lewis       80.00      25,987
1/2/2013 11:35:23 AM    4   Benson     251.00         700
1/2/2013 14:36:20 AM    5   Albert     100.00       2,250
1/2/2013 15:31:12 AM    6   Albert      99.50       1,340
1/3/2013 09:33:20 AM    7   Benson     250.00         900
1/3/2013 15:13:12 AM    8   Benson     250.00         800
1/3/2013 16:03:55 AM    9   Lewis       80.00      18,890
1/4/2013 09:01:01 AM   10   Albert     101.00       1,190
1/4/2013 09:01:01 AM   11   Albert     100.99      98,890
1/4/2013 09:01:01 AM   12   Lewis       80.98       6,890
1/4/2013 10:51:00 AM   13   Benson     279.18         190
1/4/2013 10:51:00 AM   14   Albert      99.36      78,053
...


The Id is unique, and is also sorted chronologically by definition. The Time is not unique, meaning there could be 2 transactions that happen exactly at the same time.

The sql query would need to would pull out the first transaction each client did, per day, together with the price and the quantity, something like:


Date         Client    Price    Quantity
1/2/2013     Albert    100.00     5,300
1/2/2013     Benson    251.00       700
1/2/2013     Lewis      80.00    25,987
1/3/2013     Benson    250.00       900
1/3/2013     Lewis      80.00    18,890
1/4/2013     Albert    101.00     1,190
1/4/2013     Lewis      80.98     6,890
1/4/2013     Benson    279.18       190



Can anyone help me on how to do it in SQL?

Comment: The database is in MS Access, but I'm likely going to do the query in MySQL

Comment: What database are you using?

Here is the accepted answer: You don't specify the database.  So here is a general approach.  The idea will work in most databases, but some of the functions are different.

```select cast(t.time as date) as "date", t.*
from transactions t
where not exists (select 1
                  from transactions t2
                  where cast(t2.time as date) = cast(t.time as date) and
                        t2.client = t.client and
                        t2.id < t.id
                 );
```

The expression for getting a date from a time varies.  In some databases this might be ```date(time)``` (MySQL) or ```trunc(time)``` (Oracle) or something else.

EDIT:

In Access, this would be:

```select CDATE(t.time) as [date], t.*
from transactions t
where not exists (select 1
                  from transactions t2
                  where CDATE(t2.time) = CDATE(t.time) and
                        t2.client = t.client and
                        t2.id < t.id
                 );
```

In MySQL:

```select date(t.time) as "date", t.*
from transactions t
where not exists (select 1
                  from transactions t2
                  where date(t2.time) = date(t.time) and
                        t2.client = t.client and
                        t2.id < t.id
                 );
```

Comment for this answer: Thanks, for some reason the CDATE did not worked in MS Access, but in MySQL went fine..
Comment for this answer: This doesn't partition by client as well, though.

Here is another answer: In SQL Server, something like this should work:

```select cast(Time as date), Client, Price, Quantity
from (
 select *, row_number() 
           over (partition by Client, cast(Time as Date) order by Id) [rn] 
 from transactions
) x where x.rn = 1
```

Here's a sqlfiddle: http://sqlfiddle.com/#!6/0725d/1


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 2, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:02/26/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><date><ms-access><greatest-n-per-group>', 'Title': 'SQL First transaction of each day', 'ViewCount': 3288, 'id': 22050631, 'user_AboutMe': '', 'user_AccountId': 4088445.0, 'user_DisplayName': 'francisco socías', 'user_Reputation': 39.0, 'user_Views': 6.0, 'user_id': 3357137.0}}