7
SELECT MIN(retail)
FROM books
WHERE category = 'COMPUTER'

works fine, but when I include title in select like:

SELECT MIN(retail), title
FROM books
WHERE category = 'COMPUTER'

it doesn't. Why? How to make it work?

Mark Hurd
  • 10,665
  • 10
  • 68
  • 101
user490735
  • 755
  • 2
  • 9
  • 18

2 Answers2

9

Rhys's answer is correct, if that is what you mean, but you might have wanted the title(s) where retail=MIN(retail), and that wording suggests how to get that answer:

SELECT title, retail
FROM books
WHERE category = 'COMPUTER'
 AND retail = (SELECT MIN(retail) FROM books WHERE category = 'COMPUTER')

To reduce duplication you can use a WITH clause (if you're using a recent version of SQL):

;WITH ComputerBooks AS (
  SELECT title, retail
  FROM books
  WHERE category = 'COMPUTER')
SELECT title, retail
FROM ComputerBooks
WHERE retail = (SELECT MIN(retail) FROM ComputerBooks)

Sample I used to confirm syntax.

Community
  • 1
  • 1
Mark Hurd
  • 10,665
  • 10
  • 68
  • 101
  • 1
    Why does it require such duplicated constructions? – user490735 Apr 14 '11 at 20:16
  • It looks like I have to specify condition for each column, and then more general condition (title FROM books WHERE category = 'COMPUTER') is narrowed down to a more specific. But why not have syntax allowing to specify other columns from the record retrieved given a specific condition for one column? – user490735 Apr 14 '11 at 20:23
6

MIN applies to a group of records, so you need to tell it which group of records you mean.

If you mean for each title, show the minimum of retail, then you need:

SELECT MIN(retail), title FROM books
WHERE category = 'COMPUTER'
GROUP BY title
Rhys Gibson
  • 161
  • 1
  • 6