asked
Sql Query Help
Hi Everyone, --Please find the attached script for table creation and table values I have the table like below cno cname phonenumber type 1 AAA 1234 P 1 AAA 3456 W 1 AAA 3456 H 2 BBB 5678 W 2 BBB 7894 H 3 CCC 1111 P 3 CCC 2222 H 4 DDD 8888 H I need to get the result like below[link text][1] cno cname phone 1 AAA 1234 2 BBB 5678 3 CCC 1111 4 DDD 8888 Here,I want to display phonenumber of customer If type is primary display primarynumber and if doesn't display work number otherwise display the home phonenumber [1]: /storage/temp/497-customer.txt
+1 for providing a working test data script. That certainly makes it easier to answer questions.
with customers as ( select cno, cname, phonenumber, type, row_number() over (partition by cno order by case when type = 'P' then 1 when type = 'W' then 2 when type = 'H' then 3 else 4 end) as rownum from customer ) select cno, cname, phonenumber, type from customers where rownum = 1; This works for SQL Server 2005 and later. Inside the common table expression customers, we run row_number() to create an order by cno, based on the type. We give P preference, followed by W, and then H. Any other type goes to the end--we ignore it. Finally, we get the results in which the rownum = 1. For each cno, we're guaranteed to have one and only one record with a rownum = 1. Note that if you have two records with the same cno and type (i.e., somebody somehow has two Primary phone numbers), which one gets selected is arbitrary.
Kevin, this is a great solution. I have never encountered this construct before so took a copy to play with. I have always understood that the CASE statement returns a value based on which part evaluates as true first. So, in your example the CASE returns a 1,2 or 3 to the ROW_NUMBER function. If this is the case, why do you not get an error in the way that you do when you specify a static value 1 or '1' ? Is the CASE processed differently when used in this way?
In the MSDN doc (
http://msdn.microsoft.com/en-us/library/ms189461(v=sql.105).aspx), it reads "When used in the context of a ranking window function, can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list." The ORDER BY part really is coming from the FROM clause; we're just transforming it a little bit is all. So in this case, it's actually different than specifying a constant. As a quick check to show that CASE by itself is not special: with vals as ( select n from ( values(1), (2), (3)) as v(n) ) select n, row_number() over (order by case when 1 = 1 then 1 else 0 end) from vals; You still get the same error as if you specify an integer. Perhaps this might have been an artifact of the old SQL method of ordering by an index rather than a column name or alias and that would have caused an unnecessary ambiguity (i.e., are you ordering by the number 3 or by the third column?).
Funny enough, when you replace one of the 1s with n, ex. when n=1, that actually works! +1 for clever statement :)
11 People are following this question.
