Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2014
    Posts
    74
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Selecting a row by its highest value

    Hey all,

    I want to select a row by its highest value for a specific field (a field called 'price').

    I tried it with the following command, but it failed:

    SELECT * FROM products WHERE MAX(price) AND gender = 'Mens' AND type = 'T-Shirt';

    This is my table:

    +-----------------+--------------+------+-----+-------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-----------------+--------------+------+-----+-------------------+-------+
    | number | int(10) | YES | | NULL | |
    | gender | varchar(6) | YES | | NULL | |
    | type | varchar(25) | YES | | NULL | |
    | name | varchar(50) | YES | | NULL | |
    | brand | varchar(50) | YES | | NULL | |
    | colour | varchar(50) | YES | | NULL | |
    | material | varchar(50) | YES | | NULL | |
    | countryoforigin | varchar(50) | YES | | NULL | |
    | price | decimal(6,2) | YES | | NULL | |
    | cost | decimal(6,2) | YES | | NULL | |
    | adddate | timestamp | NO | | CURRENT_TIMESTAMP | |
    | weight | int(5) | YES | | NULL | |
    | sizetype | varchar(25) | YES | | NULL | |
    | sizexs | varchar(2) | YES | | NULL | |
    | sizes | varchar(2) | YES | | NULL | |
    | sizem | varchar(2) | YES | | NULL | |
    | sizel | varchar(2) | YES | | NULL | |
    | sizexl | varchar(2) | YES | | NULL | |
    +-----------------+--------------+------+-----+-------------------+-------+

    Does anyone know why it failed?

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,944
    Thanks
    2
    Thanked 170 Times in 165 Posts
    max(price) needs to be part of the select clause, not the where clause and it's best to specify the exact fields you want rather than using *.

    Code:
    select number, gender, type, name, max(price) as price where gender = 'Mens' AND type = 'T-Shirt';

  • #3
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,147
    Thanks
    2
    Thanked 333 Times in 325 Posts
    Code:
    SELECT * FROM products WHERE gender = 'Mens' AND type = 'T-Shirt' ORDER BY price DESC LIMIT 1
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •