Challenging one!!! need real stud
-
It is really interesting puzzle, came up while desiging a complex query. Instead of describing the whole story I am putting a test data and required result. A quick response from some real SQL Guru would be appreciated.
I have following table and its sample data.
use pubs
GO
CREATE TABLE [dbo].[TestTbl] (
[Col_A] [char] (10) NOT NULL ,
[Col_B] [char] (10) NOT NULL ,
[vol] [int] NOT NULL
) ON [PRIMARY]
GO
insert into [dbo].[TestTbl] values ('A' ,'B' ,10)
insert into [dbo].[TestTbl] values ('A' ,'C' ,2)
insert into [dbo].[TestTbl] values ('A' ,'D' ,10)
insert into [dbo].[TestTbl] values ('A' ,'E' ,8)
insert into [dbo].[TestTbl] values ('A' ,'F' ,7)
insert into [dbo].[TestTbl] values ('A' ,'G' ,9)
insert into [dbo].[TestTbl] values ('A' ,'I' ,10)
insert into [dbo].[TestTbl] values ('AA' ,'B' ,2)
insert into [dbo].[TestTbl] values ('AA' ,'C' ,23)
insert into [dbo].[TestTbl] values ('AA' ,'D' ,8)
insert into [dbo].[TestTbl] values ('AAA' ,'G' ,9)
insert into [dbo].[TestTbl] values ('AAA' ,'H' ,9)
insert into [dbo].[TestTbl] values ('AAA' ,'B' ,45)
insert into [dbo].[TestTbl] values ('AAA' ,'C' ,45)
Go
Can anybody provide me a single select statement which will return a result described as following. Self join is no permitted. (Note: This is due to the nature of the query as in actual scenario above table is a result of a complex query.)
col_A, Anyone of the col_B values which has vol = max vol for col_A, max vol
A, B, 10
AA, C, 23
AAA, B, 45
Thanks
GOPAL
-
I cannot use self join as the table TestTbl is a result of a very complex query and I plan to replace TestTbl with the complex query in the final result as the final requirement is to provide this stated result in single select query.
If I use self join it will be very slow as I have to repeat the complex query which fetches me TestTbl.
I have a strong feeling that there is some solution for this in "ROLLUP" but I am not able to figure out.
-
I tried this many different ways and the only way I could get the results you were looking for is to use a correlated subquery with a self join.
use pubs
GO
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('TestTbl'))
DROP TABLE TestTbl
GO
CREATE TABLE TestTbl
(
Col_A char (10) NOT NULL ,
Col_B char (10) NOT NULL ,
vol int NOT NULL
)
insert into dbo.TestTbl values ('A' ,'B' ,10)
insert into dbo.TestTbl values ('A' ,'C' ,2)
insert into dbo.TestTbl values ('A' ,'D' ,10)
insert into dbo.TestTbl values ('A' ,'E' ,8)
insert into dbo.TestTbl values ('A' ,'F' ,7)
insert into dbo.TestTbl values ('A' ,'G' ,9)
insert into dbo.TestTbl values ('A' ,'I' ,10)
insert into dbo.TestTbl values ('AA' ,'B' ,2)
insert into dbo.TestTbl values ('AA' ,'C' ,23)
insert into dbo.TestTbl values ('AA' ,'D' ,8)
insert into dbo.TestTbl values ('AAA' ,'G' ,9)
insert into dbo.TestTbl values ('AAA' ,'H' ,9)
insert into dbo.TestTbl values ('AAA' ,'B' ,45)
insert into dbo.TestTbl values ('AAA' ,'C' ,45)
SELECT b.Col_A
, MIN(b.Col_B)
, b.vol
FROM
(SELECT Col_A, MAX(vol) vol
FROM TestTbl t
GROUP BY t.Col_A) a
JOIN TestTbl b ON a.Col_A = b.Col_A AND a.vol = b.vol
GROUP BY b.Col_A, b.vol
ORDER BY 1,2,3
returns
Col_A Col_B vol
---------- ---------- -----------
A B 10
AA C 23
AAA B 45
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Within a stored procedure you can also store the results of the complex query in a temporary table (e.g. SELECT INTO #complextable) and then use the self-join on the temporary table... That way you only have to run the complex query once and the joined table will be much smaller in size...
Kind regards,
Marcel van Waaijen
-
You state that you are limited in using a self join due to the initial statement being very complex and you dont what to repeat it in order to perform the self join. Why not run the statement once, populating the results into a temporary table (better still table variable SQL2K) and then self join the temporary table (table variable).
This would allow you to self join and only perform the complex statement once.
-
......is there an echo in here?
-
So basically the real life query is some horrendous multi-table join with a vast number of records.
I would agree that a temporary table is the way to go but I wouldn't use SELECT INTO.
I would CREATE TABLE #Tbl_Info
INSERT #Tbl_Info( your field list)
SELECT (whatever your complex query is)
If necessary add indices to your temporary table.
Do your self joins on your greatly reduced record set.
-
Here is a way to do it without any self joins. It actually returns the last occurrence of the maximum vol instead of the first, but this should be OK as you did say "Anyone of the col_B values which has vol = max vol for col_A"
select Col_A, substring(t,11,10) Col_B, cast(left(t,10) as int) vol from
(select Col_A, max(str(vol,10) + Col_B) t from TestTbl
group by Col_A) tmp
Mike McMahon
Senior Systems Analyst
Snowden Technologies -
Hey check it out!!!
select t.col_a,min(a.col_b) as col_b,max(t.vol) as vol from testtbl a,
(select col_a,max(vol) as vol from testtbl t group by col_a) t where
a.col_a=t.col_a and a.vol=t.vol group by t.col_a
Result of the above statement is
col_a col_b vol
---------- ---------- -----------
A B 10
AA C 23
AAA B 45
You can change the Min or Max Aggregate functions in the above statement and analyze the results.
Kindly send ur comments on this.
Thank you.
Vijay JDV
-
Hey Gopal:
If ur looking for the exact result what u have typed in ur message..check out this statement.
select (rtrim(t.col_a)+','+rtrim(min(a.col_b))+','+rtrim(cast(max(t.vol) as varchar(10))))
as Final_Result
from testtbl a,
(select col_a,max(vol) as vol from testtbl t group by col_a) t where
a.col_a=t.col_a and a.vol=t.vol group by t.col_a
This retrieves the following result:
Final_Result
--------------------------------
A,B,10
AA,C,23
AAA,B,45
Excuse me if the interpretation is not correct
Kindly send ur comments
Thank you.
Vijay JDV
-
Hey Gopal:
You can even try this. Its very funny that sometimes u can laugh at the way you have written the query when u look into that after sometime again in the future.
select (rtrim(col_a)+','+rtrim(min(col_b))+','+rtrim(cast(max(vol) as varchar(10))))
as Final_Result from testtbl group by col_a
Result is:
Final_Result
--------------------------------
A,B,10
AA,B,23
AAA,B,45
This is the Modified Statement of my previous ones..Isn't it funny...
Send ur comments
Thank You.
Vijay JDV
-
Thanks for all you guys to participate in resolving this subtle query. I got my result from you valuable inputs.
Special thanks to Mike McMahon and Vijay JDV for actively posting modified results.
Gopal
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply