0

I am trying to display the results of a basic arithmetic test created in python, the test is repeated three times and collects three scores, this works fine, however whe I try to display these by the highest score of the three in descending order I am displayed an error message. which is as follows.

Traceback (most recent call last):
  File "F:\Adair,Rowan CA2\Task 3\Code\DisplayTablesScore.py", line 4, in <module>
    cursor.execute("SELECT * FROM class1 ORDER BY (score1,score2,score3) DESC")
sqlite3.OperationalError: near ",": syntax error

However when it is organised by one of the columns e.g. only score 1 it works fine. The code that I cannot figure out how to fix is below.

import sqlite3
connection = sqlite3.connect("class1.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM class1 ORDER BY (score1,score2,score3) DESC")
print("Class 1:")
result = cursor.fetchall() 
for r in result:
    print(r)

Any help with this would be immensly appreciated, I am also trying to determine the average aswell.

The Boat
  • 27
  • 7

2 Answers2

1

First the syntax of ORDER BY its wrong, should be

SELECT * FROM class1 ORDER BY score1 DESC, score2 DESC, score3 DESC;

Based in the response of Max of multiple columns, if you need to order by the high score of the columns, you need to calculate them

SELECT score1, score2, score3,
    CASE
    WHEN score1 >= score2 AND score1 >= score3 THEN score1
    WHEN score2 >= score1 AND score2 >= score3 THEN score2
    WHEN score3 >= score1 AND score3 >= score2 THEN score3
    ELSE score1
END AS high_score FROM class1 ORDER BY high_score DESC;
Community
  • 1
  • 1
Patricio
  • 403
  • 3
  • 10
0

The SQL clause ORDER BY doesn't take parentheses when declaring its arguments. In other words, doing ORDER BY (...) is indeed invalid syntax.

To declare multiple columns to order against, just omit the parentheses:

cursor.execute("SELECT * FROM class1 ORDER BY score1 DESC, score2 DESC ,score3 DESC")

This example should cover the basics. Note that each column may require its own descension/ascension keyword.

Akshat Mahajan
  • 9,543
  • 4
  • 35
  • 44