Finding displaying and deleteing dulpicate records

HI all,
I am trying to find duplicate records ina database that I have taken over. I
am using group bys and count() to find the sups.
I also realise that we could have a problem with misspellings and hterefore
not have a duplicate as in Joe Soape, Jo Soap, Joe Sope Joe Soap. These
couldpossible be sups of the same. I would terefore assume to allow for user
to decide what is dups and what to keep.
Are there any other more acceptable ways and means of doing this.
THis a a sample of what I use
SELECT Name, address1, address2,city, COUNT(*) as numbofdups FROM reinsurer
GROUP BY Name, address1, address2, city HAVING COUNT(*) > 1
Thanks
RobertIf you are using SQL 2005 developer / enterprise edition you could use the
Fuzzy Grouping / Term Extraction transformations in Integration Services and
work it out through that.
Aside from that you need to role your own in T-SQL, there are functions like
SOUNDEX that help to an extent but aren't that brilliant.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:u0uuiXbbGHA.3352@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I am trying to find duplicate records ina database that I have taken over.
> I
> am using group bys and count() to find the sups.
> I also realise that we could have a problem with misspellings and
> hterefore
> not have a duplicate as in Joe Soape, Jo Soap, Joe Sope Joe Soap. These
> couldpossible be sups of the same. I would terefore assume to allow for
> user
> to decide what is dups and what to keep.
> Are there any other more acceptable ways and means of doing this.
> THis a a sample of what I use
> SELECT Name, address1, address2,city, COUNT(*) as numbofdups FROM
> reinsurer
> GROUP BY Name, address1, address2, city HAVING COUNT(*) > 1
> Thanks
> Robert
>
>

Find values from record that had a max value in a group by

I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.

If you have a table like this:

key date item 1 20070101 Apple 1 20070202 Banana 1 20070303 Cherry

The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).

You would like to do something like:

Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc

from Table

group by Key.

any suggestions?

hans

You can do the same funciton in SQL Server 2005..

Code Snippet

Create Table #data (

[key] Varchar(100) ,

[date] Varchar(100) ,

[item] Varchar(100)

);

Insert Into #data Values('1','20070101','Apple');

Insert Into #data Values('1','20070202','Banana');

Insert Into #data Values('1','20070303','Cherry');

Insert Into #data Values('2','20070101','Apple');

Insert Into #data Values('2','20070202','Banana');

Code Snippet

--Over all

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data

)

Select [key],[date],[item] from CTE Where rank=1

Code Snippet

--For each Key

;With CTE

as

(

Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data

)

Select [key],[date],[item],rank from CTE Where rank=1

|||

If you use sql server 2000 then you have to use the subquery...

Code Snippet

--For Overall

Select * from #data Where [date] in (Select max(date) From #data)

--For Each Key

Select * from #data Data

Join (Select [Key], max(date) date From #data Group By [Key]) Sub On

Sub.[Key]=Data.[Key] and Sub.date=Data.date

|||

This will do!

thanx

hans

Find out users in a particular Windows group ?

We have our admins create groups containing Windows users ... Is there a
way to check through SQL which members belong to that group ? Using SQL 2000Hi Hassan,
SQL Server doesn't store which Windows users are in a Windows group. So the
only way to find out is to use the command shell with something like (I
don't know the exact syntax of the NET command):
EXEC xp_cmdshell 'NET GROUP <group name> /DOMAIN'
--
Jacco Schalkwijk
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e5a3$$rxDHA.1996@.TK2MSFTNGP12.phx.gbl...
> We have our admins create groups containing Windows users ... Is there a
> way to check through SQL which members belong to that group ? Using SQL
2000
>

find all users in a sql server group

Hi There,
is it possible find all users in a sql server group, I'm not administrator,
I can use query analyser to run scripts that's it.
Thanks
GaneshUse sp_helpgroup system procedure, for instance:
EXEC sp_helpgroup 'db_owner'
Anith

Find All Children Of A specified Group ID

Hi all, Im designing a product menu like the one seenhere.
I need to be able to itterate through all product menu groups in a table and find all children of a specified parent. Sorry for the cryptic Explanation and crappyDiagram!

Please see mydiagramWhat does you table(s) look like? Can you post the DDL and some sample data?|||Certainly.

Here you go.|||Have you ever looped through all directories on a drive, folder by folder by coding a function that searches all subdirectories of a single directory and calls itself on each directory that it finds?

This is what im trying to achieve here, only by using stored procedures.|||>>Have you ever...

Sure. Here are some links which should get you on your way.

http://www.sqlteam.com/item.asp?ItemID=8866
http://www.intelligententerprise.com/001020/celko1_1.jhtml?_requestid=57013|||Nice one, Ta very much indeed!

Filters in combination with the aggregate function

Hi all,
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?

Filtering Top n on Group or Table

Hopefully someone can help.

I am trying to filter either my group or the whole table to return the top 5 lines of my data.

If I use Topn (Fieldname) = 5 in my filter for the group, it returns merely the total of the table and hides all the detail.

If I use the same filter in the table filter, only the table header is returned.

I have rather a complex formula based on parameters to calculate the field I want to show the top 5 of. e.g. If parameter equals 2 then add balance_period_1 to balance_period_2. This formula works perfectly if I remove the top5 filter. See detailed expression below

The expression is

Topn(iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))

The Operator is set to =

The Value is set to 5

Any ideas would be greatly appreciated.

Set the filter expression to:
=iif(Parameters!Period.Value = "02", Fields!Year_Opening_Balance. Value+Fields!Balance_Period_01.Value +Fields!Balance_Period_02.Value,0))

Set the filter operator to:
TopN

Set the filter value to:
=5

Note: if you set the filter value to just 5, it will be interpreted as string constant - which won't work. You have to set it to =5, which will evaluate to the integer value 5.

-- Robert

|||

Thanks!!

So simple but blind to my eyes, now all is revealed!