Hi All,
Using trace 1204, 1305 I found details of many deadlocks in errorlog. But in
some deadlock table that is locked is nowhere used by stored procedure
executed by participating SPIDs.
If there is no operation on a table by specified stored proceudre then how
this table is causing deadlock.
Thanks & Regards,
Subhash
-----Original Message-----
From: Subhash Chandra [mailto:[Email address protected]
Sent: Wednesday, May 23, 2007 3:20 PM
To: 'Ashish Mohite '; 'LazyDBA Discussion'
Subject: RE: query-urgent
Hi Ashish,
following query return the desired resultset.
select distinct base.a, base.b,
(select top 1 c from test1 where d = 'Telephone' and a = base.a and b =
base.b order by c) as Telephone1, (select top 1 c from test1 where d =
'Telephone' and a = base.a and b = base.b and c not in (select top 1 c from
test1 where d = 'Telephone' and a = base.a and b = base.b order by c) order
by c) as Telephone2, (select top 1 c from test1 where d = 'Telephone' and a
= base.a and b = base.b and c not in (select top 2 c from test1 where d =
'Telephone' and a = base.a and b = base.b order by c) order by c) as
Telephone3, (select top 1 c from test1 where d = 'Fax' and a = base.a and b
= base.b order by c) as Fax1, (select top 1 c from test1 where d = 'Fax'
and a = base.a and b = base.b and c not in (select top 1 c from test1 where
d = 'Fax' and a = base.a and b = base.b order by c) order by c) as Fax2,
(select top 1 c from test1 where d = 'Fax' and a = base.a and b = base.b
and c not in (select top 2 c from test1 where d = 'Fax' and a = base.a and
b = base.b order by c) order by c) as Fax3 from test1 base
The numbers of columns depends on your requirement.
You can use other way to return 2nd and 3rd (or more if required) highest
values.
Thanks & Regards,
Subhash
-----Original Message-----
From: Ashish Mohite
[mailto:mssqldba-ezmlmshield-x63997230.[Email address protected]
Sent: Wednesday, May 23, 2007 2:22 PM
To: LazyDBA Discussion
Subject: query-urgent
Hi, <http://www.dnb.co.in>
<http://www.dnb.co.in>
I have some problem while writing query. I am providing you sample data can
you help me? <http://www.dnb.co.in>
<http://www.dnb.co.in>
Sample data <http://www.dnb.co.in>
Column a b c d e
f <http://www.dnb.co.in>
19 356 91 22 25243269
Telephone <http://www.dnb.co.in>
19 356 91 22 25227847
Telephone <http://www.dnb.co.in>
19 356 91 22 25222994
Fax <http://www.dnb.co.in>
22 356 91 4111 256031
Telephone <http://www.dnb.co.in>
And output like <http://www.dnb.co.in>
A b c d phone1
phone2 fax1 <http://www.dnb.co.in>
19 356 91 22 25243269
25227847 25222994 <http://www.dnb.co.in>
<http://www.dnb.co.in>
And strictly I want use query only. <http://www.dnb.co.in>
<http://www.dnb.co.in>
<http://www.dnb.co.in>
Waiting for your reply <http://www.dnb.co.in>
Thanks in advance. <http://www.dnb.co.in>
<http://www.dnb.co.in>
Best Regards, <http://www.dnb.co.in>
<http://www.dnb.co.in>
Ashish <http://www.dnb.co.in>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a dba
job: http://jobs.lazydba.com To subscribe : http://www.LazyDBA.com To
unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page