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 |
|
spock
Starting Member
35 Posts |
 Posted - 2002-01-24 : 20:56:13
|
| Hi declare @s varchar(4) -- 'asc'/'desc'set @s = 'ASC'select *from empt_emporder by case when @s = 'asc' then emp_id end, emp_id desc when variable @s is set to asc then the query should contain an orber by clause likeorder by emp_id,emp_id desc.but it is obviously not that so.can anyone explain me how it works for @s = asc (i was able to figure out how it worked for values other than asc).thankskaushik |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-01-24 : 21:13:14
|
| I think what you want isdeclare @s varchar(4) -- 'asc'/'desc'set @s = 'ASC'select *from empt_emporder by case when @s = 'asc' then emp_id else null end, case when @s = 'desc' then emp_id else null end descThe second statement can be just emp_id desc if you wish.It works by @s = 'desc' making the first order by statement irrelevant as all values are the same.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
spock
Starting Member
35 Posts |
Posted - 2002-01-24 : 21:28:29
|
nr, this query was actually taken from one of your old posts !  quote:
order by case when @s = 'asc' then emp_id else null end, case when @s = 'desc' then emp_id else null end desc
the above piece is clear.but when the second case statement is replaced by just emp_id desc likeorder by case when @s = 'asc' then emp_id else null end,emp_id descto be frank i still dont get it  . when i substitute the value asc for @s then why does the emp_id desc keyword get ignored ? when @s='desc' then stmt becomes order by null,emp_id desc.that is clear.but i cant seem to grasp what happens when @s=asc.could you please clarify ?thankskaushik |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-24 : 23:55:34
|
quote:
when i substitute the value asc for @s then why does the emp_id desc keyword get ignored ?
Well, it does not really get ignored... It just has no effect on the outcome of the query because of the way ORDER BY clause works.The most important thing to remember is that ORDER BY clause is processed from left to right. This means that initially the rows are sorted on the left-most criteria. Only in cases where the first sort criterion is the same for several records, the second criterion (if there is one) comes into play. If that criterion is also the same for multiple records, SQL Server will look for the next and so on... For example, lets forget your query for a moment and look at Northwind database. Run the following from Query Analyzer: use northwindgo select customerid, orderid from orders where customerid in ('bolid', 'centc', 'franr') order by customerid asc, --first criterionorderid desc --second criterionThe result will look like this: customerid orderid ---------- ----------- BOLID 10970BOLID 10801BOLID 10326CENTC 10259FRANR 10971FRANR 10860FRANR 10671(7 row(s) affected) Notice that since customerid appears first in the ORDER BY clause, SQL Server first tries to sort the records by that field. However, when a customer has multiple orders (as both BOLID and FRANR do in this case), SQL Server needs to sort the records within each customer id, so that's when it looks at the second criterion (orderid). Now, going back to your question... Essentially you have ...ORDER BY emp_id asc ,emp_id descSo the first criterion is evaluated, and the records are sorted in ascending order by emp_id. As long as emp_id is unique, SQL Server does not need to bother looking at the second sorting criterion at all -- it simply sorts on the first. However, if multiple records have the same emp_id, your query instructs SQL Server to try to order records within this emp_id in descending order by the next sorting criteria... But the second criterion is the same as the first one: emp_id, therefore the records in question can not be distinguished by the second criterion either. There are no more criteria specified for the ORDER BY clause. As a result, the second criterion has no impact on how the records are returned, making it look like it is ignored alltogether. I hope this makes some sense... Edited by - izaltsman on 01/24/2002 23:57:35 |
|
|
|
spock
Starting Member
35 Posts |
Posted - 2002-01-25 : 02:57:53
|
izaltsman,thanks for the great info. but then i have one more minor issue.quote:
So the first criterion is evaluated, and the records are sorted in ascending order by emp_id. As long as emp_id is unique, SQL Server does not need to bother looking at the second sorting criterion at all -- it simply sorts on the first.
when i tried to run the queryselect * from employee_table order by emp_id asc,emp_id desc the server should sort on emp_id asc and since emp_id being unique should process no further.but i get an error saying columns in order by should be unique. but as far as i understand the same is the processing that goes on when the case statement is present.so why does the error not crop up then ?thankskaushik |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-01-25 : 08:52:51
|
| Basically, when you have the same criteria hardcoded multiple times in the ORDER BY statement, SQL Server realizes right away that the second criterion will NEVER make any difference. So it assumes you must have made a mistake and throws an error. However, if you use a function or a CASE expression in your criteria, the result returned by the function may vary, so the second criteron might prove useful (e.g. in your original query the second criteron was used to sort records when @s <> 'asc')In other words, when you hardcode the same sorting criteria multiple times, SQL Server tries to be smart and doesn't allow this to save you the cost of unnecessary processing. But when you are using functions or conditionals, there is a possibility that additional sorting criteria may be used, so no error is generated. |
|
|
|
|
|
|
|
|
case statement help