More Efficient way to Concat Rows

Hi I have this problem with a query to process data concatenating
Units/Modules.
I have one View called Certificates thats lists Course IDs, Candidate
Names and Units. As each Certificate shows multiple Units achieved by
the Candidate I need to transform this data and combine the Units into
a single value - so I have 1 row per certificate rathan than 1 row per
unit.
I have been using this Query and several UDF's (Unit names, Unit
Grades, Unit Award dates etc) but as the database incresed in size the
query is timing out - Is there a more efficient way to do this ? Also
the function seems to create duplicates so i am using DISTINCT and
thisn seems to slow it down too. Help!
Thanks
hals_left
SELECT DISTINCT CourseID, CandidateID,dbo.GetUnits(CandidateID,
CourseID) AS Units
FROM Certificates
CREATE FUNCTION dbo.GetUnits
(@.CandidateID AS smallint, @.CourseID AS smallint )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @.str AS varchar(1000)
SET @.str = ''
SELECT @.str = @.str + UnitTitle + char(13) + char(10)
FROM Certificates
WHERE CandidateID = @.CandidateID AND CourseID= @.CourseID
RETURN @.str
ENDIf this is just formatting for display then it isn't obvious why you
would need to do it in the database. The simplest and most likely
fastest method is in the client or presentation tier. See:
http://msdn.microsoft.com/library/d...
etstringmethod(recordset)ado.asp
David Portas
SQL Server MVP
--|||I agree but the client application (Word 2K Mailmerge) isnt capable of
this.|||Hello, hals_left
To make sure that the function is not called too many times, use a
query like this:
SELECT CourseID, CandidateID,
dbo.GetUnits(CandidateID, CourseID) AS Units
FROM (
SELECT DISTINCT CourseID, CandidateID,
FROM Certificates
) x
Razvan

More efficient way of checking for value

This trigger needs to check the contact1 table for the deleted accountno. If
it is not found there it will then use the record in contact1del.
This trigger works but I know there is a better way to check for the deleted
accountno in contact1 in the IF statement. Accountno values in Contact1 are
always unique
Thank you in advance for reading this. Any help and\or advise on this
trigger in general is greatly appreciated
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from Contact1
)
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join
contact1
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
END
ELSE
BEGIN
INSERT INTO Contact1Del
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
FROM deleted
INNER join
contact1del
ON contact1del.accountno = deleted.accountno
WHERE contact1del.rectype IS NULL and deleted.rectype='C'
ENDJenks,
Try using EXISTS instead.
See:
http://msdn.microsoft.com/library/d...br />
0a2b.asp
HTH
Jerry
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:1D3AE7DF-B678-461B-B0FF-11F4010DFDD6@.microsoft.com...
> This trigger needs to check the contact1 table for the deleted accountno.
> If
> it is not found there it will then use the record in contact1del.
> This trigger works but I know there is a better way to check for the
> deleted
> accountno in contact1 in the IF statement. Accountno values in Contact1
> are
> always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
>|||jenks wrote:
> This trigger needs to check the contact1 table for the deleted
> accountno. If it is not found there it will then use the record in
> contact1del.
> This trigger works but I know there is a better way to check for the
> deleted accountno in contact1 in the IF statement. Accountno values
> in Contact1 are always unique
> Thank you in advance for reading this. Any help and\or advise on this
> trigger in general is greatly appreciated
>
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> if (SELECT TOP 1 accountno FROM deleted) in (SELECT accountno from
> Contact1)
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join
> contact1
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> END
> ELSE
> BEGIN
> INSERT INTO Contact1Del
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1,
> fax, address1, address2, address3, city, state, zip, country,
> mergecodes, lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact,
> deleted.title, deleted.contsupref, deleted.dear, deleted.phone ,
> deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contact1del.company, Contact1del.key5
> FROM deleted
> INNER join
> contact1del
> ON contact1del.accountno = deleted.accountno
> WHERE contact1del.rectype IS NULL and deleted.rectype='C'
> END
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )
But your original code does not handle multi-row deletes where one row
deleted exists in the Contact1 table and one row that is deleted does
not. Tthe EXISTS code above won't work correctly in that case either.
You could just always run both inserts for rows that exist and for rows
that do not, assuming those rows are mutually exclusive and forget
checking for existence.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||In this case, the deleted rows with rectype of 'C' will always have an
accountno value in one of the two tables(contact1 or contact1del). EXISTS
will always work, unless I am missing something.
Thanks alot guys. Really appreciate it.
"David Gugick" wrote:
IF EXISTS (
SELECT *
FROM dbo.Contact1
INNER JOIN deleted
on deleted.accountno = Contact1.accountno )

> But your original code does not handle multi-row deletes where one row
> deleted exists in the Contact1 table and one row that is deleted does
> not. Tthe EXISTS code above won't work correctly in that case either.
> You could just always run both inserts for rows that exist and for rows
> that do not, assuming those rows are mutually exclusive and forget
> checking for existence.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||David, I just read your response more carefully and I see I missed your poin
t.
In this case, the records are touched through a front end app. All Contsupp
records with a rectype of 'C' will always have an associated Accountno in
Contact1. It is possible to delete individual Contsupp records, in which cas
e
the first part of the query with the IF EXISTS statement will be true. The
second part of the trigger is to handle deletion of Contact1 records. When a
Contact1 record is deleted, the associated Contsupp records are automaticall
y
deleted imediately after the Contact1 record. (there is a deletion trigger o
n
Contact1 as well).
Again, thank you for taking a look
"jenks" wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del). EXISTS
> will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
>|||jenks wrote:
> In this case, the deleted rows with rectype of 'C' will always have an
> accountno value in one of the two tables(contact1 or contact1del).
> EXISTS will always work, unless I am missing something.
> Thanks alot guys. Really appreciate it.
> "David Gugick" wrote:
> IF EXISTS (
> SELECT *
> FROM dbo.Contact1
> INNER JOIN deleted
> on deleted.accountno = Contact1.accountno )
Let's say that two rows are deleted in a single statement:
Delete from dbo.Contsupp
Where accountno in (1, 2)
One of the rows deleted exists in Contact1. The other one doesn't. The
EXISTS statement will return true because one row exists in the
relationship between deleted and Contact1 and the corresponding insert
will take place. However, there is another row in the deleted table
which does not exist in Contact1 and its insert will not be executed.
So I think you still have a problem in your code.
David Gugick
Quest Software
www.imceda.com
www.quest.com

More efficient than LEFT JOIN

I have a table with data that is refreshed regularly but I still need to
store the old data. I have created a seperate table with a foreign key
to the table and the date on which it was replaced. I'm looking for an
efficient way to select only the active data.

Currently I use:

SELECT ...
FROM DataTable AS D
LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
WHERE D.Key IS NULL

However I am not convinced that this is the most efficient, or the most
intuitive method of acheiving this.

Can anyone suggest a more efficient way of getting this information
please.

Many thanks.

*** Sent via Developersdex http://www.developersdex.com ***Hi, Brian

I think that you wanted to write "WHERE I.Key IS NULL" (instead of
"WHERE D.Key IS NULL"). In this case, you can use something like this:

SELECT ...
FROM DataTable WHERE Key NOT IN (
SELECT Key FROM InactiveTable
)

Razvan

PS. I assume that the "Key" column does not allow NULL-s.|||On Wed, 15 Feb 2006 12:41:09 GMT, Brian Wotherspoon wrote:

>
>I have a table with data that is refreshed regularly but I still need to
>store the old data. I have created a seperate table with a foreign key
>to the table and the date on which it was replaced. I'm looking for an
>efficient way to select only the active data.
>Currently I use:
>SELECT ...
>FROM DataTable AS D
>LEFT OUTER JOIN InactiveTable AS I ON I.Key = D.Key
>WHERE D.Key IS NULL
>However I am not convinced that this is the most efficient, or the most
>intuitive method of acheiving this.
>Can anyone suggest a more efficient way of getting this information
>please.
>Many thanks.

Hi Brian,

The most intuitive way, IMO, is

SELECT ...
FROM DataTable AS D
WHERE NOT EXISTS
(SELECT *
FROM InActiveTable AS I
WHERE I.Key = D.Key)

The most efficient is either the above or your own LEFT OUTER JOIN query
(but do change D.Key to I.Key in the IS NOT NULL check!!) - but it'll be
only efficient if the I.Key column is indexed.

--
Hugo Kornelis, SQL Server MVP|||try to bring it up a level.

for instance, you are probably creating a temp table? Perhaps create
the temp table with closer to the data you really need.
if you are looking for only one cusotmer, then only pull that one
customer. or, for a specfiic time period, then only that time period.

also, make sure you have an index on inactivetable.key.

if you knew that inactivetable started at some timeframe for all
records, then you could create a composite index on
inactivedata.timestamp plus key.

would it be worth putting an "inactive data datestamp" at the customer
level? perhaps if you have enough rows.

More Efficient SQL Statement - Select Count(1)

I know that you can write select statement (Select Count(1)) within Oracle
if finds one record it will stop the search critieria.
Please help me modify the SQL statement listed below to use the equivalent
if it finds one records it stops and output is 1 if not the output is 0.
Thank You,
SET @.COUNT_CALLS_REC_1 =
(Select count(Icent_Num)
from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||something like this?
if exists(
Select 1 from TKCalls.dbo.tblCalls
where DATEDIFF(mi, StartedTime, GETDATE()) <=30
AND left(cast(Icent_Num as varchar(20)),6) = ('962472')
)
SET @.COUNT_CALLS_REC_1 = 1
else
SET @.COUNT_CALLS_REC_1 = 0
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Sorry, the first SET ROWCOUNT should be 1 -NOT 0.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23ojjUjHnGHA.2264@.TK
2MSFTNGP04.phx.gbl...
Preface the query with a return limitation -ROWCOUNT.
USE Northwind
GO
SET ROWCOUNT 0
SELECT CustomerID
FROM Customers
WHERE Country = 'Mexico'
SET ROWCOUNT 0
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message news:FDDCBA4B-3553-4459-B7DA-2
5436FBEC8C9@.microsoft.com...
>
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
>
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
>
> Thank You,
>
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Joe k.,
Use the operator "exists" and do not manipulate the columns in the "where"
clause as possible, to let SQL Server to use the indexes optimally in case
they exists.
if exists (
select *
from TKCalls.dbo.tblCalls
where
StartedTime between dateadd(minute, -30, GETDATE()) and GETDATE()
and cast(Icent_Num as varchar(20)) like '962472%'
)
set ...
else
set ...
go
AMB
"Joe K." wrote:

> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
>
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||Are you sure that on Oracle, the statement SELECT COUNT(1) will never
return a value > 1? I would be very surprised if this is the Oracle
behavior, because that is not what the SQL statement is specifying...
Gert-Jan
Joe K. wrote:
> I know that you can write select statement (Select Count(1)) within Oracle
> if finds one record it will stop the search critieria.
> Please help me modify the SQL statement listed below to use the equivalent
> if it finds one records it stops and output is 1 if not the output is 0.
> Thank You,
> SET @.COUNT_CALLS_REC_1 =
> (Select count(Icent_Num)
> from TKCalls.dbo.tblCalls
> where DATEDIFF(mi, StartedTime, GETDATE()) <=30
> AND left(cast(Icent_Num as varchar(20)),6) = ('962472'))|||doesn't "select top 1 * from ..." work for you?
Jos=E9.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:
cle
ent|||This should work as well...
scott0100
---
scott0100's Profile: http://www.dbtalk.net/m491
View this thread: http://www.dbtalk.net/t316762|||As I recall, one of the issues with TOP 1 is that it has to find all records
(or at least indexes) first in order to determine which one is the TOP 1. So
that may not be 'efficient'.
;-)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
<josearaujof@.gmail.com> wrote in message
news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
doesn't "select top 1 * from ..." work for you?
Jos.
Gert-Jan Strik wrote:
> Are you sure that on Oracle, the statement SELECT COUNT(1) will never
> return a value > 1? I would be very surprised if this is the Oracle
> behavior, because that is not what the SQL statement is specifying...
> Gert-Jan
>
> Joe K. wrote:|||> As I recall, one of the issues with TOP 1 is that it has to find all records (or at least
indexes)
> first in order to determine which one is the TOP 1. So that may not be 'efficient'
.
Only with ORDER BY. Without ORDER BY, you ask for any one row, so the optimi
zer and engine know it
can stop after the first row it encounters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arnie Rowland" <arnie@.1568.com> wrote in message news:%23G0c2mInGHA.3388@.TK2MSFTNGP05.phx.
gbl...
> As I recall, one of the issues with TOP 1 is that it has to find all recor
ds (or at least indexes)
> first in order to determine which one is the TOP 1. So that may not be 'ef
ficient'.
> ;-)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> <josearaujof@.gmail.com> wrote in message
> news:1151700301.773682.267740@.75g2000cwc.googlegroups.com...
> doesn't "select top 1 * from ..." work for you?
> Jos.
> Gert-Jan Strik wrote:
>

More efficient query than this?

I have two tables:
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
where tblB/ColA references tblA/ColA.
I need to find the most recently added row from tblB given a value of
ColA.
I wrote the following but wondered if there was a better, more
efficient method.
declare @.ColA int
select ColB, p.dateadded
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA and
b.dateadded = (select max(b.dateadded)
from tblB b
inner join tblA a on b.ColA = a.ColA
where a.ColA = @.ColA)
TIA LarsMhhm,
if there is a constraint between the two why do you need the Join ?
declare @.ColA int
select ColB, MAX(p.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>|||Jens,
Thanks for the reply. I don't think I explained the problem well
enough.
The table definition
tblA tblB
---
ColA int PK ColA int FK
... ColB int PK
DateAdded datetime
and some test data
ColA ColB DateAdded
---
100 32 2-10-2005
100 16 3-01-2005
100 24 3-16-2005
100 20 1-15-2005
101 ...
I would like a query which would return the single row whose ColA
value = 100
100 24 3-16-2005
since it has the latest date.
Lars
On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>Mhhm,
>if there is a constraint between the two why do you need the Join ?
>declare @.ColA int
>select ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>HTH, Jens Smeyer
>--
>http://www.sqlserver2005.de
>--
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:vtd061tnman8e1hft4f23rfos4sqa1q9a8@.
4ax.com...
>|||OK, whats wrong with that?
declare @.ColA int
Set ColA = 100
select b.ColB, MAX(b.dateadded)
from tblB b
where b.ColA = @.ColA
Group by ColB
Jens Smeyer.
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
> Jens,
> Thanks for the reply. I don't think I explained the problem well
> enough.
> The table definition
> tblA tblB
> ---
> ColA int PK ColA int FK
> ... ColB int PK
> DateAdded datetime
> and some test data
> ColA ColB DateAdded
> ---
> 100 32 2-10-2005
> 100 16 3-01-2005
> 100 24 3-16-2005
> 100 20 1-15-2005
> 101 ...
> I would like a query which would return the single row whose ColA
> value = 100
> 100 24 3-16-2005
> since it has the latest date.
> Lars
>
> On Sat, 16 Apr 2005 00:17:02 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||It returns all 4 rows when I expect only 1.
On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
<Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:

>OK, whats wrong with that?
>declare @.ColA int
>Set ColA = 100
>select b.ColB, MAX(b.dateadded)
>from tblB b
>where b.ColA = @.ColA
>Group by ColB
>Jens Smeyer.
>
>"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
> news:e9i061t1nrhddkviiksi0vf3qdk3m2g3qo@.
4ax.com...
>|||Sorry youre right, forget about that, try this.
Select * from TableB Where
COLB =
(
Select TOP 1 COLB
From TableB
WHERE COLA = 100
Group by COLB
Order by MAX(dateadded) DESC
)
HTH (now), Jens Smeyer ;-)
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:j5k061hvmpdqbgjog7nf78qrq9ms5ki73k@.
4ax.com...
> It returns all 4 rows when I expect only 1.
> On Sat, 16 Apr 2005 01:31:54 +0200, "Jens Smeyer"
> <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote:
>
>|||Try This...
Select ColA, ColB, DateAdded
From TblB B
Where DateAdded =
(Select Max(DateAdded)
From TblB
Where ColA = B.ColA)
And ColA = @.ColA
Leave out the last row ( And ColA = @.ColA) to get the results for all ColA
values
"larzeb" wrote:

> I have two tables:
> tblA tblB
> ---
> ColA int PK ColA int FK
> .... ColB int PK
> DateAdded datetime
> where tblB/ColA references tblA/ColA.
> I need to find the most recently added row from tblB given a value of
> ColA.
> I wrote the following but wondered if there was a better, more
> efficient method.
> declare @.ColA int
> select ColB, p.dateadded
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA and
> b.dateadded = (select max(b.dateadded)
> from tblB b
> inner join tblA a on b.ColA = a.ColA
> where a.ColA = @.ColA)
> TIA Lars
>
>sql

more efficient - exists or in

Which is more efficient:

Select * from table1 where id in (select id from table2)

or

Select * from table1 where exists(select * from table2 where
table2.id=table1.id)On Wed, 11 Aug 2004 14:53:52 +0100, Trev@.Work wrote:

>Which is more efficient:
>Select * from table1 where id in (select id from table2)
>or
>Select * from table1 where exists(select * from table2 where
>table2.id=table1.id)

Hi Trev,

That question has no one correct answer; it depends on lots of factors,
such as table structures, whether there are indexes, etc. If you really
want to know, you'll have to test it for each specific situation. I think
that in many cases, the execution plan will be equal. And you firgot to
include the third option:
Select table1.* from table1
inner join table2 on table2.id = table1.id

Another important thing to remember: when you change the query to find
rows not in the other table, behaviour of the first query will become
unpredictable by NULL values in table1.id and table2.id:

CREATE TABLE table1 (id int)
CREATE TABLE table2 (id int)
INSERT table1 (id) SELECT 1
INSERT table1 (id) SELECT 3
INSERT table1 (id) SELECT NULL
INSERT table2 (id) SELECT 1
INSERT table2 (id) SELECT 2
INSERT table2 (id) SELECT NULL
Select * from table1 where id not in (select id from table2)
Select * from table1 where not exists(select * from table2 where
table2.id=table1.id)
Select table1.* from table1
left join table2 on table2.id = table1.id
where table2.id is null
DROP TABLE table1
DROP TABLE table2

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||'Exists' more efficient. SQL Server hardly calculates 'in' comprassions|||Trev@.Work (no.email@.please) writes:
> Which is more efficient:
> Select * from table1 where id in (select id from table2)
> or
> Select * from table1 where exists(select * from table2 where
> table2.id=table1.id)

In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
NOT EXISTS, although I have not confirmed this.

Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
out there are some gotchas with NOT IN.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See
http://groups.google.nl/groups?hl=n...ver.programming

Gert-Jan

"Trev@.Work" wrote:
> Which is more efficient:
> Select * from table1 where id in (select id from table2)
> or
> Select * from table1 where exists(select * from table2 where
> table2.id=table1.id)

--
(Please reply only to the newsgroup)|||On Wed, 11 Aug 2004 21:53:07 +0000 (UTC), Erland Sommarskog wrote:

> Trev@.Work (no.email@.please) writes:
>> Which is more efficient:
>>
>> Select * from table1 where id in (select id from table2)
>>
>> or
>>
>> Select * from table1 where exists(select * from table2 where
>> table2.id=table1.id)
> In 6.5 EXISTS and NOT EXISTS were often big winners over IN and NOT IN.
> In SQL 2000, I have feeling that IN/NOT IN are recast into EXISTS
> NOT EXISTS, although I have not confirmed this.
> Personally, I always use EXISTS and NOT EXISTS, and as Hugo pointed
> out there are some gotchas with NOT IN.

One follow-up question: is there a performance difference between

select * from table1 where exists(select * from table2 where
table2.id=table1.id)

and

select * from table1 where exists(select id from table2 where
table2.id=table1.id)

(assuming that table1.id and table2.id are clustered primary keys)|||> One follow-up question: is there a performance difference between
> select * from table1 where exists(select * from table2 where
> table2.id=table1.id)
> and
> select * from table1 where exists(select id from table2 where
> table2.id=table1.id)

There are not difference in
exists ( select * ...
exists ( select 1 ...
exists ( select id ...
SQL Server execute second expression for all situations|||On Thu, 12 Aug 2004 19:26:57 +0300, Garry wrote:

>> One follow-up question: is there a performance difference between
>>
>> select * from table1 where exists(select * from table2 where
>> table2.id=table1.id)
>>
>> and
>>
>> select * from table1 where exists(select id from table2 where
>> table2.id=table1.id)
> There are not difference in
> exists ( select * ...
> exists ( select 1 ...
> exists ( select id ...
> SQL Server execute second expression for all situations

thanks|||Ross Presser (rpresser@.imtek.com) writes:
> One follow-up question: is there a performance difference between
> select * from table1 where exists(select * from table2 where
> table2.id=table1.id)
> and
> select * from table1 where exists(select id from table2 where
> table2.id=table1.id)
> (assuming that table1.id and table2.id are clustered primary keys)

As far as I know the * or id are only syntactic sugar in this case,
so as Garry says, it does not matter which you use.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp