Merge apparent duplcate rows into 1 row?

Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
ThanksUse a SELECT DISTINCT:
SELECT DISTINCT
tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125055041.994620.74900@.g14g2000cwa.googlegroups.com...
Hi,
I have a select query that can generate apparent duplicates; this
occurs because the Histology value is determined from a table
tblSample, this may contain a number of samples for the same location
as there are different methods of obtaining samples sometimes 2 or more
method are used to back up results. The method is not important for
this table and so not shown, so showing apparent duplicates. Heres an
example of the table:
Code Date Location Histology
---
CO123 12/08/2005 Left Main Adeno
CO123 12/08/2005 Left Main Adeno
BJ234 12/08/2005 Right Main Normal
BJ234 12/08/2005 Right Lower Squamous
CH345 17/08/2005 Right Middle Normal
This is my SQL:
SELECT tblPatient.pntCode AS Code, tblPDT.pdtDate AS Date,
tblLesion.lesLocation AS Location,
tblSample.splHistology AS Histology
FROM tblPatient, tblPDT, tblLesion, tblSample
WHERE tblPatient.patientNo = tblPDT.patientNo
AND tblPatient.patientNo = tblLesion.patientNo
AND tblLesion.lesNo = tblSample.lesNo
Is there a way to combine these apparent duplicate rows into one row?
Essentially doing:
If no of rows where Code, Date, Location, match > 1
Delete rows >= 2
Thanks|||Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!|||If you're new to SQL, this is the place to hang out. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1125058223.511501.191000@.o13g2000cwo.googlegroups.com...
Thanks Tom, i'm pretty new to SQL so not very familiar with the syntax
yet. Glad this one was an easy fix!

Merge Agent Profile downloadreadchangesperbatch

If a MS represenative monitors this group, I am curious if there is a known
bug where the merge agent quadruples the value stored in the
downloadreadchangesperbatch?
Evidence of this is seen by capturing calls to the procedure shown below
where the first paramter is the number of rows to select. This specific
example occurs when the downloadreadchangesperbatch = 50.
exec sp_MSenumpartialchanges 200, N'#belong_agent_409150406', 2205125,
'1A842C8F-EDD2-4853-9BA4-979BBDF31ADE', 'DE4291F8-D4AE-48FC-A120-486818DFE5A9'
Forgot to mention SP3a @.@.VERSION = 8.00.760 on both publisher and subscriber
running development edition.
"Brian Reuter" wrote:

> If a MS represenative monitors this group, I am curious if there is a known
> bug where the merge agent quadruples the value stored in the
> downloadreadchangesperbatch?
> Evidence of this is seen by capturing calls to the procedure shown below
> where the first paramter is the number of rows to select. This specific
> example occurs when the downloadreadchangesperbatch = 50.
>
> exec sp_MSenumpartialchanges 200, N'#belong_agent_409150406', 2205125,
> '1A842C8F-EDD2-4853-9BA4-979BBDF31ADE', 'DE4291F8-D4AE-48FC-A120-486818DFE5A9'
>

Merge Agent fails - "Invalid character value for cast specificatio

Hi
From http://support.microsoft.com/kb/867880 it seems to have been fixed in
release 952. If you call PSS they should be able to give you the approriate
patch.
John
"Luke Ward" wrote:

