CODEWITHSUNDEEP
phpmysqlsql
KBriggs
KBriggs

Reputation: 1412

Selecting a subset of rows from a PHP table

I'm pretty new to using SQL, so I was hoping someone could point me in the right direction for setting up a slightly more complex query than I have needed to use in the past.

I have a simple mySql table representing a market for a game, which looks like this: (numbers for the purposes of examples later:

id seller price amount
1  tom    330   100
2  tom    370   500
3  tom    400   750
4  jerry  700   250

currently I am loading this table using php:

$items = dbquery("SELECT * 
                  FROM $marketdb 
                  WHERE price=$minprice 
                  ORDER BY amount;");

This is fine, but what I would like to do instead is to only load one row per seller: in particular, I would like to load only the row with the largest value of 'amount' for each seller. In the example above, this means the result would only contain the last two lines in the table.

Upvotes: 1

Views: 375

Answers (1)

John Woo
John Woo

Reputation: 263723

You can have a subquery which separately get the greatest amount for every seller and join it with the table again to get the extra columns.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  seller, MAX(amount) amount
            FROM    tableName
            GROUP   BY seller
        ) b ON a.seller = b.seller AND
                a.amount = b.amount

or

SELECT  a.*
FROM    tableName a
WHERE   a.amount =
        (
            SELECT  MAX(amount)
            FROM    tableName b
            WHERE   a.seller = b.seller
        )

both queries will OUTPUT

╔════╦════════╦═══════╦════════╗
║ ID ║ SELLER ║ PRICE ║ AMOUNT ║
╠════╬════════╬═══════╬════════╣
║  3 ║ tom    ║   400 ║    750 ║
║  4 ║ jerry  ║   700 ║    250 ║
╚════╩════════╩═══════╩════════╝

Upvotes: 1

Related Questions