Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to get Min and Max from Resultset

    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 raw data looks like (RID=regionID):

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

    I have used the following sql:

    SELECT Region_id, VendorQuery.Year AS BeginningYear
    FROM VendorQuery
    WHERE (((VendorQuery.Year)=(Select min(year) from vendorquery as subquery where subquery.year = vendorquery.year)));

    I manage to get the region and beginning year using the above SQL but now i need to extend the query into "quarter" e.g. i should retrieve 1997 qtr 2 as my last record for region 1:

    RID Year Quarter
    1 1997 2

    How do i do that?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Try this article from the MySQL Moderator Guelphdad.

    It explains how to get what you are looking for:

    http://guelphdad.wefixtech.co.uk/sql...romgroup.shtml
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New to the CF scene
    Join Date
    Jun 2006
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there

    i did what was suggested. However i need to do this at 2 levels:

    1) between RID and Year
    2) between Year and Quarter

    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
    where Quarter=(SELECT vendorquery.Year, Min(vendorquery.Quarter) AS MinOfQuarter
    FROM vendorquery
    )
    GROUP BY vendorquery.Region_ID;

    the error given by MS Access is:
    "You have written a subquery that can return more than one field without using "exists" in the main query "from" clause

    I am really new in SQL and will appreciate any help.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •