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 6 of 6
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Aggregate Function problem...

    Hello all. I didn't find a forum for regular SQL, so I figured I would try here. I have the following statement which I am having problems with:


    Code:
    SELECT Max(odThis) as ThisOD, Max(odLast) as LastOD, id   FROM tblEqOdometer  WHERE EqNum = '" & EqNum & "' group by Id

    The thing is that I need the ID for another process, but using the Group By Id it is not giving me the Max(odThis) that I need, it seems like it is giving the first record that it comes across. Is there a way to either get rid of the Group By or do an Order By the Id? I really don't need them grouped, but it is forcing me to since I used the Sum() function.

    Thanks,

    Parallon

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    the way the query works now, is select all records where the WHERE part of the query applies. Then group them by Id and for each group, pick the maximum value for odThis and pick the maximum value for odLast in that particular group.

    ANd indeed, if you use aggregate functions on certain requested columns, you need to group on the columns where no aggregate function is performed.

    If you can define functionally which result you expect, i can try to put up the query you need. If you can post some sample data and the desired result, that would give me a good start.

    THis could have been posted in the General Databases forum
    Last edited by Roelf; 03-20-2007 at 10:08 PM. Reason: Link added
    I am the luckiest man in the world

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thank you for your reply. Here is some sample data. What I need the system to do is to find the Highest odometer reading in the 'OdThis' column and use that line item. The reason I need the ID is so when the user Submits the report, I can change the value of the 'Submitted' column for the appropriate ID.

    So, with this query, I am getting a result of 1000 instead of 2500.

    Code:
    ID       EqNum        WeekStart       WeekEnd       OdLast       OdThis       Submitted
    178      TRK-0001     3/7/2007        3/13/2007     0            1000         No
    180      TRK-0001     3/14/2007       3/20/2007     1000         1500         No
    182      TRK-0001     3/21/2007       3/27/2007     1500         2100         No
    183      TRK-0001     3/28/2007       4/3/2007      2100         2500         No
    Thanks again,

    Parallon

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    Code:
    SELECT Id
    FROM tblEqOdometer  
    WHERE (
      OdThis = (SELECT MAX(odthis) FROM tblEqOdometer)
    )
    This returns the Id (or more than one Id) for the record with the highest value for odthis
    I am the luckiest man in the world

  • #5
    SSJ
    SSJ is offline
    Regular Coder
    Join Date
    Mar 2007
    Posts
    230
    Thanks
    0
    Thanked 4 Times in 4 Posts
    Try this...

    SELECT Id
    FROM tblEqOdometer
    WHERE (
    OdThis In (SELECT MAX(odthis) FROM tblEqOdometer)
    )

  • #6
    Regular Coder
    Join Date
    May 2005
    Posts
    215
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Thank you both so much for leading me in the right direction. I did have to modify it slightly since I was using other fields elsewhere within my page. Here is what I came up with:

    Code:
    SELECT ID, OdThis AS ThisOD, OdLast AS LastOD 
    FROM tblEqOdometer 
    WHERE (
      OdThis=(SELECT MAX(odthis) FROM tblEqOdometer)
    )
    Works like a charm!

    Thanks again,

    Parallon


  •  

    Posting Permissions

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