10-21-03, 12:19 #1Registered User
- Join Date
- Oct 2003
Unanswered: How to get largest 3 values from 5 columns
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.
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.
10-21-03, 16:33 #2Registered User
- Join Date
- Jun 2003
- West Palm Beach, FL
Did you try using the MAX function?
MAX(Round1Pts,MAX(Round2Pts,MAX(Round3Pts,MAX(Roun d4Pts)))) AS Best3Points
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb