List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 9 1999 6:01am
Subject:SQL Cookbook for MySQL? (was: Help me to form a SELECT please)
View as plain text  
On Fr, 1999-08-06 16:51:24 +0400, Andrey Muratov wrote:
> I need the goods that are supported only by one sertain dealer=11
> i.e. unique items for the dealer=11

    SELECT      item
    FROM        Medic
    WHERE       dealer = 11
    GROUP BY    item
    HAVING      COUNT(*) = 1

This only works allright, if there are no duplicate entries ...



Dear MySQL community, as I've seen quite a few questions of this kind
since I've joined this MySQL mailing list, what do you think about
preparing a "SQL Cookbook for MySQL", that later on maybe also could
become part of the MySQL manual?

This SQL Cookbook should include typical tasks and their solutions
in both standard SQL and with MySQL's extensions and restrictions,
collected off this mailing list.  Everything should be illustrated
with examples to make it even more easy for beginners.


All the stuff below using sub-queries is written off the cuff, so I'd
appreciate somebody (maybe with access to an Oracle DBMS)
double-checking these queries ...


Well, I'll start here with an example similiar to Andrey's table.

The table 'shop' holds the prices of each article (item number) for
certain traders.
Supposing that each trader has a single fixed price per article,
then (item, trader) is a primary key for the records.

  > --------------
  > CREATE TABLE shop (
  >   article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
  >   dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
  >   price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
  >   PRIMARY KEY(article, dealer)
  > )
  >
  > INSERT INTO shop VALUES
  >  (1,'A',3.45)
  > ,(1,'B',3.99)
  > ,(2,'A',10.99)
  > ,(3,'B',1.45)
  > ,(3,'C',1.69)
  > ,(3,'D',1.25)
  > ,(4,'D',19.95)
  > --------------

Okay, so the example data is:
  > --------------
  > SELECT * FROM shop
  > --------------
  >
  > +---------+--------+-------+
  > | article | dealer | price |
  > +---------+--------+-------+
  > |    0001 | A      |  3.45 |
  > |    0001 | B      |  3.99 |
  > |    0002 | A      | 10.99 |
  > |    0003 | B      |  1.45 |
  > |    0003 | C      |  1.69 |
  > |    0003 | D      |  1.25 |
  > |    0004 | D      | 19.95 |
  > +---------+--------+-------+
  > 7 rows in set (0.01 sec)


Some SQL tasks that come to mind:

1. Maximum of field

1.1 Maximum of field: overall

1.1.1 Maximum of field: overall: only the value

  "What's the highest item number?"
  > --------------
  > SELECT MAX(article) AS article FROM shop
  > --------------
  >
  > +---------+
  > | article |
  > +---------+
  > |       4 |
  > +---------+
  > 1 row in set (0.00 sec)

1.1.2 Maximum of field: overall: the entire row
      «The row holding the maximum of a certain field.»

  "Find number, dealer, and price of the most expensive article!"

* In Standard-SQL this is easily done with a sub-query:
    SELECT article, dealer, price
    FROM   shop
    WHERE  price=(SELECT MAX(price) FROM shop)

* In MySQL (not having sub-selects yet) just do it in two steps:
   1st, get the maximum value (as in 1.1.1).
   2nd, using this value compile the actual query,
    SELECT article, dealer, price
    FROM   shop
    WHERE  price=19.95

* Another solution is to sort all rows descending by price and only
   get the first row using the MySQL specific LIMIT clause:
  > --------------
  > SELECT article, dealer, price
  > FROM   shop
  > ORDER BY price DESC
  > LIMIT 1
  > --------------
  >
  > +---------+--------+-------+
  > | article | dealer | price |
  > +---------+--------+-------+
  > |    0004 | D      | 19.95 |
  > +---------+--------+-------+
  > 1 row in set (0.04 sec)

  Note:  if there are several most expensive articles (e.g. each 19.95)
         the LIMIT solution does only show one of them!

1.2 Maximum of field: per group

1.2.1 Maximum of field: per group: only the values

  "What's the highest price per article?"
  > --------------
  > SELECT article, MAX(price) AS price
  > FROM   shop
  > GROUP BY article
  > --------------
  >
  > +---------+-------+
  > | article | price |
  > +---------+-------+
  > |    0001 |  3.99 |
  > |    0002 | 10.99 |
  > |    0003 |  1.69 |
  > |    0004 | 19.95 |
  > +---------+-------+
  > 4 rows in set (0.00 sec)

1.2.1 Maximum of field: per group: the entire row
      «The rows holding the group-wise maximum of a certain field»

  "For each article find the dealer(s) with the most expensive price."

* In Oracle I'd do it with a sub-query like this:
    SELECT article, dealer, price
    FROM   shop s1
    WHERE  price=(SELECT MAX(s2.price)
                  FROM shop s2
                  WHERE s1.article = s2.article)

* In MySQL best do it in several steps:
   1st, get the list of (article,maxprice) (as in 1.2.1).
   2nd, for each article get the corresponding rows which have the
        stored maximum price.

  This easily can be done with a temporary table:
   a) CREATE TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);
   b) LOCK TABLE tmp WRITE, shop READ;
   c) INSERT INTO tmp
             SELECT article, MAX(price) FROM shop GROUP BY article;
   d) SELECT article, dealer, price
      FROM   shop, tmp
      WHERE  shop.article=tmp.articel AND shop.price=tmp.price;
   e) UNLOCK TABLES;
   f) DROP TABLE tmp;

* Can it be done with a single query?

  Yes, but only quite inefficient with a trick that I call the
  "MAX-CONCAT trick":
  > --------------
  > SELECT article,
  >        SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  >   0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
  > FROM   shop
  > GROUP BY article
  > --------------
  >
  > +---------+--------+-------+
  > | article | dealer | price |
  > +---------+--------+-------+
  > |    0001 | B      |  3.99 |
  > |    0002 | A      | 10.99 |
  > |    0003 | C      |  1.69 |
  > |    0004 | D      | 19.95 |
  > +---------+--------+-------+
  > 4 rows in set (0.01 sec)

  Explanation: ... [sometimes later, time escapes me now] ...


Other possible topics:

2. Counting of distinct values
2.1 Counting of distinct values: overall
2.2 Counting of distinct values: per group

3. Duplicates
3.1 Duplicates: get distinct rows only
3.2 Duplicates: find duplicates (in respect to some key)

4. n:m Relationships
4.1 n:m Relationships: select on number of m
4.2 n:m Relationships: select on specific set of values on "m" side

[...]

And so on, I think you all got the idea.  What do you think?
Is it worth the effort?

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Help me to form a SELECT pleaseAndrey Muratov6 Aug
Re: Help me to form a SELECT pleaseAndrey Muratov6 Aug
  • Re: Help me to form a SELECT pleaseThimble Smith6 Aug
  • SQL Cookbook for MySQL? (was: Help me to form a SELECT please)Martin Ramsch9 Aug