How to get min and max from resultset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

How to get min and max from resultset

Author

Topic

newbie2006
Starting Member

7 Posts

Posted - 2007-07-12 : 20:57:42

Hi there

I am using MS Access and am trying to extract the region information from my vendorquery and find the earliest period and latest period for each. My data looks like (RID=regionID):

RID Year Quarter
1 1997 2
1 1997 3
2 1998 2
2 1999 1

I need to retrieve the following

RID Year Quarter
1 1997 2
2 1998 2

This is to select the minimum year for that Region_id

SELECT vendorquery.Region_ID, Min(vendorquery.Year) AS MinOfYear
FROM vendorquery
GROUP BY vendorquery.Region_ID;

and this is to select the min quarter for that Year

SELECT vendorquery.Year, Min(vendorquery.Quarter) AS MinOfQuarter
FROM vendorquery
GROUP BY vendorquery.Year;

I tried to combine them

SELECT vendorquery.Region_ID, Min(vendorquery.Year) AS MinOfYear, Quarter FROM vendorquery
Inner join (
SELECT vendorquery.Year, Min(vendorquery.Quarter) AS MinOfQuarter
FROM vendorquery
GROUP BY vendorquery.Year) as B on B.Year=vendorquery.Year
GROUP BY vendorquery.Region_ID

but it doesnt seem to work, the error message was that i did not use "quarter" as part of an aggregate function. If i did a group by region_id, quarter, then i will not be retrieving the resultset that i wanted. How should i combine the 2 SELECT?

(moved to the Access forum)

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-09 : 16:59:07

Its not clear what exactly you are trying to do.

Will you not need the data for 1999 in the result set.

Also post some more data along with scripts for tables. That way you will get more responses.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources