April 14, 2010 at 12:11 pm
How do I code my select statement?
I recall doing this in PL/SQL years ago, but I'm not familiar with the TSQL flavor.
create table #table1
(
field1 int,
field2 datetime
)
create table #table2
(
field1 int,
field2 datetime
)
insert into #table1
(field1, field2)
values
(1, '05/15/2010')
insert into #table2
(field1, field2)
values
(1, '01/15/2010'),
(1, '05/30/2010'),
(1, '07/31/2010')
select #table1.field1, lookup.line_count
from #table1
inner join (
select field1, count(*) line_count from #table2 where field2 > #table1.field2 group by field1
) lookup
on #table1.field1 = #table2.field1
-- the select statement should return field1 value 1, and line_count value 2.
April 14, 2010 at 12:25 pm
Make it easy on us, show us what the result set should look like based on your sample data. They do say a picture is worth a thousand words, and your verbal description isn't even close to a thousand words.
April 14, 2010 at 12:26 pm
I think this will work for what you want to achieve:
select #table1.field1, count(*)
from #table1
inner join #table2 on #table1.field1 = #table2.field1 and #table2.fiel2 > #table1.field2
group by #table1.field1
The probability of survival is inversely proportional to the angle of arrival.
April 14, 2010 at 12:26 pm
Actually there was a small typo to the select statement.
select #table1.field1, lookup.line_count
from #table1
inner join (
select field1, count(*) line_count from #table2 where field2 > #table1.field2 group by field1
) lookup
on #table1.field1 = lookup.field1
result should be
field1 line_count
1 2
April 14, 2010 at 12:27 pm
select t1.field1, lkp.line_count
from #table1 t1
inner join (
select t2.field1, count(*) line_count
from #table2 t2
Inner Join #table1 tt
On tt.field1 = t2.field1
where t2.field2 > tt.field2
group by t2.field1
) lkp
on t1.field1 = lkp.field1
I believe this will work for you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 12:28 pm
Mister Ken (4/14/2010)
Actually there was a small typo to the select statement.select #table1.field1, lookup.line_count
from #table1
inner join (
select field1, count(*) line_count from #table2 where field2 > #table1.field2 group by field1
) lookup
on #table1.field1 = lookup.field1
result should be
field1 line_count
1 2
Those are the results I see for the query I provided.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 12:29 pm
Buddy, dint your subquery (which u used for the lookup alias) give the result u wanted?
Else, try this.
SELECT
T1.FIELD1 , COUNT(T2.FIELD2) COUNT_VAL
FROM
#FIELD1 T1
JOIN
#FIELD2 T2
ON
T1.FIELD1 = T2.FIELD1
AND
T2.FIELD2 >= T1.FIELD2
GROUP BY
T1.FIELD1
And as Lynn said, pls post your desired result in some visual representation...
April 14, 2010 at 12:33 pm
OMG, so many replies whilst i was decorating my code.. lol...:-D
Great going at SSC...
Cheers..
April 14, 2010 at 12:33 pm
COldCoffee (4/14/2010)
Buddy, dint your subquery (which u used for the lookup alias) give the result u wanted?
The initial query would provide an error.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 12:36 pm
Yes jason, very silly of me.. there were no join in that query.. i just watched that group by and said that.. thanks for pointing it out though 🙂
April 14, 2010 at 12:37 pm
I missed it as well until I copied it ans pasted to my query window - then it became obvious.
Also, I was surprised to see the sudden responses all while I was prettyfying my code. That was a very quick response on this question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 12:38 pm
Thanks for all the replies.
The example that I set forth was a very simplified version of what I wanted. I realize that I could have coded it the various ways as reflected in the replies.
However, I'm trying to determine if an "external" column from a joined table can be referenced within a derived table. That's what I really want to get the answer to. I've see this in PL/SQL years ago.
The result set should be one row. First column would be Field1 with a value of 1, and second column should be line_count with a value of 2. I tried to type it out, but the formatting didn't reflect well.
Any ideas on using an external column within a derived table?
April 14, 2010 at 12:40 pm
CirquedeSQLeil (4/14/2010)
Also, I was surprised to see the sudden responses all while I was prettyfying my code. That was a very quick response on this question.
Exactly... even i was stunned to see the lightning fast responses.. lol.. i was also decorating my code with iFCodes..:-D
Cooooollll 😎
April 14, 2010 at 12:45 pm
Mister Ken (4/14/2010)
Any ideas on using an external column within a derived table?
The external column throws an error every which way you try to configure it. My answer on this would be no.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 14, 2010 at 12:57 pm
I saw a coworker's snippet once, it reminded me vaguely of a similar idea. The statement below works.
UPDATE t1
SET t1.ID = NULL
FROM dbo.table1 t1
WHERE EXISTS(SELECT * FROM #table2 t2 WHERE t2.ID = t1.ID)
But I guess for my Select statement, I'll have to take a different approach.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply