Sql Query Help!!!!

[Andreex]

Oh man.... I have been fighting with this for days now... I need help... please.....

I have this table

Price Adults idSeason
--------------------------------------- ------ -----------
50 1 155
200 2 155
150 3 155
50 1 156
125 2 156


Now.. the table is reduced in complexity and size... but pretty much what I need is this....
I need a select like this

select price, adults
from table
group by idSeason
having adults = max(adults)

do you understand what I am trying to do? I want to select only the registries that have the top adults of the season... in this case I would get (if it worked):

150 3 155
125 2 156

PLEASE HELP ME!!
The having adults = max(adults) doesnt work!
How do I do this??
THANKS!!!

[RobDog888]

SELECT Price, Max(Adults) As MaxAdults, idSeason
FROM Table
GROUP BY idSeason, Price

[RobDog888]

I was too lazy to create a test table to query from.

[Andreex]

nope... none of them work!!
having adults=max(adults) doesnt filter anything... actually... adults and max(adults) if you put them in the select list .. they are the same value!!

[RobDog888]

I am on my second cup of coffee now.
Solution.
Create two queries.

'Query1:
SELECT Table1.idSeason, Max(Table1.Adults) AS MaxOfAdults
FROM Table1
GROUP BY Table1.idSeason;

'Query2:
SELECT Query1.idSeason, Query1.MaxOfAdults, Table1.Price
FROM Query1 
INNER JOIN Table1 ON (Query1.MaxOfAdults = Table1.Adults) AND (Query1.idSeason = Table1.idSeason)
GROUP BY Query1.idSeason, Query1.MaxOfAdults, Table1.Price;

'Results:
idSeason	MaxOfAdults	Price
155	3	150
156	2	125

[Andreex]

can it be done in one?!?!?

[Andreex]

I guess not..

[justastef]

I'm working on it

[Andreex]

SQL SERVER 7.0

Thanks....

[RobDog888]

SQL Server 7.0 Solution in one query.

SELECT
 Q2.idSeason,
 Q2.MaxAdults,
 Table1.Price
FROM
(
 SELECT
  Q1.idSeason,
  Q1.MaxAdults
 FROM
 (SELECT
   Table1.idSeason,
   Max(Table1.Adults) AS MaxAdults 
  FROM 
   Table1 
  GROUP BY
   Table1.idSeason
  ) AS Q1
)
AS Q2
INNER JOIN Table1 ON (Q2.MaxAdults=Table1.Adults) AND (Q2.idSeason=Table1.idSeason)
GROUP BY
 Q2.idSeason,
 Q2.MaxAdults,
 Table1.Price

RESULTS:
idSeason    Adults          Price
-------------------------------------
155         3               150
156         2               125

PS. I am on my thrid cup of coffee.

[RobDog888]

With your query modified from tablename to table1...

Select Price, Adults, idSeason
from table1
group by idseason
having adults=max(adults)

I get these errors on SQL 2000 actually.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'table1.Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'table1.Adults' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Line 1
Column 'table1.Adults' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.