|
Hello Friends
What should be the query to show or find duplicate rows in a table?
Thanking in advance
Johnny
|
|
|
|
|
One way is to group by every column, so if you table has four columns, this would do the trick:
select col1, col2, col3, col4 from MyTable
group by col1, col2, col3, col4 having count(1) > 1
Regards,
Rob Philpott.
|
|
|
|
|
|
hi,
I have written a cursor that goes into infinite loop. I am not able to make out where i have done wrong.
the following is the code.
/******************************************************************************/
declare @dirid int
declare @topic varchar(8000)
create table #topic(Dir_Id int,topic_Id varchar(1000))
insert into #topic(Dir_Id,topic_Id)select D.Dir_Id,D.topicid FROM Category CN
INNER JOIN directory D ON CN.Cat_Id = D.OrgType_Id
INNER JOIN SubCategory SB ON D.Country = SB.SubCat_ID
WHERE CN.Cat_Name = 'Academic'
declare curtopic cursor for
select Dir_Id,topic_Id from #topic
open curtopic
fetch next from curtopic into @dirid,@topic
begin
while @@fetch_status = 0
set @topic = ''
SELECT @topic = topic_Id from #topic
fetch next from curtopic into @dirid,@topic
end
close curtopic
deallocate curtopic
print @topic
drop table #topic
/****************************************************************************/
i want to retrieve the data row by row for which i have written cursor.
but it is going in to infinite loop.
any help would be great full.
regards,
pranav
Pranav Dave
|
|
|
|
|
pranavcool wrote: while @@fetch_status = 0
set @topic = ''
There you have it - that's the loop that executes. You need to put the statements in a block using begin and end.
Regards,
Rob Philpott.
|
|
|
|
|
I want to know if SQL Server 2005 has predictive analysis tools?
http://www.microsoft.com/sql/technologies/dm/TATForecast/TATForecast.html
In above link you can see how Office 2007 has this Forcast feature using the analysis services(2008 i think). Can such a thing be done in VS 2005/2008 or SQL Server Business Intelligence Development Studio 2005
If yes, then can you point me to some useful links or demos.
If it cannot be done in SQL server 2005, but can be done in SQL server 2008( which i think it can be) then please give some useful links and demo for same.
Thanx,
s.a.w
s.a.w.
|
|
|
|
|
Hi all,
I am using SQL2005. I have 2 tables which are T1 and T2. I would like to intergrate them into one tabel, what i mean is add column when intergrate them instead of add row record.According what i know if using join or union will create a extra row.Record to be intergrate have to match the T1.AID with T2.ID
Below is my senario.
Table : T1 Table : T2
=========================== ===================
AID | Code | Type | Amount1 ID | Code | Amount2
=========================== ===================
A 123 IA 200 B 223 12
B 223 IP 100 C 323 29
C 323 MR 55 D 444 30
Result have to get:
=====================================
AID | Code | Type | Amount1 | Amount2
=====================================
A 123 IA 200 0
B 223 IP 100 12
C 323 MR 55 29
The record D in T2 would not include in new table.
Any idea are welcome.
Thanks in advance
cocoonwls
|
|
|
|
|
Hi,
If you know that table T1 has more records than table T2 you could do a LEFT OUTER JOIN as follows:-
SELECT t1.AID,t1.CODE,t1.TYPE,t1.AMOUNT1,COALESCE(t2.AMOUNT2,0)
FROM t1 LEFT OUTER JOIN t2 on (t1.AID = t2.ID)
I hope this helps.
Cheers,
Kevin
|
|
|
|
|
Hi Kevin,
I got it!thanks alot 
I have edit my sql to match my case...
thanks in advance
cocoonwls
|
|
|
|
|
Hi Kevin,
I got it,Thanks alot!
I have success to use the left outer join in my case...Thanks again
cocoonwls
|
|
|
|
|
Hi Cocoonwis,
You need to change the GROUP BY clause so it does not include the Amount fields.
Try this instead...
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE
Good luck,
Kevin
On Apr 9, 2009, at 9:17 AM, The Code Project forums wrote:
Hi Kevin,
Thanks for your help.I got it right now 
But i have another question about it, if the records are duplicated in t1, and also t2. How could i sum the amount in t1.Amount and t2.Amount. Example there are 2 record A1 in t1, then i would like to sum them.Also in t2, i would like to sum the amount which have the same id.
I have try in my database, it dosen't SUM for me if i write like :
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
group by t1.AID,t1.CODE,t1.Amount,t2.Amount
thanks in advance
cocoonwls
|
|
|
|
|
Hi kevin,
First of all,thanks for your help
I am facing another problem of the sql.How about if i need to filter also of the date?For example,
in t1 have 3 record as below:
Table : T1 Table : T2
====================================== ===============================
AID | Code | Type | Amount1 | Date ID | Code | Amount2 | Date
====================================== ===============================
A 123 IA 200 1/15/2009 A 223 12 1/16/2009
B 223 IP 100 1/24/2009 B 323 29 2/13/2009
C 323 MR 55 2/11/2009 E 444 30 2/13/2009
If i want the data which are between 1/1 to 1/30.
The result will be:
Table : TableResult
==================================================
AID | Code | Type | Amount1 | Amount2 | Date
==================================================
A 123 IA 200 12 1/15/2009
B 223 IP 100 0 1/24/2009
Note that the T2.ID for B is not include in.
any ideas are welcome 
Thanks in advance
regards
cocoonwls
|
|
|
|
|
Hi cocoonwls,
You will need to expand the query to include a WHERE clause. So something like this for example...
SELECT
t1.AID,
t1.CODE,
MAX(distinct(t1.TYPE) as t1Type),
SUM(t1.Amount) as t1Amount,
COALESCE(SUM(t2.Amount),0)
FROM t1
left outer join t2
on t1.AID = t2.ID
WHERE t1.DATE >= '20090101' AND t1.DATE <= '20090130'
group by t1.AID,t1.CODE
Cheers,
Kevin
|
|
|
|
|
Hi friend
How to create encrypted trigger in sql 2005
plz help me
Thanks and Regards
Azad yadav
|
|
|
|
|
You already asked this question, just a few posts down. Please don't do that, it is considered rude. People have already answered your original post.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
The first service pack for SQL Server 2008 has been released. Service Pack 1 (SP1) contains all the cumulative updates and fixes issued since SQL Server 2008 was released. It also makes it easier to deploy SQL Server 2008. A few specific features have been added:
- A feature called Slipstream allows users to install the database and service pack at once, easing the process of loading the software onto hundreds or thousands of servers.
- Report Builder 2.0 Click Once eases deployment.
- Service Pack Uninstall is designed to allow administrators to uninstall the service pack separately from the database release.
You can get the download here[^].
|
|
|
|
|
I am working with Microsoft Access MDBs (2003 Format) on a project and have a situation where two threads can be inserting records in the same table at the same time. I am using OleDB to open the Access MDB, create commands and transactions, and insert the records.
The connection string to open the MDB is essentially the following:
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Data Source = SOMENAME.MDB"
Even though I am trying to use record locking with this connection string, and the MDB is certainly shared, we consistently get "Could not update. Currently locked." errors when the two threads are writing to the same table at the same time (and the two threads are using separate OleDBConnections). I have tried using and not using transactions, and the error is the same.
Can anyone tell me what else I may need to do to avoid the "Could not update" problem I am experiencing?
Thank you...
modified on Wednesday, April 8, 2009 4:38 PM
|
|
|
|
|
AFAIK Access has PAGE locking, not record locking. I don't know if it still applies but MS used to state that Access is NOT a multiuser database and therefore record locking is not supported. Obviously the page is bigger than 1 record and your multithreaded app is screwing it up.
I used earlier versions with multiple users connected to the same data database with no real locking problems, but that is very different to a threaded application (which was not available on the desktop in those days).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. FWIW, the earliest versions of Access only supported page locking, but the Access 2003 format, at least in theory, is supposed to support record-level locking. If you happen to have such a version of an Access MDB, under its Tools/Options/Advanced tab you can see that there is an option to open databases using record-level locking. And, in the connection string I am using the parameter "Jet OLEDB:Database Locking Mode=1" is supposed to be for record level locking (Mode=0 would be page locking).
I agree that the multi-threading seems to be using page locking, so I am curious if anyone has had any success with Access MDBs under a similar, but certainly not recommended, scenario. Unfortunately using SQL Express is not currently an option on this project; users may have to suffer with slower non-threaded performance if I can't figure something out.
|
|
|
|
|
I presume you have jumped MS directly with the problem and got no respose?
It's been has 15 yrs since I worked with Access. 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I presume you have jumped MS directly with the problem and got no respose?
We don't have direct MS support; and their knowledge base (and other groups) has not yet shed any light on what I am seeing. However, I continue to hold out hope that I am missing something painfully obvious that will soon become clear. OTOH, I may just have to travel down another road.
Mycroft Holmes wrote: It's been has 15 yrs since I worked with Access.
Lucky you.
|
|
|
|
|
Assuming the two update threads belong to a single process, I would delegate all DB modifications to one specialized thread, and probably give it an input queue. Or choose another database (SQL Server Express).
The alternative, assuming DB load is low, is providing retry loops for every modification, with an appropriate sleep value of course.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Luc Pattyn wrote: I would delegate all DB modifications to one specialized thread, and probably give it an input queue
Thanks for the reply. Delegating all DB modifications to one thread with an input queue is an interesting thought. I'll probably give that a try if I can not figure out why this version of an Access MDB, which is supposed to support record-level locking, does not seem to at least in this multi-threaded app. Unfortunately SQL Server Express is not an option, and a retry loop could get real ugly in this app.
|
|
|
|
|
Hi,
How can I run (or call) more than one store procedure at the same time?
For example I have a 3 store procedure;
First is sp_GetResult
Second is sp_Allotment
Third is sp_Price
How can I run sp_Allotment and sp_Price stored procedures at the same time (like thread) in sp_GetResult stored procedure.
Note: I use MS SQL Server 2005
Best Regards...
|
|
|
|
|
Don't think you can. Sql Server determines its concurrency automatically and steps through stored procs iteratively.
Regards,
Rob Philpott.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
