Is there a way to find the max value in a table of a self-join?

Question

SELECT *
FROM Trips T1
WHERE T1.Cost NOT IN (SELECT T2.Cost FROM Trips T1, Trips T2 WHERE T1.Cost > T2.Cost)

This is currently my query that will return the most expensive trip listed in the itinerary. Of course, you can simply use MAX, but I have been instructed not to.

This is the query I have figured out that returns what I want. In the Trips table, it features the TripId, StateofTrip (ie MA, NC, PA), TravelMethod (Car, Train, Bus), and the Cost. So output may look like

987  CA   Plane   2000

What I'm wondering is if there is a way to do this in a Self Join, not a nested subquery. So something that starts like this...

SELECT * 
FROM Trips T1, Trips T2
....

Is this possible to do without using MAX explicitly? How can I compare values in a self-join and return the singular max?

Additionally, in the first query, can someone explain the nested example? I don't really understand how NOT in returns the desired result.

score 1 · Answer 1 · answered Apr 25 '21 at 19:11

1

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax.

You can find the max using a LEFT JOIN and WHERE:

SELECT T1.* 
FROM Trips T1 LEFT JOIN
     Trips T2
     ON T2.Cost > T1.Cost
WHERE t2.Cost IS NULL;

That said, I consider this a ridiculous approach to finding the max. There are much better ways, including:

SELECT T.*
FROM Trips T
ORDER BY T.COST DESC
LIMIT 1;

answered Apr 25 '21 at 19:11

Gordon Linoff

1,242,037
58
646
786

the `limit 1` doesn't find all records with the maximum cost (which they may or may not want) – ysth Apr 25 '21 at 19:13
That is concerning to hear because this is how I'm being taught syntax. Thank you for making note. – buckywucky Apr 25 '21 at 19:13

score 1 · Answer 2 · answered Apr 25 '21 at 19:31

query that will return the most expensive trip

"most expensive trip" is a trip which's price/cost is maximal - i.e. it is possible that more than one trip may be most expensive at the same time. So

SELECT *
FROM Trips T1
WHERE NOT EXISTS ( SELECT NULL
                   FROM Trips T2
                   WHERE T2.Cost > T1.Cost )

Is there a way to find the max value in a table of a self-join?

2 Answers2