List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:December 3 1999 11:03am
Subject:RE: Query Question!
View as plain text  
>>>>> "Simon" == Simon Freeman <simon.freeman@stripped> writes:

Simon> Not sure but you are not asking for one exclusive answer in the result thus
Simon> no guarantee what you will get it seems.  Another way around is to take the
Simon> top record of an ordered query.

Simon> i.e. select id,soort from sinterklass order by bedrag ASC (or DSC depending
Simon> upon whether you want the highest or lowest)

Simon> The first record should be the highest or lowest.
Simon> You may then be able to limit the answers to the top 1 using a limit command
Simon> but I cannot remember the syntax.

Simon> What do you want to do if you get more than one highest record. i.e. if
Simon> there were two bedrag 10's.


Simon> Can someone tell me how to get the max 'bedrag' of each 'soort' including
Simon> the correct id?

mysql> select * from sinterklaas;
Simon> +----+-------+--------+
Simon> | id | soort | bedrag |
Simon> +----+-------+--------+
Simon> |  1 | Sint  |      5 |
Simon> |  2 | Sint  |     10 |      <<----- this row
Simon> |  3 | Piet  |      7 |
Simon> |  4 | Piet  |      4 |
Simon> |  5 | Sint  |      8 |
Simon> |  6 | Piet  |      9 |      <<----- this row
Simon> +----+-------+--------+
Simon> 6 rows in set (0.02 sec)

Hi!

You can find an explanation for this query and a solution to the above 
problem in the MySQL manual, sections 'Functions for use with GROUP BY 
clauses'.  (The problem is that the it's not defined in SQL or MySQL
what not-summary-columns that are not included in the GROUP BY clause
should return and in the general case, its impossible to include the
'correct' id for queries like this.  Simply don't do use this feature
if you don't know how MySQL resolves queries like this!)

Regards,
Monty
Thread
mysqldump - Dumping Tables Using Wildcard CharactersEric Moore2 Dec
  • Query Question!james ho2 Dec
    • RE: Query Question!Simon Freeman2 Dec
      • RE: Query Question!Michael Widenius3 Dec
  • Re: mysqldump - Dumping Tables Using Wildcard Characterssinisa2 Dec