Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Help using MIN()

  1. #1
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default Help using MIN()

    Hi guys,

    Having some trouble using the MIN() function in SQL.

    I have a table laid out like so:

    product size price

    test A2 50
    test A3 35
    test A4 25

    And what I want to find is the size of the cheapest product.

    My SQL looks like this atm:

    Code:
    SELECT MIN(price) AS cheapest, size
    FROM product_sizes WHERE product = 'test'
    This gives me the right price (25), but the size that comes back is not associated with that row (it returns A2).

    Any ideas on how I can resolve this?

    Thanks!
    Last edited by Schmoopy; 06-12-2011 at 11:31 AM.

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Subqueries!

    Code:
    SELECT `price` AS `cheapest`, `size` 
    FROM `product_sizes` 
    WHERE `product` = 'test' 
    AND `price` = (SELECT MIN(`price`) FROM `product_sizes`)
    Last edited by traq; 06-12-2011 at 12:58 AM.

  3. The Following User Says Thank You to traq For This Useful Post:

    Schmoopy (06-12-2011)

  4. #3
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    Hi traq,

    Thanks very much for that. I can see it works with the table provided, but the actual table I have is slightly different to that one (I didn't think significantly), and the query doesn't seem to be working on that oddly.

    I've attached an image of how my table actually looks, I only renamed certain fields so that it was easier to follow for you guys.

    Based on what my table looks like, I changed your query to:

    Code:
    SELECT `current_price` AS `cheapest`, `size` 
    FROM `product_sizes` 
    WHERE `product_id` = 'grrr'
    AND `current_price` = (SELECT MIN(`current_price`) FROM `product_sizes`)
    But this gives me back nothing =/

    If I take out the first part of the WHERE clause (product_id = 'grrr'), then the query comes back with the right data:

    Code:
    cheapest        size
    15              m
    If you could let me know why this part of the query might not be working I'd love to hear it.

    Thanks for all your help so far!

    Last edited by Schmoopy; 06-12-2011 at 08:06 AM.

  5. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    umm... I don't know. it looks correct. if you could do an sql dump of the table, and the relevant records, I could test it out.

  6. #5
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    Hey,

    Here's what's generated from the table:

    Code:
    # --------------------------------------------------------
    # Host:                         localhost
    # Server version:               5.5.8-log
    # Server OS:                    Win32
    # HeidiSQL version:             6.0.0.3603
    # Date/time:                    2011-06-12 10:15:54
    # --------------------------------------------------------
    
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    
    # Dumping structure for table antidote.product_sizes
    CREATE TABLE IF NOT EXISTS `product_sizes` (
      `product_id` varchar(50) NOT NULL DEFAULT '',
      `size` varchar(50) NOT NULL DEFAULT '',
      `gender` enum('Mens','Womens') NOT NULL DEFAULT 'Mens',
      `current_price` float NOT NULL,
      `old_price` float NOT NULL,
      `qty` int(10) DEFAULT NULL,
      `qty_threshold` int(10) DEFAULT NULL,
      PRIMARY KEY (`product_id`,`size`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    # Dumping data for table antidote.product_sizes: ~4 rows (approximately)
    /*!40000 ALTER TABLE `product_sizes` DISABLE KEYS */;
    INSERT INTO `product_sizes` (`product_id`, `size`, `gender`, `current_price`, `old_price`, `qty`, `qty_threshold`) VALUES
    	('cloud_nine_black', 'm', 'Mens', 15, 25, 20, NULL),
    	('grrr', 'A2', 'Mens', 50, 0, NULL, NULL),
    	('grrr', 'A3', 'Mens', 35, 0, NULL, NULL),
    	('grrr', 'A4', 'Mens', 25, 0, NULL, NULL);
    /*!40000 ALTER TABLE `product_sizes` ENABLE KEYS */;
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    Many thanks
    Last edited by Schmoopy; 06-12-2011 at 09:16 AM. Reason: Removed database creation code

  7. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,282
    Thanks
    97
    Thanked 104 Times in 102 Posts

    Default

    Could it be due to the use of single quotes mixed with back tics in your query?
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #7
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    I don't see where that would be causing a problem in this case.

    If I had used backticks instead of single quotes around the product ID, then yes, but otherwise it all looks correct to me.

    Could you be a little more specific on what part you would change?

    Thanks

  9. #8
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,282
    Thanks
    97
    Thanked 104 Times in 102 Posts

    Default

    Code:
    SELECT `current_price` AS `cheapest`, `size` 
    FROM `product_sizes` 
    WHERE `current_price` = (SELECT MIN(`current_price`) FROM `product_sizes` WHERE `product_id` = 'grrr')
    In your original query it found the MIN result of your table and then ruled it out because `product_id` != 'grrr'.

    As to being more specific I had never tried mixing single quotes and back tics in a query, so I wasn't sure if that caused some sort of conflict.
    Last edited by james438; 06-12-2011 at 11:23 AM. Reason: added last sentence.
    To choose the lesser of two evils is still to choose evil. My personal site

  10. The Following User Says Thank You to james438 For This Useful Post:

    Schmoopy (06-12-2011)

  11. #9
    Join Date
    Sep 2008
    Location
    Bristol - UK
    Posts
    842
    Thanks
    32
    Thanked 132 Times in 131 Posts

    Default

    Ah you legend!

    Thank you

    Working now.

    Thanks to both of you!

  12. #10
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,282
    Thanks
    97
    Thanked 104 Times in 102 Posts

    Default

    Glad to help . I was surprised traq didn't beat me to it! He is a lot more skilled than I am.

    Doing an sql dump really helped. Gonna sleep now. It is 6:50am here.
    To choose the lesser of two evils is still to choose evil. My personal site

Bookmarks

Posting Permissions

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