Sql Query Help!!!!

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!!!

Code:

---

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

---

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

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!! :(

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

Code:

---

'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

---

can it be done in one?!?!?
:rolleyes:

I guess not.. :(

I'm working on it

SQL SERVER 7.0
:)
Thanks....

SQL Server 7.0 Solution in one query.

Code:

---

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.

:D

With your query modified from tablename to table1...

Code:

---

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.