Tut#21: issue with "max" boxes of frogs:

0 Chris Ciampi · April 6, 2015
Hey,

Running through the SQL tutorials and was dinking around: query from tutorial

SELECT name, MIN(cost)
FROM items
WHERE name
LIKE '%boxes of frogs'
AND seller_id IN(

SELECT seller_id
FROM items
WHERE name
LIKE '%boxes of frogs' 

)

This returns "3 boxes of frogs" with a cost '30.48...'.

However, when you swap the MIN(cost) with MAX(cost), it returns the max cost (857.75), but still returns '3 boxes of frogs'. Shouldn't this associate with '7 boxes of frogs?'  Why does the name not change with the cost association?  

Post a Reply

Replies

Oldest  Newest  Rating
0 Dave . · May 6, 2015
The tutorial is about sub queries and works but is not wholly proper.

There is actually no relationship between "name" and MIN/MAX(cost) as written

If we run this code

SELECT
*
FROM items
WHERE name
LIKE '%boxes of frogs'


We get

idnamecostseller_idbids
103 boxes of frogs30.4968145
1148 boxes of frogs74.29 699
127 boxes of frogs857.75 1888

If we change that code to


SELECT
*,
MIN(cost)
FROM items
WHERE name
LIKE '%boxes of frogs'
-- ORDER BY name DESC


We now get just one line for our result which is a result of using the function MAX or MIN

idnamecostseller_idbidsMIN(cost)
103 boxes of frogs30.496814530.48

Id, name, cost, seller_id and bids show up in the report because they are the first result in the database. If ID 10 had the name 48 boxes of frogs then that would be the one to show up in the report.

If  we change the code to


SELECT
*,
MAX(cost)
FROM items
WHERE name
LIKE '%boxes of frogs'
-- ORDER BY name DESC

We get

idnamecostseller_idbidsMAX(cost)
103 boxes of frogs30.4968145857.75

As expected.

If we wanted a better relationship between the item name and the MAX(cost) we would do this


SELECT
id,
name,
cost,
MAX(cost),
seller_id,
bids
FROM items
WHERE name
LIKE '%boxes of frogs'
AND cost = (
    SELECT
    MAX(cost)
    FROM items
    WHERE name
    LIKE '%boxes of frogs'
    )


That would give us

idnamecostMAX(cost)seller_idbids
127 boxes of frogs857.75857.751888
  • 1

SQL & Databases

106,955 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator