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 2005 Forums
 Transact-SQL (2005)
 select data

Author  Topic 

Gekko
Yak Posting Veteran

63 Posts

Posted - 2014-04-03 : 04:15:23
hallo

pleas help me.
I have this table.

TabSale
numcustomer.......salesman....branch
1.................John.........100
1.................John.........200
1.................Georg........500
2.................Smith........900
2.................Smith........500
2.................Alan.........700
3..................Xena........900
3..................Xena........600
3..................Xena........200



I need this select :

numcustomer.....salesman
1..............John
1..............Georg
2...............Smith
2...............Alan

I need to select numcustomer where are 2 salesmans.

thanks boys

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 04:51:51
[code]
;with TabSale
AS
(select 1 as numcustomer,'John' as salesman,100 as branch union all
select 1,'John',200 union all
select 1,'Georg',500 union all
select 2,'Smith',900 union all
select 2,'Smith',500 union all
select 2,'Alan',700 union all
select 3,'Xena',900 union all
select 3,'Xena',600 union all
select 3,'Xena',200)

select
numCustomer
,salesman
from TabSale
where numCustomer in (select numCustomer
from
(
select
numCustomer
,rank() Over(Partition by numCustomer Order by salesman) as countCustomer
from TabSale)A
where A.countCustomer>1)
Group by numCustomer, SalesMan
[/code]


sabinWeb MCP
Go to Top of Page

Gekko
Yak Posting Veteran

63 Posts

Posted - 2014-04-03 : 06:53:03
thanks thanks stepson
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 07:01:09
You are welcome!


sabinWeb MCP
Go to Top of Page
   

- Advertisement -