March 29, 2016 at 7:28 pm
Hi,
i have a table that has data as data as the following:
CustNumber Name location division
1 Test1 MA E
2 Test2 ME E
2 Test2 ME G
3 Test3 CA E
3 Test3 CA G
I would like to have a select statement that if there are 2 the same records but have different Division G and E, i only want to get the G one. The result should be:
CustNumber Name location division
1 Test1 MA E
2 Test2 ME G
3 Test3 CA G
How can i do this? Please advise.
Thanks
Dee
March 29, 2016 at 9:21 pm
Something like this
SELECT *
FROM (SELECT *,
Row_number()
OVER(
partition BY custnumber
ORDER BY division desc)rid
FROM yourtable)t
WHERE rid = 1
EDIT:What happens if there is a CustNumber with 3 division E,F,G or a CustNumber with 2 distinct divisions but having no G ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
March 29, 2016 at 10:45 pm
create table dbo.SSC1(CustNumber int, Name varchar(20), location varchar(10), division varchar(10))
insert into dbo.SSC1(CustNumber,Name,location,division)
VALUES
(1, 'Test1', 'MA', 'E'),
(2, 'Test2', 'ME', 'E'),
(2, 'Test2', 'ME' ,'G'),
(3, 'Test3', 'CA', 'E'),
(3, 'Test3', 'CA', 'G')
;with cte as(
select ROW_NUMBER() over (PARTITION by custNumber order by CustNumber) RowId,CustNumber,Name,location,division
from SSC1
)
select CustNumber,Name,location,division from cte c1
where (NOT exists( Select top 1 1 from cte c2 where c1.CustNumber = c2.CustNumber and c2.RowId > 1))
OR
((exists( Select top 1 1 from cte c2 where c1.CustNumber = c2.CustNumber and c2.RowId > 1)) and c1.division = 'G')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply