select non unique 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

select non unique values

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2008-11-20 : 11:03:38

Hello

to select distinct records I use select distinct field1 from table1
this will return the unique values

I want to select the non unique values. How to do this?

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:07:55

simply use


select field1 from table1 group by field1 having count(*)>1

mmalaka
Starting Member

33 Posts

Posted - 2008-11-20 : 11:50:14

ok

I am trying to use that to do the following

I have table1 with Field1 and Field2

I have table2 with Field21 and field22

I need to update Field1 in table1 using the value of Field21 from table2 on the condition of table1.field2 = table2.field22 but note that field22 is not unique

Any advice?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 11:55:31

then you will oviously have more than 1 value of Field21 for each value of field22. which of those value needs to be updated to Field1 of table1?

mmalaka
Starting Member

33 Posts

Posted - 2008-11-20 : 11:58:58

I have duplicated values of field22
I need anyone of them

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 12:02:33

[code]UPDATE t1
SET t1.Field1=t2.MaxField21
FROM table1 t1
INNER JOIN (SELECT MAX(Field21) AS MaxField21,Field22
FROM Table2
GROUP BY Field22)t2
ON t2.Field22=t1.Field2
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources