Make SELECT MAX(col) return 0 rows

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

SQL Server 2000 Forums

Transact-SQL (2000)

Make SELECT MAX(col) return 0 rows

Author

Topic

Kristen
Test

22859 Posts

Posted - 2004-07-05 : 04:45:19

I want to have a SELECT that returns MAX(MyColumn) if something exists, otherwise return NO rows - so I can use it in a UNION:


SELECT	T_Sequence,
	[T_PrevSet] = COALESCE(T_PrevSet, 0)
FROM	@tabQtyBreak
WHERE	T_blnPriceBreakChange <> 0
UNION ALL
-- This to return 0 rows if ALL T_blnPriceBreakChange = 0
SELECT	[T_Sequence] = 999998,
	[T_PrevSet] = MAX(T_Sequence)
FROM	@tabQtyBreak
WHERE	T_blnPriceBreakChange <> 0
UNION ALL
SELECT	[T_Sequence] = 999999,
	[T_PrevSet] = 0
WHERE	NOT EXISTS (SELECT * FROM @tabQtyBreak WHERE T_blnPriceBreakChange <> 0)

but it seems that the MAX(T_Sequence) select always returns a row (with a NULL if there are no matching rows) ... which means I will have to convert the whole UNION to a SUBSELECT with a WHERE to get rid of the NULL value. But maybe there is a better way?

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-07-05 : 05:28:53

and if you add AND ([T_Sequence] is not null) in the where?

Go with the flow & have fun! Else fight the flow :)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-05 : 05:35:55

Does this work?

SELECT T_Sequence,
[T_PrevSet] = COALESCE(T_PrevSet, 0)
FROM @tabQtyBreak
WHERE T_blnPriceBreakChange <> 0
UNION ALL
-- This to return 0 rows if ALL T_blnPriceBreakChange = 0
SELECT [T_Sequence] = 999998,
[T_PrevSet] = T_Sequence
FROM (SELECT TOP 1 T_Sequence FROM @tabQtyBreak WHERE T_blnPriceBreakChange <> 0 ORDER BY T_Sequence DESC) as a
UNION ALL
SELECT [T_Sequence] = 999999,
[T_PrevSet] = 0
WHERE NOT EXISTS (SELECT * FROM @tabQtyBreak WHERE T_blnPriceBreakChange <> 0)

Duane.

Kristen
Test

22859 Posts

Posted - 2004-07-05 : 06:48:58

That did the job, thanks Duane

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources