changing a query to remove "HAVING"

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)

changing a query to remove "HAVING"

Author

Topic

frozax
Starting Member

3 Posts

Posted - 2005-09-11 : 18:19:17

Hello, I have the following query :

SELECT DISTINCT name AS n, score, country FROM `table` HAVING score = ( SELECT score FROM `table` WHERE name = n ORDER BY score DESC LIMIT 1 ) ORDER BY score DESC

And i would like to get the same result without using HAVING and without two SELECT in the query. Is it possible ?
Some explanations on the query:
I have one single table containing for example:

NAME SCORE COUNTRY
A 10 USA
B 100 NZ
C 50 CA
A 120 USA
A 40 USA
D 75 MX
B 50 NZ

I want to create a hiscore list with only one entry per (name/country).
Is the example, i would get:

A 120 USA
B 100 NZ
D 75 MX
C 50 CA

The main problem is because I can have many scores with same name and I need to sort them and get the best one.

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 00:46:56

Try this

Select Name, max(Score), max(Country) from yourTable group by Name

Madhivanan

Failing to plan is Planning to fail

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2005-09-12 : 03:44:35

[code]
SELECT name AS n, score, country
FROM t as q
where score in (
SELECT max(score)
FROM t
WHERE name = q.name)
ORDER BY score DESC
[/code]

frozax
Starting Member

3 Posts

Posted - 2005-09-12 : 12:50:43

Thank a lot, I forgot the max() thing.

quote:
Originally posted by madhivanan

Try this

Select Name, max(Score), max(Country) from yourTable group by Name

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-12 : 12:52:32

LIMIT 1?

my, my, mySQL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-13 : 00:41:36

quote:
Originally posted by X002548

LIMIT 1?

my, my, mySQL?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Yes. I think so