Queries - MAX function

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • doma23
    New Member
    • May 2010
    • 107

    #1

    Queries - MAX function

    I have next table from a query:

    Values.....Star t
    25............. .0
    26............. .0
    27............. .1
    28............. .0
    29............. .0
    30............. .0

    What I'm trying to accomplish is to divide all the values with the value in which the start field is 1; in this case, with 27.
    I've done this easily with 2 queries, however I have a large set of this data and I would like to simplify it by putting everything into 1 query.
    Can this be done?

    Thank you!
  • Mariostg
    Contributor
    • Sep 2010
    • 332

    #2
    select values, values/(select max(start) from MyTable) from MyTable

    Comment

    • doma23
      New Member
      • May 2010
      • 107

      #3
      Thank you!

      Just a little correction. The code you provided gave me an insight how it should work ("sub sql inside sql"), but the correct code is a bit different as you probably misunderstood that I need to divide it with max value in start field.
      I actually needed to divide it with max value in Values field where value of teh Start field is 1.
      So the correct code is this:
      Code:
      SELECT tblTest.Values, [tbltest].[Values]/(SELECT tblTest.Values
      FROM tblTest
      GROUP BY tblTest.Values, tblTest.Start
      HAVING (((tblTest.Start)=1))) AS Divided
      FROM tblTest;

      Comment

      • doma23
        New Member
        • May 2010
        • 107

        #4
        Hmm..Now I have another question, which brings me back in a way to my initial question.

        What if the figures in the Start field were not of boolean type. What if they were 1,2,5,3,4; and I need to divide it by max value which is in this case 5.
        I tried to do it, but it gives me back all values. Here is the SQL:

        Code:
        SELECT Max(tblTest.Start) AS MaxDiStart, tblTest.Values
        FROM tblTest
        GROUP BY tblTest.Values;

        Comment

        • Mariostg
          Contributor
          • Sep 2010
          • 332

          #5
          Hi. Yeah I misunderstood your question.
          To answer post #4, try this:
          Code:
          SELECT Top 1 Values,Start
          SORT BY Start DESC
          This select values and start, sort by descending order of Start and return the first row.
          And I guess your post #3 is what you wanted regarding the division issue...

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32669

            #6
            You need a double-subquery. It's possible to do it with just the one and some string manipulation, but the most logical way is like this :
            Code:
            SELECT [Values]
                 , [Values]/(
                SELECT TOP 1
                       [Values]
                FROM   [tblTest]
                WHERE  ([Start] = (
                    SELECT Max([Start])
                    FROM   [tblTest]
                    )
                ) AS Divided
            FROM   [tblTest]

            Comment

            Working...