CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
user111434
user111434

Reputation: 31

get max of max in select function

so I have 3 tables :

parkingZone - 

ZID - zone id
Name - name of the zone
maxprice - max price of the parking zone
pricePerHour

carParking - 

CID - the id of the car which parking
StartTime - start time of parking 
EndTime -  end time of parking 
ParkingZoneID - zone ID (same as ZID in parkingzone)
Cost - how much the paking costed

Cars - 

CID - same as CID in carParking
ID - ID of who owns the car
cellPhone - cellPhone of who ownsthe car

now I need to find the ID and CID of who has the max "cost" of the max "maxprice", In other words, I need to find the ZID of the maximum "maxprice" and then to find the ID and CID of the maximum "cost" related to "ZID"

so I managed to find all the CID that relates to the ZID:

select  CarParking.CID, CarParking.Cost
    from CarParking
        inner join  (select ParkingArea.AID
            from ParkingArea
            inner join(
                select max(ParkingArea.maxpriceperday) maxpriceperday
                from ParkingArea
            )maxrow on maxrow.maxpriceperday = ParkingArea.maxpriceperday)maxCid on  maxCid.AID= CarParking.ParkingAreaID

but how can I get the maximum cost, and then the CID AND ID from Cars table?

important note - there can be more then one max both in "maxpriceperday" and "Cost" which means there could be more then one ZID with maxpriceperday(if they are equal) and more then one maximum CID to each of those ZID (if the costs are equal).

so using "TOP" or "LIMIT" will not work.

for example:

example

continue_of_example how can I accomplish that? thanks

Upvotes: 0

Views: 115

Answers (1)

J.D.
J.D.

Reputation: 4561

This would be my approach: First, select all ZID's with maxprice using a dense_rank. Next, use a second dense_rank to get all CID and with the highest cost from the selected ZID's. Finally, use the found CID's to get the Car-data.

That gives the CID's and ID's of all cars that have the highest (equal) cost in all lots with the highest maxprice.

If the dense_rank is new to you, you can read about it here

Gathered in one query:

SELECT CID
, ID
FROM Cars AS C
INNER JOIN (
            SELECT CID
            , Cost
            , DENSE_RANK() over (ORDER BY Cost DESC) AS orderedCosts 
            FROM carParking AS CP
            INNER JOIN (SELECT ZID
                        , DENSE_RANK() over (ORDER BY maxprice DESC) AS orderedMaxprice
                        FROM ParkingArea 
                        ) AS PA
                        ON PA.ZID= CP.ParkingAreaID
                        AND orderedMaxprice = 1
            ) as cars_most_costs
            ON cars_most_costs.CID = C.CID
            AND cars_most_costs.orderedCosts = 1

A dense_rank works like this:

ZID | maxprice| dense_rank
 1  |   1000  |     1
 3  |   1000  |     1
 2  |   500   |     2   
 4  |   400   |     3

Using your paper example: First step gets ZID 1 and 3, which both have the highest maxprice. Next step gets CID 1010 and 1011, which are the cars with the higest cost on parkingzoneID's 1 and/or 3. Final step returns CID/ID combo's 1010/2000 and 1011/2001. The result you provided is actually wrong, because CID 1014 has a cost of 10 while the other two have 20.

If you meant max cost per parkingzoneID, then the question was not very clear, but you only have to change one line:

, DENSE_RANK() over (PARTITION BY ZID ORDER BY Cost DESC) AS orderedCosts

This will also return car 1014/2004

Upvotes: 1

Related Questions