duplicate records appears - 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

duplicate records appears

Author

Topic

damuchinni
Starting Member

29 Posts

Posted - 2009-01-27 : 13:25:03

Hi,

Even though im using distinct im getting quplicate records.

1) select * from caseuser where caseuser.case_id = 142506

result:
case_userid case_id role_cd from_userid assgn_userid assgn_dtm case_user_role_cd lst_updt_usrid lst_updt_dtm is_deleted_ind

241145 142506 100007 NULL 100375 2009-01-23 00:00:00.000 P 100375 2009-01-23 13:49:19.000 N
241242 142506 100004 NULL 100397 2009-01-27 21:13:03.000 P 100375 2009-01-27 21:13:04.000 N
241249 142506 100004 NULL 100379 2009-01-27 21:14:38.000 RA 100397 2009-01-27 00:00:00.000 N

2)select * from users

userid firstname middlename lastname jobtitle
100375 Patrick NULL Nicholson Data Entry operator
100376 Maura E Hametz Tele Underwriter
100377 Thomas P Murray Tele Underwriter
100378 Leonard N Ashley Case Coordinator
100379 David NULL Tucker Case Coordinator
100380 Wilbur NULL Zelinsky Junior Underwriter
100381 Alan NULL Rayburn Junior Underwriter
100382 Edward NULL Callary Case Coordinator Manager
100383 Edwin NULL Lawson Underwriter Manager

3) select * from userrole

role_cd sl_role_dsc
100002 UnderWriter
100004 Case Coordinator
100006 Tele underwriter
100007 DataEntryoperator
100010 1035 Specialist
100014 Medical Director
100015 Quality Assurance

and i worte a join query to get dinstinct data but im geting the duplicate values

SELECT distinct(CASEUSER.ROLE_CD),USERS.FIRSTNAME,USERS.LASTNAME, (select sl_role_dsc from userrole where role_cd =CASEUSER.ROLE_CD)
AS RoleDesc,USERS.USERID,CASEUSER.ASSGN_DTM FROM CASEUSER INNER JOIN USERS ON CASEUSER.ASSGN_USERID = USERS.USERID
AND CASEUSER.CASE_ID =142506 AND CASEUSER.CASE_USER_ROLE_CD IN ('P','RA')
AND (CASEUSER.IS_DELETED_IND <> 'Y' OR CASEUSER.IS_DELETED_IND IS NULL) group by CASEUSER.ROLE_CD,USERS.FIRSTNAME,USERS.LASTNAME
,USERS.USERID,CASEUSER.ASSGN_DTM ORDER BY CASEUSER.ROLE_CD desc,CASEUSER.ASSGN_DTM desc

100007 Patrick Nicholson DataEntryoperator 100375 2009-01-23 00:00:00.000
100004 David Tucker Case Coordinator 100379 2009-01-27 21:14:38.000
100004 Phillip Frost Case Coordinator 100397 2009-01-27 21:13:03.000

please can any body help me

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 13:37:45

Search for ROW_Number() Over(Partition by ....) here. There are lots of examples.

damuchinni
Starting Member

29 Posts

Posted - 2009-01-27 : 14:53:32

Thanks a lot sodeep

i made it .

select distinct(t.RoleDesc),t.FIRSTNAME,t.LASTNAME,t.role_cd,t.RoleDesc,t.USERID,max(t.ASSGN_DTM) as maxs from (
SELECT ROW_NUMBER() OVER (PARTITION BY CASEUSER.ROLE_CD ORDER BY CASEUSER.ASSGN_DTM desc ) AS ROWID,
CASEUSER.ROLE_CD as role_cd,USERS.FIRSTNAME,USERS.LASTNAME, (select sl_role_dsc from userrole where role_cd =CASEUSER.ROLE_CD)
AS RoleDesc,USERS.USERID,CASEUSER.ASSGN_DTM FROM CASEUSER INNER JOIN USERS ON CASEUSER.ASSGN_USERID = USERS.USERID
AND CASEUSER.CASE_ID =142506 AND CASEUSER.CASE_USER_ROLE_CD IN ('P','RA')
AND (CASEUSER.IS_DELETED_IND <> 'Y' OR CASEUSER.IS_DELETED_IND IS NULL)
) t where ROWID=1 group by t.ROLE_CD,t.FIRSTNAME,t.LASTNAME,t.USERID,t.ASSGN_DTM,t.RoleDesc
order by t.maxs asc

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-27 : 15:54:01

[code]SELECT T.ROLEDESC,
T.FIRSTNAME,
T.LASTNAME,
T.ROLE_CD,
T.ROLEDESC,
T.USERID,
T.ASSGN_DTM
FROM (SELECT ROW_NUMBER()
OVER(PARTITION BY CASEUSER.ROLE_CD ORDER BY CASEUSER.ASSGN_DTM DESC) AS ROWID,
CASEUSER.ROLE_CD AS ROLE_CD,
USERS.FIRSTNAME,
USERS.LASTNAME,
(SELECT Distinct SL_ROLE_DSC
FROM USERROLE
WHERE ROLE_CD = CASEUSER.ROLE_CD) AS ROLEDESC,
USERS.USERID,
CASEUSER.ASSGN_DTM
FROM CASEUSER
INNER JOIN USERS
ON CASEUSER.ASSGN_USERID = USERS.USERID
AND CASEUSER.CASE_ID = 142506
AND CASEUSER.CASE_USER_ROLE_CD IN ('P','RA')
AND (CASEUSER.IS_DELETED_IND <> 'Y'
OR CASEUSER.IS_DELETED_IND IS NULL)) T
WHERE T.ROWID = 1
ORDER BY T.ASSGN_DTM [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources