help deleting SIMILAR records (not duplicate)

i've read lots of usenet and and microsoft support articles about how
to remove duplicate rows from a table, but i am trying to modify that
logic to delete "similar" rows. for example, consider the following:
create table t1 (
col1 int,
col2 bit,
col3 bit)
insert into t1 values (1, 0, 0)
insert into t1 values (2, 0, 0)
insert into t1 values (3, 0, 1)
now, clearly there are no duplicate rows. but what if, for the sake of
logical consistency, i need to remove "similar" rows, defining similar
in this example as rows with duplicate col2 and col3 values? keep in
mind: i don't care which row gets deleted (deleting the col1 value of 1
OR 2 will be fine)
most of the examples i'm reading involve selecting distinct * into a
temp table, which won't work for me, because the whole row is not
duplicated.
the result i am looking for AFTER the similar row deletion is as
follows:
select * from t1
col1 col2 col3
-- -- --
1 0 0
3 0 1
thanks for any help!Delete Table
Where Col1 In(
Select Min(T1.Col1)
From Table1 As T1
Group By T1.Col2, T1.Col3
Having Count(*) > 1
)
Thomas
"jason" <iaesun@.yahoo.com> wrote in message
news:1114800493.591486.311530@.l41g2000cwc.googlegroups.com...
> i've read lots of usenet and and microsoft support articles about how
> to remove duplicate rows from a table, but i am trying to modify that
> logic to delete "similar" rows. for example, consider the following:
> create table t1 (
> col1 int,
> col2 bit,
> col3 bit)
> insert into t1 values (1, 0, 0)
> insert into t1 values (2, 0, 0)
> insert into t1 values (3, 0, 1)
> now, clearly there are no duplicate rows. but what if, for the sake of
> logical consistency, i need to remove "similar" rows, defining similar
> in this example as rows with duplicate col2 and col3 values? keep in
> mind: i don't care which row gets deleted (deleting the col1 value of 1
> OR 2 will be fine)
> most of the examples i'm reading involve selecting distinct * into a
> temp table, which won't work for me, because the whole row is not
> duplicated.
> the result i am looking for AFTER the similar row deletion is as
> follows:
> select * from t1
> col1 col2 col3
> -- -- --
> 1 0 0
> 3 0 1
> thanks for any help!
>|||this is exactly the kind of logic i need, even though that will only
delete 1 similar row, where as i would like to only KEEP 1 similar row.
your code satisfies the example completely, however i might actually
have dozens of "similar" rows, for which i would only want to keep the
rows identified by the select min(col1) statement.
thanks again!|||You could run the query several times in succession or put it in a loop :)
BTW, once you get this resolved, you might want to make sure that the
client-side logic doesn't allow inserts of "similar rows"; or you could
ensure this via trigger.
"jason" <iaesun@.yahoo.com> wrote in message
news:1114801761.830867.51880@.z14g2000cwz.googlegroups.com...
> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||Try,
delete t1
where exists(select * from t1 as t2 where t1col2 = t2.col2 and t1.col3 =
t2.col3 and t2.col1 > t1.col1)
AMB
"jason" wrote:

> this is exactly the kind of logic i need, even though that will only
> delete 1 similar row, where as i would like to only KEEP 1 similar row.
> your code satisfies the example completely, however i might actually
> have dozens of "similar" rows, for which i would only want to keep the
> rows identified by the select min(col1) statement.
> thanks again!
>|||DELETE FROM T1
WHERE EXISTS
(SELECT *
FROM T1 AS T2
WHERE T1.col2 = T2.col2
AND T1.col3 = T2.col3
AND T1.col1 > T2.col1)
David Portas
SQL Server MVP
--

HELP CANT CONNECT TO SQL SERVER ON WIN XP PRO

I am having connection issues with SQL 2k on my win XP pro box. I had no problems last week but now I cant do anything.
I read many kb articles about problems with SP 2 and SQL 2k and nothing seems to help. Has anyone had any similiar problems? I need to access my remote sql server for a client and I have been dealing with this problem for the last week with no solution? I believe I have opened up every possible port the SQL will use. I followed all of the kb articles and still nothing.
Thanks

Can you give us more detail on the "connection issues"? Are youreceiving any error messages? If so, these will go a long waytowards helping you to resolve the problem.
|||

Sure, in enterprise manager when I click on one of my server nodes it try to connect to the site.

The error I get is
A connection could not be established to xyz server
Reason: SQL Server does not exist or access denied
ConnectionOpen (Connect()...)
I was able to connect to this server last week with no issues. I have tired 5 different servers and I cant connect to any of them. I am running windows xp pro service pack 2 and sql 2k.

|||One thing you could do is to check the logs under Data folder.

HELP ! Login failed for user MachineName\ASPNET

Hi,

I am new to ASP.NET and have an ever approaching deadline to develop a database driven web application. Having read a lot of articles on the Internet I decided on Visual Studio 2003, MSSQL Server 2000 Developer Edition, all running off Windowns XP Pro.

I cannot get the ASP to access the database, I keep getting the following message

System.Data.SqlClient.SqlException: Login failed for user 'MachineName\ASPNET'

I do not know what to do to sort this problem and I cannot test anything I have developed as a result. I am very desperate for help as I have been trying to sort this out for the last 5 days.

Somebody please please please help me!!!!!!!!!

Thank you if you can.

RW#1
install Connector/ODBC - MySQL ODBC driver
http://dev.mysql.com/downloads/

#2
check out Connector/ODBC Programmer's Reference Manual
http://www.mysql.com/search/?q=myodbc

#3
Here is my code to populate a datagrid from a mySQL database.

IN GLOBAL.ASAX
Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)
'Create Database Connection String
Dim globalConnStr As String
globalConnStr = "driver={MySQL ODBC 3.51 Driver};"
globalConnStr = globalConnStr + "server=localhost;"
globalConnStr = globalConnStr + "port=3306;"
globalConnStr = globalConnStr + "Stmt=;"
globalConnStr = globalConnStr + "uid=root;"
globalConnStr = globalConnStr + "pwd=;"
globalConnStr = globalConnStr + "database=smfr_wildland;"
globalConnStr = globalConnStr + "Option=3;"

'Assign Database Connection String To
'global connection object
Application("globalConnStr") = globalConnStr
End Sub

in your code behind page that contains a datagrid...It should look KINDA like this.

Dim strSQL As New System.Text.StringBuilder
strSQL.Append("SELECT users.*,smfr_shifts.shift_name")
strSQL.Append(" FROM users")
strSQL.Append(" INNER JOIN smfr_shifts ON users.user_shift_id = smfr_shifts.shift_id")
strSQL.Append(" WHERE users.user_deploymentYN = " & x)
strSQL.Append(" ORDER BY users.user_deploymentYN DESC , users.user_sort;")

Dim DBConn As System.Data.Odbc.OdbcConnection
Dim DBCommand As System.Data.Odbc.OdbcDataAdapter

Dim DSPageData As New DataSet
DBConn = New System.Data.Odbc.OdbcConnection(Application.Item("globalConnStr"))
DBCommand = New System.Data.Odbc.OdbcDataAdapter(strSQL.ToString, DBConn)

DBCommand.Fill(DSPageData, "users")

dgTeam.DataSource = DSPageData.Tables("users").DefaultView

dgTeam.DataBind()

'Close and Clean-Up
DBConn.Close()
DBConn.Dispose()
DBConn = Nothing

DBCommand.Dispose()
DBCommand = Nothing

DSPageData.Dispose()
DSPageData.Clear()
DSPageData = Nothing

-------
NO System.Data.SqlClient
USE System.Data.Odbc

Hope this helps?

Tom|||Ralph,

If you are indeed using Microsoft SQL Server, then your code should be OK. You've posted in the MySQL section.

What you need to do is add the MACHINENAME\ASPNET user as a Login in your database, and give that user rights to the database objects it needs.

When an ASP.NET application trys to access your SQL Server database, it does so by using that account.

[now moved from MySQL forum to SQL Server forum]|||A few important remarks:
1. ASPNET will only work on W2K and Windows XP, not on Windows Server 2003 where the identity of the application pool will be used instead (network service account).
2. Even if yo'ure on W2K, the ASPNET user can be bypassed if you're using (Windows authentication with) impersonation or you've changed the processModel section; in that case, use that user account on SQL Server instead to give permissions to.
3. However, a big fat remark: if you grant ASPNET or NETWORK SERVICE rights to the database, every other service/application running in that identity's context will have rights on the database as well. On a hosted environment where others can upload code to the machine as well, this is a big big security leak since others can write code to mess up your database. If you still need to do this, consider to give the user only read rights on the database!
4. It would be better from this side of the picture to use SQL authentication instead, although the password is not encrypted then when it's sent to the server. This is a less "heavy" issue if SQL Server is running on the same box as the web server (definitely not a scalable solution, but still it's out there in many places) or when the server environment is heavy controlled (a switched network between the servers).
5. Another approach is to impersonate as a certain identity from within the code whenever you want to talk to the database and to undo that impersonation afterward, as shown on my blog intip 7.

In any case, try to lock down the number of users that can access the database as much as you can by creating separate users on the level of the database for each database (which has only rights on that database). Never ever connect in a lazy way (e.g. using a sysadmin user).|||I did not read the posting close enough...it was in the mySQL group when I read it and replied.

MONITOR...you may delete/remove my two post for this discussion...SORRY

Tom|||Put this into a text file called grantpermission.sql. Change the 'MachineName\ASPNET' to your computer name and change Use dbname to your database name. Then use SQL Query Analyzer and execute the code to add the user and grant permissions. ASPNET will become owner as you can see in the code. This assumes that you have everything installed correctly. I have the almost the exact same setup. Although I'm also using WebMatrix. SQL Desktop Edition on Win XP Pro.

-- Begin --
DECLARE @.username sysname
SELECT @.username = 'MachineName\ASPNET'

USE master
EXEC sp_grantlogin @.username

USE dbname
EXEC sp_grantdbaccess @.username
EXEC sp_addrolemember N'db_owner', @.username
-- End --