> Hi Guys
> This above error is suppose to be fixed in SP4 for SQL Server 2000, howeve
r,
> SP4 is not available yet!
> Does anyone know of a HOT FIX or work around?
> Many Thanks
> Luke
>
>Thanks, I will give them a call
btw - both my servers are on SP3a
Cheers
Luke
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:EDF66E5C-3BEE-4F80-A24B-49868D66D251@.microsoft.com...
> Hi
> From http://support.microsoft.com/kb/867880 it seems to have been fixed in
> release 952. If you call PSS they should be able to give you the
> approriate
> patch.
> John
>
> "Luke Ward" wrote:
>|||Hi Guys
MS Tech gave me a fix, which did not fix it, I have now applied SP4, and
still get the same error :-(
I am going to see if I can attach logging to the publication.
What else can I do? any advise appreciated.
Luke
"Luke Ward" <lukeward@.campbelluk.com> wrote in message
news:OkrgViKUFHA.2940@.TK2MSFTNGP10.phx.gbl...
> Thanks, I will give them a call
> btw - both my servers are on SP3a
> Cheers
> Luke
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:EDF66E5C-3BEE-4F80-A24B-49868D66D251@.microsoft.com...
>

Merge 2 queries

Hi all i have 2 queries. One of which works out where 2 values are equal and the other where where all value are there.. This is in order for me to divid one by another to get percentage.

THIS IS FOR ALL
select results.playerid, players.playername, count (results.playerid) as allgames
from results, players
where results.playerid = players.playerid
group by results.playerid, players.playername
order by allgames desc

THIS IS FOR WON
select results.playerid, players.playername, count (results.result) as wongames
from results, players
where results.result = 1 and
results.playerid = players.playerid
group by results.result, results.playerid, players.playername
order by results.playerid asc

I can make them have the same view output...

How can i merge them into one to get percentage.. Ideally i would like to this on the fly because once i have all the result i want i want to do a front end on the net.

I am using SQL server Enterprise Manage.

Thanks in advance.declare @.var1 float, @.var2 float, @.var3 float

set @.var1 = (select count (results.playerid) from results, players where results.playerid = players.playerid)
set @.var2 = (select count (results.result) as wongames from results, players where results.result = 1 and results.playerid = players.playerid)

set @.var3 = @.var2/@.var1 * 100

select @.var3
--or--
select convert(varchar(50),@.var3)+'%'|||thank you!! i'll try it tomorrow.|||is this method only able to send single value???

"Subquery returned more than 1 value"

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:
> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
--
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>> Hi
>> No. SQL Server only uses what is is configured to use (700MB).
>> This drop of 1400MB, was it Physical Memory or Commit Charge in task
>> manager?
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>>
>> "Frank Esser" wrote:
>> Hi,
>> I am not so familiar with SQL server settings...
>> I use the latest MSDE and recognized huge memory usage of the SQL server
>> thread (seen in TaskManager). Then I used sp_configure to set the
>> maximum
>> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
>> while the TaskManager showed a SQL server memory usage of about 680 MB
>> which
>> was quite stable for a long time.
>> Now I stopped the SQL server and the total memory usage was decreased by
>> almost 1400 MB...
>> That's strange because this would mean that the SQL server really used
>> twice
>> of the memory that was set as maximum and that was dispalyed as real
>> memory
>> usage in the TaskManager.
>> Can anybody explain this to me? How can I avoid this behaviour?
>> Does it mean that I have to set the max memory usage to 350 MB to get a
>> real
>> memory usage of 700 MB?
>> Thanks a lot and sorry if this is a silly or already discussed
>> question...
>> Frank
>>
>

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
Frank
Hi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager?
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:

> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB which
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twice
> of the memory that was set as maximum and that was dispalyed as real memory
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a real
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>
|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>

Memory usage - double of the expected value?

Hi,
I am not so familiar with SQL server settings...
I use the latest MSDE and recognized huge memory usage of the SQL server
thread (seen in TaskManager). Then I used sp_configure to set the maximum
memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
while the TaskManager showed a SQL server memory usage of about 680 MB which
was quite stable for a long time.
Now I stopped the SQL server and the total memory usage was decreased by
almost 1400 MB...
That's strange because this would mean that the SQL server really used twice
of the memory that was set as maximum and that was dispalyed as real memory
usage in the TaskManager.
Can anybody explain this to me? How can I avoid this behaviour?
Does it mean that I have to set the max memory usage to 350 MB to get a real
memory usage of 700 MB?
Thanks a lot and sorry if this is a silly or already discussed question...
FrankHi
No. SQL Server only uses what is is configured to use (700MB).
This drop of 1400MB, was it Physical Memory or Commit Charge in task manager
?
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Frank Esser" wrote:

> Hi,
> I am not so familiar with SQL server settings...
> I use the latest MSDE and recognized huge memory usage of the SQL server
> thread (seen in TaskManager). Then I used sp_configure to set the maximum
> memory usage to 700 MB (1,5 GB RAM on my machine) and it worked. After a
> while the TaskManager showed a SQL server memory usage of about 680 MB whi
ch
> was quite stable for a long time.
> Now I stopped the SQL server and the total memory usage was decreased by
> almost 1400 MB...
> That's strange because this would mean that the SQL server really used twi
ce
> of the memory that was set as maximum and that was dispalyed as real memor
y
> usage in the TaskManager.
> Can anybody explain this to me? How can I avoid this behaviour?
> Does it mean that I have to set the max memory usage to 350 MB to get a re
al
> memory usage of 700 MB?
> Thanks a lot and sorry if this is a silly or already discussed question...
> Frank
>
>|||It was Physical Memory.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...[vbcol=seagreen]
> Hi
> No. SQL Server only uses what is is configured to use (700MB).
> This drop of 1400MB, was it Physical Memory or Commit Charge in task
> manager?
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Frank Esser" wrote:
>|||The Max memory setting is only for the buffer pool and does not take into
account the MemtoLeave area which can add up to 386MB's to SQL Servers
usage. But that still would only be around a GB. I think there must have
been something else holding on to it and it was a coincidence.
Andrew J. Kelly SQL MVP
"Frank Esser" <Mistral@.nurfuerspam.de> wrote in message
news:eg%23XF0pZFHA.1448@.TK2MSFTNGP09.phx.gbl...
> It was Physical Memory.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> schrieb im Newsbeitrag
> news:A25641B5-DAE8-49C2-A1D7-67C48E29FD45@.microsoft.com...
>