Obtaining First and Last Values in a Group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Stewart Campbell » Obtaining First and Last Values in a Group

Obtaining First and Last Values in a Group

Rate Topic

Display Mode

Topic Options

Author

Message

donald.hook

Posted Wednesday, March 30, 2011 6:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 31, 2011 12:00 PM
Points: 1, Visits: 26

I am trying to get data for a week. I am getting the min and max for a couple of fields but also want to get the first and last values for a couple of columns. I am using a query to group by week and can get the min and max but not sure how to obtain the first and last values. I had tried joining the table on itself, but to no avial.

Below is the query... Any insight is greatly appreciated!!

SELECT MIN(q1.[QUOTE_DATE]) AS STARTDATE,
MAX(q1.[QUOTE_DATE]) AS ENDDATE,
--> FIRST(q2.[OPEN]) AS OPENPRICE,
MAX(q1.[HIGH]) AS HIGH,
MIN(q1.[LOW]) AS LOW,
--> LAST(q1.[CLOSE]) AS CLOSEPRICE
FROM [dbo].[quote] as q1
WHERE q1.[SECURITY_ID] = @securityId
AND q1.[QUOTE_DATE] BETWEEN @fromDate AND @toDate
GROUP BY DATEPART(WEEK,q1.[QUOTE_DATE])
ORDER BY 1 ASC

Post #1086180

« Prev Topic | Next Topic »

Permissions