MIN - Aggregate/non-aggregate - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN - Aggregate/non-aggregate

Author

Topic

acbarberi
Starting Member

11 Posts

Posted - 2009-11-19 : 19:20:50

Here is my SQL code:
select title, min(retail) from books
where category = 'COMPUTER'
group by title;

I need to find out the title of the lowest retail value computer book in the database.

If I type this:
select min(retail) from books
where category = 'COMPUTER';

It shows me the lowest retail value, but I need the title as well. When I add the title field to the query and the group by, it shows me all of the computer books.

How do I fix this problem?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-19 : 19:24:50

select title, retail
from books
where retail = (select min(retail) from books where category = 'COMPUTER')
and category = 'COMPUTER'

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

acbarberi
Starting Member

11 Posts

Posted - 2009-11-19 : 19:28:09

Wow that is awesome. This is for a class and we haven't talked about subqueries yet so they won't let me use that code. Is there another way to do it along the lines of where I was going?

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-20 : 00:57:35

Try this..

SELECT TOP 1 WITH TIES title, retail
FROM #samp
WHERE title LIKE 'COMPUTER'
ORDER BY retail ASC

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 02:20:47

quote:
Originally posted by kbhere

Try this..

SELECT TOP 1 WITH TIES title, retail
FROM #samp
WHERE title LIKE 'COMPUTER'
ORDER BY retail ASC

Balaji.K

WHERE title LIKE 'COMPUTER'

can be rewritten

WHERE title = 'COMPUTER'

Madhivanan

Failing to plan is Planning to fail

kbhere
Yak Posting Veteran

58 Posts

Posted - 2009-11-20 : 05:49:04

For String comparison "LIKE" is the Best operator rather than "="..
The query posted by me is an optimized one..
So, use LIKE operator for any string related comparisons..

Balaji.K

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-20 : 07:28:43

quote:
Originally posted by kbhere

For String comparison "LIKE" is the Best operator rather than "="..
The query posted by me is an optimized one..
So, use LIKE operator for any string related comparisons..

Balaji.K

Do you have samples to prove this?

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-11-20 : 08:21:54

No performance difference I can see. Both can use an index. no difference in the execution plan. No significant difference in cpu time. Test set


/*
DROP TABLE #samp
CREATE TABLE #samp (
	[ISBN] INT
	, [title] VARCHAR(255)
	, [retail] MONEY
	)

INSERT #samp ([ISBN], [title], [retail])
SELECT
	CAST(CAST(NEWID() AS VARBINARY) AS INT)
	, CASE WHEN RIGHT(d.[unq], 1) = '1' THEN 'Computer' ELSE d.[unq] END
	, ABS(CAST(CAST(NEWID() AS VARBINARY) AS MONEY))
FROM
	(
	SELECT CAST(CAST(CAST(NEWID() AS VARBINARY) AS INT) AS VARCHAR(255)) AS [unq]
	)
	d
	CROSS JOIN information_schema.columns a
	CROSS JOIN information_schema.tables b

CREATE CLUSTERED INDEX IX_name ON #samp (title)
*/

SELECT COUNT(*) FROM #samp AS [total Rows]
SELECT COUNT(*) FROM #samp AS [Rows with computer] WHERE title = 'Computer' 

SET STATISTICS IO ON
SET STATISTICS TIME ON

SELECT TOP 1 WITH TIES title, retail 
FROM #samp 
WHERE title LIKE 'Computer' 
ORDER BY retail ASC

SELECT TOP 1 WITH TIES title, retail 
FROM #samp 
WHERE title = 'Computer' 
ORDER BY retail ASC

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources