select data - SQL Server Forums

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2014-04-03 : 06:53:03

thanks thanks stepson

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 07:01:09

You are welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources