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.
Author |
Topic |
Rock_query
Yak Posting Veteran
55 Posts |
Posted - 2013-05-22 : 16:56:35
|
I copied a CASE function as a part of a WHERE function, then modified some column names to test this on the AdventureWorks2012 database.Here is the code:select top 5 ProductID, ActualCost from Production.TransactionHistory where case when ActualCost < 30.00 then 'Cheap' when ActualCost < 12.00 then 'Really Cheap' when ActualCost > 30.00 and ActualCost < 100.00 then 'Average' else 'Expensive' end = 'Average'The results are two columns with these values:Product ID: 894, 907, 916, 941, 945ActualCost: 89.88, 78.81, 38.95, 59.93 and 67.70I don't understand how this is working.1. Is SQL selecting the first 5 ProductIDs, then looking at their ActualCost? Or is SQL grouping the ActualCosts, then extracting the top 5 ProductIDs?2. Also, END = 'Average' implies that there should be a column displayed called Average right? |
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-05-22 : 17:08:26
|
No, columns come from the Select list.-Chad |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-05-22 : 17:21:22
|
Using a CASE expression in the WHERE clasue like that works, but it is a bit superfluous and makes the intent less clear (IMHO). 1. SQL is getting 5 random rows that statisfy the predicate.2. No. That that is just a predicate.Again, no need for the case expression: select top 5 ProductID, ActualCostfrom Production.TransactionHistorywhere ActualCost > 30.00 and ActualCost < 100.00 |
|
|
Rock_query
Yak Posting Veteran
55 Posts |
Posted - 2013-05-22 : 20:01:39
|
quote: Originally posted by Lamprey Using a CASE expression in the WHERE clasue like that works, but it is a bit superfluous and makes the intent less clear (IMHO). 1. SQL is getting 5 random rows that statisfy the predicate.2. No. That that is just a predicate.Again, no need for the case expression: select top 5 ProductID, ActualCostfrom Production.TransactionHistorywhere ActualCost > 30.00 and ActualCost < 100.00
Thank you Lamprey. |
|
|
|
|
|
|
|