Dyntex.net Support Centre: MySQL Reference Manual p.169

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 3: Tutorial Introduction 169 +---------+ | 4 | +---------+ 3.5.2 The Row Holding the Maximum of a Certain Column \Find number, dealer, and price of the most expensive article." In ANSI SQL this is easily done with a subquery: SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop) In MySQL (which does not yet have subselects), just do it in two steps: 1. Get the maximum price value from the table with a SELECT statement. 2. 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 rst row using the MySQL specic LIMIT clause: SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1 NOTE: If there are several most expensive articles (for example, each 19.95) the LIMIT solution shows only one of them! 3.5.3 Maximum of Column per Group \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 | +---------+-------+

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide