Help using MIN()

[Schmoopy]

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:

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!

[traq]

Subqueries!

SELECT `price` AS `cheapest`, `size` 
FROM `product_sizes` 
WHERE `product` = 'test' 
AND `price` = (SELECT MIN(`price`) FROM `product_sizes`)

[Schmoopy]

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:

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:

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!

[traq]

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.

[Schmoopy]

Hey,

Here's what's generated from the table:

# --------------------------------------------------------
# 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

[james438]

Could it be due to the use of single quotes mixed with back tics in your query?

[Schmoopy]

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

[james438]

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.

[Schmoopy]

Ah you legend!

Thank you

Working now.

Thanks to both of you!

[james438]

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.