-
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:
-
-
-
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.