Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How do I implement First() and Last() aggregates like Access in MS-SQL?

Posted on 2006-05-02
Medium Priority
Last Modified: 2008-03-06
Hi!  I need First() and Last() aggregates for MS-SQL that work like the same-named aggregates in Access.  My specific use is the I have financial "tick" values (date/time with price) that I want to roll up into a standard Open/High/Low/Close bar.  Min() and Max() work great for the Low and High values but I need something to get the first (Open) and last (Close) values as well.  I will be grouping the bars on one-minute intervals.  Any help in this regard would be greatly appreciated.  Thanks....
Question by:lsberman
1 Comment
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 16590315
sql server does not really have the First and Last, you can however implement a subquery getting the first and last values for a given group of data and related to a certain order

select id, min(value) as min_value , max(value) as max_value
, ( select top 1 value from yourtable i where i.id = t.id order by somefield asc   ) as first_value
, ( select top 1 value from yourtable i where i.id = t.id order by somefield desc ) as last_value
from yourtable t
group by t.id

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question