CODEWITHSUNDEEP
mysqlsql
KBriggs
KBriggs

Reputation: 1412

Selecting a subset of rows with MySql: conditionally limiting number of entries selected

this is a followup question to my previous query. I hope that posting a new question is appropriate in the circumstances: Selecting a subset of rows from a PHP table

I have an sql table that looks like this (for example):

id seller price amount
1  tom    350   500
2  tom    350   750
3  tom    350   750
4  tom    370   850
5  jerry  500   1000

I want to select one row per seller: in particular, for each seller I want the row with the cheapest price, and the largest amount at that price. In the example above, I want rows 2 and 5 (or 3 and 5, I don't care which of 2 and 3 I get as long as I only get one of them).

I am using this:

dbquery("SELECT a.* FROM $marketdb a
        INNER JOIN
        (
            SELECT  seller, MAX(amount) amount
            FROM    $marketdb
            WHERE   price=$minprice
            GROUP   BY seller
        ) b ON a.seller = b.seller AND
                a.amount = b.amount;");

But this is giving me rows 2,3 and 5, and I only want one of rows 2 and 3.

I also have a nagging suspicion that this might not always return the minimum price rows either. My tests so far have been confused by the fact that I am getting more than one row with the same amount entered for a given seller.

If someone could point out my error I would be most appreciative.

Thanks!

EDIT: my apologies, I did not ask what I mean to ask. I would like rows returned from the global min price, max 1 per seller, not the min price for each seller. This would be only row 2 or 3 above. Sorry!

Upvotes: 1

Views: 3632

Answers (5)

Justin
Justin

Reputation: 9724

Query:

SQLFIDDLEExample

SELECT s.* 
FROM sellers s 
WHERE s.id = (SELECT s2.id
              FROM sellers s2
              WHERE s2.seller = s.seller
              ORDER BY s2.price ASC, s2.amount DESC
              LIMIT 1)

Result:

| ID | SELLER | PRICE | AMOUNT |
--------------------------------
|  2 |    tom |   350 |    750 |
|  5 |  jerry |   500 |   1000 |

Upvotes: 0

Abubakkar
Abubakkar

Reputation: 15644

Just try adding another group by on seller as you want single row for a seller

to final query like

SELECT a.* FROM $marketdb a
INNER JOIN
(
    SELECT  seller, MAX(amount) amount
    FROM    $marketdb
    WHERE   price=$minprice
    GROUP   BY seller
) 
b ON a.seller = b.seller AND
a.amount = b.amount group by a.seller;

Upvotes: 2

Strawberry
Strawberry

Reputation: 33935

There's an ugly hack at the end of this answer, but if you don't care which row is returned then I guess it saves some typing. Although, if you really don't care which row is returned, that tends to point to a more fundamental flaw in your schema design!

 SELECT x.* 
   FROM market x 
   JOIN 
      ( SELECT seller,MIN(price) min_price FROM market GROUP BY seller) y 
     ON y.seller = x.seller 
    AND y.min_price = x.price
   JOIN 
      ( SELECT seller,price,MAX(amount) max_amount FROM market GROUP BY seller,price) z
     ON z.seller = y.seller 
    AND y.min_price = z.price
    AND z.max_amount = x.amount
  GROUP
     BY seller;

Another method, which i dislike but which is popular with others here, goes something like this...

SELECT x.*         
  FROM 
     ( SELECT * 
         FROM market
        ORDER
           BY seller
            , price
            , amount DESC
            , id
     ) x
 GROUP
    BY seller;

Upvotes: 1

CodeAngry
CodeAngry

Reputation: 12985

Test this SQL fiddle:

http://sqlfiddle.com/#!2/7de03/2/0

CREATE TABLE `sellers` (
  `id` INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `seller` VARCHAR(16) NOT NULL,
  `price` FLOAT NOT NULL,
  `amount` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `sellers` VALUES (1, 'tom', 350, 500);
INSERT INTO `sellers` VALUES (2, 'tom', 350, 750);
INSERT INTO `sellers` VALUES (3, 'tom', 350, 750);
INSERT INTO `sellers` VALUES (4, 'tom', 350, 850);
INSERT INTO `sellers` VALUES (5, 'jerry', 500, 600);
INSERT INTO `sellers` VALUES (6, 'jerry', 500, 1000);
INSERT INTO `sellers` VALUES (7, 'jerry', 500, 800);

SELECT * FROM
(SELECT DISTINCT * FROM sellers ORDER BY price ASC, amount DESC) t0
GROUP BY seller;

Kind of... works :)

Upvotes: 1

Rob W
Rob W

Reputation: 9142

You may need to GROUP BY the seller column outside of the join. Also, your WHERE clause looks like where price is a set number, instead of <=.

Upvotes: 0

Related Questions