# SQL - selecting first and last values in an time+date ordered list. **URL:** \ **Category:** Factual Questions\ **Created:** [June 14, 2008, 9:58pm UTC](https://boards.straightdope.com/t/sql-selecting-first-and-last-values-in-an-time-date-ordered-list/452954 "2008-06-14T21:58:44Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 14, 2008, 9:58pm UTC](https://boards.straightdope.com/t/sql-selecting-first-and-last-values-in-an-time-date-ordered-list/452954/1 "2008-06-14T21:58:44Z")
Given a table of transactions with a common date. I want to get a sum of some fields, and the first value and last value of one other field. I want a sum of the credit field and the debit field (to show total added to the account and total taken away) In the same results I want the balance at the beginning of the day, and the balance at the end of the day… this is basically the first and last values of ‘balance\_forward’ this seems simple but I am having the hardest time trying to get this. ---
**Author:** ![chrisk](https://avatars.discourse-cdn.com/v4/letter/c/6de8d8/32.png) [@chrisk](https://boards.straightdope.com/u/chrisk)\ **Post date:** [June 15, 2008, 12:05am UTC](https://boards.straightdope.com/t/sql-selecting-first-and-last-values-in-an-time-date-ordered-list/452954/2 "2008-06-15T00:05:38Z")
[QUOTE=Lobsang] Given a table of transactions with a common date. I want to get a sum of some fields, and the first value and last value of one other field. I want a sum of the credit field and the debit field (to show total added to the account and total taken away) In the same results I want the balance at the beginning of the day, and the balance at the end of the day… this is basically the first and last values of ‘balance\_forward’ this seems simple but I am having the hardest time trying to get this. [/QUOTE] Okay, this time, rather than trying to come up with a query and handing it to you, I’ll start asking questions and making vague suggestions. First, you’ll need some way to group by day - this might be easy or harder depending on how the date and time are encoded. If there’s a field that’s just date with no transaction-time component you can just group by this, otherwise you’ll need to come up with an expression or a function that will return the same value for each day. I have a regular function for removing the time component of an MSSQL datetime that I can probably dig up and post if it would be handy. Now, within the result set for any particular day, is there a field that you can do min() and max() of to uniquely get the ‘first’ and ‘last’ record for the day? An identity primary key will probably be best. The transaction\_time field might have more than one hit at the same second. Then you can run the select min(unique\_field) from table group by day as a subquery, and join from there to the full results based on unique\_field. Ta-da! You could probably even do it on min and max at the same time - joining to seperate copies of the table. And throw in those other things that you want to aggregate on per day. Does that much help you out? ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [June 15, 2008, 1:17pm UTC](https://boards.straightdope.com/t/sql-selecting-first-and-last-values-in-an-time-date-ordered-list/452954/3 "2008-06-15T13:17:58Z")
I tried that, pretty much as you describe. But I get a message - error converting from type char to datetime. I know the conversion works because I’ve used it to show the date in the mssql format, but when used in the way above, I get the error. I’m at home now so can’t work on it.