Distinct values - 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

General SQL Server Forums

New to SQL Server Programming

Distinct values

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-21 : 06:44:11

I have the following table (dbo.tb1) as shown below;

Patid eventdate consid staffid techcode
1 11/01/09 4 4 100
1 12/01/09 5 3 101
2 10/10/10 10 18 105
2 10/10/10 10 17 105

so I have,

SELECT DISTINCT patid, eventdate, consid, techcode
FROM         dbo.tb1

Results

Patid eventdate consid techcode
1 11/01/09 4 100
1 12/01/09 5 101
~~2 10/10/10 10 105~~
2 10/10/10 10 105

However, what I want to do is if the patid, eventdate, consid, techcode is the same REMOVE all the 2 records from the result list..

Therefore, I want a result of

Patid eventdate consid techcode
1 11/01/09 4 100
1 12/01/09 5 101
~~2 10/10/10 10 105~~
~~2 10/10/10 10 105~~

Any help please.. many thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 06:50:06

[code]select
patid, eventdate, consid, techcode
from
dbo.tb1
group by
patid, eventdate, consid, techcode
having
count(*) = 1[/code]Do you want to remove if there are 3 identical rows? This assumes so.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-21 : 07:17:19

yes, but i have one other problem..
Lets assume, I want to return

Patid eventdate consid techcode prodcode
1 11/01/09 4 100 13
1 12/01/09 5 101 14
2 10/10/10 10 105 18
2 10/10/10 10 105 20

I changed the query to

select
patid, eventdate, consid, techcode, prodcode
from
dbo.tb1
group by
patid, eventdate, consid, techcode, prodcode
having
count(*) = 1

However, the result is incorrect as it brings all the other records

Patid eventdate consid techcode prodcode
1 11/01/09 4 100 13
1 12/01/09 5 101 14
2 10/10/10 10 105 18
2 10/10/10 10 105 20

Any help please!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-21 : 07:27:31

If you are on SQL 2005 or later, you can use a CTE as shown below:


with CTE as
(
select
   count(*) over (partition by patid, eventdate, consid, techcode) as CN,
   *
from
   dbo.tb1
)
select
   patid, eventdate, consid, techcode, prodcode
from
   CTE
where
   CN = 1

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-21 : 09:53:36

Thank you .. Sorted

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources