High Scores Table Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

High Scores Table Query

Author

Topic

pmartin@jessops.com
Starting Member

4 Posts

Posted - 2002-09-20 : 10:07:31

I have been using this for MySQL in an ASP page:

SELECT name, MAX(score) AS score FROM highscores GROUP BY name ORDER BY score DESC LIMIT 8

It grabs the highest score for each name and doesn't display the same name twice in the list of top scorers. Scores are listed from highest to lowest and only 8 records are required.

I have to run the same ASP page on a different server that uses SQL server 2000. My problem is I can't write a query to get the same result.

Any help would be most appreciated.

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-20 : 10:20:08

Probably easiest to use SET ROWCOUNT:

SET ROWCOUNT 8
SELECT name, MAX(score) AS score FROM highscores GROUP BY name ORDER BY score DESC

You could also use the TOP keyword:

SELECT TOP 8 name, MAX(score) AS score FROM highscores GROUP BY name ORDER BY score DESC

But TOP can't be used to set a dynamic number of rows. ROWCOUNT can be set to a variable:

DECLARE @rows int
SET @rows=10
SET ROWCOUNT=@rows
SELECT... etc.

pmartin@jessops.com
Starting Member

4 Posts

Posted - 2002-09-20 : 10:39:47

Thanks for that. I dropped it into my ASP but today I can only test it on my local system using an Access db:

SET objRS = objCon.EXECUTE("SELECT TOP 8 name, MAX(score) AS score FROM tblDartsHighScore GROUP BY player_name ORDER BY score DESC ")

I get an error.

[Microsoft][ODBC Microsoft Access Driver] Circular reference caused by alias 'score' in query definition's SELECT list.

Using...

SET objRS = objCon.EXECUTE("SET ROWCOUNT 8 SELECT player_name, MAX(score) AS score FROM tblDartsHighScore GROUP BY name ORDER BY score DESC")

Throws this error:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I don't know if this is because of Access or something else. Removing SET ROWCOUNT throws the "circular Reference" error again.

Any thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-20 : 10:54:55

OK, are you using MS Access, or SQL Server, or both?

Access doesn't use SET ROWCOUNT, you have to use TOP instead. Access also does not allow you to alias a column with the same name as the column, so you can't do Max(score) AS Score. SQL Server does allow this however.

You need to separate SQL commands with a semicolon:

SET objRS = objCon.EXECUTE("SET ROWCOUNT 8; SELECT player_name, MAX(score) AS score FROM tblDartsHighScore GROUP BY name ORDER BY score DESC")

If you are going to use this in SQL Server, you can make the query a stored procedure and pass the # of rows you want returned as a variable. This will improve performance and give great flexibility. You can also set the default # of rows to 8, or any other number.

pmartin@jessops.com
Starting Member

4 Posts

Posted - 2002-09-20 : 11:26:20

Thanks for your help. I will be running the code on SQL Server once it goes live. I was hoping to test it on my dev server but I don't have another copy of SQL Server (it is still in the post)

I did try a different alias name for Access but it still throws an error:

"You tried to execute a query that does not include the specified expression 'score' as part of an aggregate function."

In order to demonstrate the app to my collegues I was hoping to get it working on Access aswell.

These errors are what I keep on getting and I have been going round in circles it seems.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-20 : 11:45:28

Well, I tried this in the Northwind sample database:

SELECT TOP 5 [Order Details].OrderID, Sum([Order Details].UnitPrice) AS Total
FROM [Order Details]
GROUP BY [Order Details].OrderID
ORDER BY Sum([Order Details].UnitPrice) DESC

And it worked as expected. Access is very finicky about the SQL it will run, if you haven't been using the query design grid you might want to try it; it will accommodate Access' quirks.

BTW, Access uses DISTINCT values with the TOP keyword, so you will probably get more rows that what's specified by the TOP value. There's a workaround for it, I believe it's in the MS Access forum here on SQL Team, a forum search for "TOP" should find it.

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2002-09-20 : 11:46:06

in your select list you have name but in the group by clause you have player_name. These should be the same.

pmartin@jessops.com
Starting Member

4 Posts

Posted - 2002-09-20 : 11:59:39

robvolk - Many thanks for the example, Access is now outputting what I expected.

LarsG - Well spotted, but it was the quirky Access syntax that was causing the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources