select query with no dupliactes

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 query with no dupliactes

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-12 : 01:16:17

HI All,

I come across the situation where i need to fetch distinct record based on latest value of another column

Query which i am using:

select distinct UPPER(RTRIM(SUBSTRING(WindowsUserName,4,100)))USERNAME, NameGiven, NameFamily, Email, WWID, '002010', '61' + RTRIM(SUBSTRING(MobileNumber,2,100)), CASE WHEN MobileNumber IS NULL THEN 0 ELSE 1 END, NULL
from juice.dbo.emp e
where e.WWID not in (SELECT WWID FROM users) and e.Email not in (SELECT Email FROM users WHERE Email is not NULL) and e.WindowsUserName not in (SELECT Username FROM users) and e.IsActive = 1 and LEN(e.WindowsUserName) > 3 and RTRIM(SUBSTRING(e.WindowsUserName,4,100)) not in (select username from users where username is not NULL)

Here i need to select only one "USERNAME" of latest WWID for duplicate rows.

Example:
USERNAME|NAME|LASTNAME|EMAIL|WWID|YEAR|TO|FROM|UPTO|

AALVES12 Ana Vitr Alves a@a.com 1010780 2010 NULL 0 NULL
AANDRAD2 DILSON ANDR b@b.com 75365260 2010 NULL 0 NULL
ATSCHANN Au Tschann c@c.com 702050820 2010 NULL 0 NULL
ATSCHANN Au Tschann c@c.com 702051362 2010 NULL 0 NULL

Here the row 3 and 4 is having a duplicate user name but with different WWID i.e 702050820 and 702051362 As 702051362 is the latest value i need select this row in the out put.

Pls help me

Thanks,
Gangadhar

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 01:39:16

select * from (
select*,row_number()over(partition by USERNAME order by WWID desc) from yourtable
)T where rid=1

PBUH

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-12 : 02:45:57

Hi Sachin,

Thanks for immediate reply.

Where i need to use this query row_number()over(partition by USERNAME order by WWID desc

when i used within that query i am getting with the syntax error.

Syntax error near from.

If could please give the full query when i need to place will be helpful.

Thanks,
Gangadhar

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-12 : 02:56:28

Hi all,

I am using with this query

select * from
(select distinct UPPER(RTRIM(SUBSTRING(WindowsUserName,4,100)))username, NameGiven, NameFamily, Email, WWID, '002010' a , '61' + RTRIM(SUBSTRING(MobileNumber,2,100)), CASE WHEN MobileNumber IS NULL THEN 0 ELSE 1 END, NULL,
row_number()over(partition by WindowsUserName order by WWID desc)rid
from juice.dbo.emp e
where e.WWID not in (SELECT WWID FROM users) and e.Email not in (SELECT Email FROM users WHERE Email is not NULL) and e.WindowsUserName not in (SELECT Username FROM users) and e.IsActive = 1 and LEN(e.WindowsUserName) > 3 and RTRIM(SUBSTRING(e.WindowsUserName,4,100)) not in (select username from users where username is not NULL))T
where rid=1

But i am getting this error:

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 7 of 'T'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 8 of 'T'.
Msg 8155, Level 16, State 2, Line 1
No column was specified for column 9 of 'T'.

Please help me

Thanks,
Gangadhar

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 02:59:12

Are you using SQL 2005?

PBUH

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2010-11-12 : 06:37:50

yes i am using SQL server 2005 EE

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-12 : 06:43:09

Try:

select * from
(select distinct UPPER(RTRIM(SUBSTRING(WindowsUserName,4,100)))username, NameGiven, NameFamily, Email,
WWID, '002010' a ,
'61' + RTRIM(SUBSTRING(MobileNumber,2,100)) COL1,
CASE WHEN MobileNumber IS NULL THEN 0 ELSE 1 END COL2,
NULL COL3,
row_number()over(partition by WindowsUserName order by WWID desc) rid
from juice.dbo.emp e
where e.WWID not in (SELECT WWID FROM users) and e.Email not in
(SELECT Email FROM users WHERE Email is not NULL) and e.WindowsUserName not in
(SELECT Username FROM users) and e.IsActive = 1 and LEN(e.WindowsUserName) > 3 and RTRIM(SUBSTRING(e.WindowsUserName,4,100))
not in (select username from users where username is not NULL))T
where rid=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources