|
|
|
Hi All,
I have a table with the below columns.
ID , First Name , Last Name , City. The ID column is unique. This table contains duplicate rows (First Name , Last Name , City values are same). Could you please advise me on how to write a select query to display duplicate rows only once along with the other rows.
Thanks and Regards
Pha
|
|
|
|
|
Do you need the ID for display? If not, a simple DISTINCT might be easiest.
|
|
|
|
|
Thanks for the response. But i need the ID as well in the display.
Regards
Pha
|
|
|
|
|
-- ALWAYS TEST FIRST
-- I don't have your table name
-- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME
-- Try this to select the dups
select count(*),FirstName , LastName , City
from "TABLE"
group by FirstName , LastName , City
having count(*) > 1
-- select the data into another table and TEST THIS
-- REPLACE "TABLE" WITH THE ACTUAL TABLE NAME
--*********************** DELETE DUPS ***************************************
-- the Magic is in the rowcount setting!!! make sure it is set to 1 at the start and 0 on completion
set rowcount 1
while exists (select FirstName,LastName,City
from "TABLE"
group by FirstName , LastName , City
having count(*) > 1)
begin
delete T
from "TABLE" T
inner join (select FirstName,LastName,City
from "TABLE"
group by FirstName,LastName,City
having count(*) > 1) dup on T.FirstName = dup.FirstName and T.LastName = dup.LastName and T.City = dup.City
end
go
set rowcount 0
go
Good Luck DBranscome Phoenix AZ
David Branscome
modified on Tuesday, February 12, 2008 7:19 PM
|
|
|
|
|
I am having a little problem in handshakes routed through MSMDPump getting unduly delayed and thrown back with a Timeout exception.
I have the configuration in line with http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx[^] setup. It is working fine with a significant number of installations except a typical one which involves the server and DB in isolated networks. The initial request goes through however, the subsequent requests choke the server with timeouts. There is no heavy CPU activity anyway.
I have tried adjusting MaxThreadsPerClient in the above URL but not much significant gains.
Any clues?
Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips
A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson
|
|
|
|
|
Hi to all
create table emp(eid int identity(1,1) primary key,ename varchar(30),sal money)
create table emprel(RelId int,RelMobile char(10),RelAddress varchar(50))
i created two tables like above, i want to create a view and i want restrict to access the RelAddress for some users how can i do it
RelId is foreign key for emp table
eid ename sal RelId RelMobile RelAddress
1 aaa 10000 1 84874 doorno:5435,hyd
2 bbb 10000 1 84874 doorno:4435,pune
3 ccc 10000 1 84874 doorno:5435,bangalore
i want to specific users restrict to access the RelAddress column , can we do this by using view,
what is the exact use of view, how can i user, i very familiar using of stored procedure, but no idea on view please any body can help, i want simple view example to Analise initially about views.
This is haneef.............................................................
|
|
|
|
|
Hi all
i want to develop a win desktop application in vb.net with mssql as backend for a client which has 6 to 10 users in his office.
as i am new to mssql, please tell me which edition of mssql is suitable for this type application.
is there need to install msssql on all pcs or on a single (main server)
thanks a lot in advance
rmshah
Developer
|
|
|
|
|
If cost is not a factor then definitely SQL Server 2005 Enterprise edition.
Otherwise go for SQL Server 2005 Standard edition.
If you are on a shoe-string budget then MySQL is the answer Clickety
r_mohd wrote: is there need to install msssql on all pcs or on a single (main server)
is answered by this
r_mohd wrote: i want to develop a win desktop application in vb.net with mssql as backend
The clients will need the engines/etc to be able to communicate with the server, which you will be providing with your client end applications
If you want one of the clients to be able to administer the server then you will need to install the client tools on that client.
Regards
Guy
You always pass failure on the way to success.
|
|
|
|
|
Thanks a lot. for providing me this info.
rmshah
Developer
|
|
|
|
|
Hi,
why does the following code not works?
DECLARE @sql AS nvarchar(4000)<br />
SET @sql = 'SELECT COUNT(subject) AS Expr1, new_de_scoringname<br />
FROM (' + @CRM_FilteredLead+ ') <br />
GROUP BY new_de_scoringname<br />
ORDER BY new_de_scoringname<br />
as fa' EXEC (@sql)
Code runs in the dataset of a sql-server reporting services report
and will be loaded in microsoft CRM reports.
Errormassage in visual studio is:
"Error by running the dataset
wrong syntax near GROUP keyword"
Thank you
Wolfgang
|
|
|
|
|
I;m getting the above error mesage even though im connecting to sql 2000.
the message is shown below:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
However I am not using sql 2005 im using sql2000, what would be the reason for this?
thanks
|
|
|
|
|
i cannot run the sqlserver2005, like sqlserver2000. how handle that with vb.net2003?
Harry
|
|
|
|
|
What is that you are trying to do and what is the issue that you are facing?
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
HI,
how will operating system update database backup daily by itself in sqlserver2000?could U pls help me?are their steps?how to do that?first, i to have to take backup or what?
renuka
|
|
|
|
|
Create a SQL Server JOB with below code
EXECUTE master..xp_sqlmaint '-D <dbname> -WriteHistory -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB -UseDefDir -DelBkUps 1DAYS -BkExt "BAK" -VrfyBackup -UpdOptiStats 15'
DUMP TRANSACTION <dbname> WITH TRUNCATE_ONLY
---------
Shyam Benagal
|
|
|
|
|
Hi.
Our company has some old VB6 programs which connect to a database through ODBC providers (for SqlServer, Access or MySql, depending on what the client prefers).
I wrote two new modules in C#, .NET 2.0 which connect to the same databases but using the .NET OldDb(for Access) and SqlDb or MySql Connector components.
The annoying thing is that more than once, we had reports from customers saying that they cannot use the .NET programs, but the VB6 ones were working. After turning off their firewalls they all worked. The error messages indicate timeouts while reaching the server.
I can't figure out the reason for this. I mean if the DB server is set to work on the default port, why would ODBC connection would be allowed by the firewall and the .NET connection would be timed-out?
Any clues or similar experiences?
Thanks.
|
|
|
|
|
what is teh diff b/w sql and pl/ sql?
raja
modified on Tuesday, February 12, 2008 1:05 AM
|
|
|
|
|
What does this have to do with ASP.NET ?
SQL is the language all databases use. There are many variations, such as T/SQL, PL/SQL, etc. They are dialects, SQL is the core language.
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
What is the difference between a programmer and a slacker?
Google not working where you live??
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
what is the diff b/w create and create alter stored procedure in tee sql server?
raja
modified on Tuesday, February 12, 2008 1:04 AM
|
|
|
|
|
Are you preparing for any interview?? You are keep on asking questions.
Instead of asking questions here, you can google it.
To my knowledge, this forums helps the people to fix their issues rather than answering 5 marks questions.
|
|
|
|
|
what is diff b/w inline query and the stored procedure?
raja
modified on Tuesday, February 12, 2008 1:05 AM
|
|
|
|
|
Hi dude.
I think u have faced Interview in very near past.
Just don't rely only this forum go through Google and search for More That will help
Them main diffrence between InlQuery ans SP is as follow.
InlineQuery = This is written in ur class file.
StoreProc = This is written in sql server.
Inlinequery = Syntax is checked in Sql Server.
StoreProc = Doesn't need checking.
InlineQ = Needs Compilation
StoreProc = No required Compilation.
InlineQ = When Grand Searching Requires Inline Query Becomes Very Handy and Easy To handle
StoreProc = It is not as much as powerful for searching.
Now search for other resources and Post here whatever u find. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
