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] 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 : /storage/temp/497-customer.txt
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.