Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: How to get largest 3 values from 5 columns

    Hi,

    I wondered if someone could help me, I'm new to defining my own SQL statements!

    I am looking to extract 4 columns of data from a Access database, these columns I am pasting into a table. That I can do using the select query, and set them AS variable1 to 4.

    (I am putting these variables into an ASP page)

    My problem comes when I need to create an extra column which shows the best 3 results from the 4 returned data points. In excel I would use something such as =Large(CellA1:E1, 1) to give me the largest value and then so on for the largest 3, and then summate these three values.

    I have pasted my code so far below:

    I'd be very grateful if someone could enlighten me.

    Many thanks!
    __________________________________________________ ____

    ResultsSQL = "SELECT *, (Round1Pts) AS Round1Points, " & _
    "(Round2Pts) AS Round2Points, " & _
    "(Round3Pts) AS Round3Points, " & _
    "(Round4Pts) AS Round4Points, " & _
    "(??????????) AS Best3Points " & _
    "FROM " & ResultsTable & " ORDER BY (Best3Points) DESC, (DriverName) DESC"
    Last edited by RichieRodriguez; 10-21-03 at 12:34.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Lightbulb

    Did you try using the MAX function?

    MAX(Round1Pts,MAX(Round2Pts,MAX(Round3Pts,MAX(Roun d4Pts)))) AS Best3Points
    ...etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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