List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:August 10 1999 1:31pm
Subject:Re: can it be done with only one query?
View as plain text  
On Di, 1999-08-10 17:17:52 +0400, some anonymous person wrote:
> if I execute query like "select * from Domain_tech where
> name='petroconcert.spb.ru'" I got:
> 
> +------+-------+---------------------+
> | uid  | pid   | name                |
> +------+-------+---------------------+
> | 9748 | 12994 | petroconcert.spb.ru |
> | 9748 | 16908 | petroconcert.spb.ru |
> | 7801 | 23606 | petroconcert.spb.ru |
> +------+-------+---------------------+
> 
> but I need to select only the line with the greatest pid.

Excerpt of my SQL Cookbook (to be written :-)
----------------------------------------------------------------------
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!
----------------------------------------------------------------------

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
can it be done with only one query?Webmaster10 Aug
  • Re: can it be done with only one query?Martin Ramsch10 Aug
  • can it be done with only one query?sinisa11 Aug
    • Re: can it be done with only one query?James Blackwell12 Aug
  • Re: can it be done with only one query?Markus Schulte11 Aug