SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Tariq / Counts Puzzle / Latest Posts

RE: Counts Puzzle

uvraja-506197 — Mon, 15 Sep 2008 01:04:17 GMT

[size="4"]hi i am not clear this question and answer pls tell again other solution[/size:discuss:]

RE: Counts Puzzle

skyline666 — Thu, 28 Aug 2008 01:45:53 GMT

[quote][b]dgvsbabu (8/28/2008)[/b][hr]It should be 1,5,2,3,3,2[/quote]I don't think it should be, can you explain why?

RE: Counts Puzzle

Giri Duddu — Thu, 28 Aug 2008 01:19:10 GMT

It should be 1,5,2,3,3,2

RE: Counts Puzzle

Christian Buettner-167247 — Thu, 28 Aug 2008 00:54:48 GMT

[quote][b]Aleksandr Furman (8/27/2008)[/b][hr][quote][b]skyline666 (8/27/2008)[/b][hr]Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).[/quote]That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.[/quote]Hi Aleksandr,Local temporary tables are dropped when the stored procedure completes. The behaviour you describe applies not to stored procedures (unless you use global temp tables instead of local temp tables).Here is an example that creates a temp table within the stored procedure dbo.Test and tries to select from the table after the procedure completed:[code]CREATE PROCEDURE dbo.TestAS CREATE TABLE #Test (ColA int NOT NULL);GOEXEC dbo.Test;GOSELECT * FROM #Test;[/code]

RE: Counts Puzzle

skyline666 — Wed, 27 Aug 2008 07:46:15 GMT

RE: Counts Puzzle

Aleksandr Furman — Wed, 27 Aug 2008 07:36:54 GMT

[quote][b]skyline666 (8/27/2008)[/b][hr]Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).[/quote]That's not exactly true. Temp table exists for the life of the connection. Try to rerun same code in the same query window (same connection id) and you'll get an error saying that table already exists. It's a good practice to always clean up after yourself.

RE: Counts Puzzle

skyline666 — Wed, 27 Aug 2008 07:04:22 GMT

Yes thats a good point, temporary tables only exist for the life of the stored procedure (when prefixed with one #), I temporarily forgot about that ;).

RE: Counts Puzzle

Christian Buettner-167247 — Wed, 27 Aug 2008 06:52:39 GMT

[quote][b]YeshuaAgapao (8/26/2008)[/b][hr]It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.Better yet, re-write it so it uses table variables.[/quote]I think it is perfectly valid not to drop temp tables explicitly, since they go out of scope automatically.I am also curious to know why you favor table variables, especially for this example.

RE: Counts Puzzle

Anipaul — Wed, 27 Aug 2008 04:44:05 GMT

Good Question...

RE: Counts Puzzle

YeshuaAgapao — Tue, 26 Aug 2008 12:26:41 GMT

It don't, but you wouldn't want to see this coding practice (not dropping temp tables) in real applications, so one would want to set an example for that for noobs and semi-noobs that are still moldable.Better yet, re-write it so it uses table variables.

RE: Counts Puzzle

skyline666 — Tue, 26 Aug 2008 11:47:27 GMT

[quote][b]YeshuaAgapao (8/26/2008)[/b][hr]You forgot to put drops for you temp tables (#data, #category) at the end of the code block.[/quote]That doesn't affect the end result tho ;).

RE: Counts Puzzle

YeshuaAgapao — Tue, 26 Aug 2008 11:17:10 GMT

You forgot to put drops for you temp tables (#data, #category) at the end of the code block.

RE: Counts Puzzle

Aleksandr Furman — Tue, 26 Aug 2008 10:39:43 GMT

[quote][b]skyline666 (8/26/2008)[/b][hr][quote][b]ian treasure (8/26/2008)[/b][hr]Shouldnt this be SELECT C.CATID, COUNT(*)FROM #DATA DINNER JOIN #CATEGORY C ON (C.VAL1 = [u]DINNER.[/u]VAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID(was ON C.VAL1 = D.VAL1)[/quote]Nope, its this:SELECT C.CATID, COUNT(*)FROM #DATA D [b]INNER JOIN[/b] #CATEGORY C ON (C.VAL1 = DVAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID[/quote]When you're copying the code to query window remember to add proper line brakes so you don't end up with "DINER Join" :w00t: . Must've been hungry? Thanks for the laughs :hehe:

RE: Counts Puzzle

skyline666 — Tue, 26 Aug 2008 08:19:33 GMT

Round yours at 6 then :D

RE: Counts Puzzle

Chad Crawford — Tue, 26 Aug 2008 07:57:21 GMT

mmmmm.... Dinner Join.... sounds like an invitation to a barbeque.Reminds me of the QOD from 12/13/07 that used a MIDDLE JOIN. :)Chad

RE: Counts Puzzle

Jamie Longstreet-481950 — Tue, 26 Aug 2008 05:39:51 GMT

There was also that new [quote]grin nullgroup means no one invited[/quote] keyword NULLGROUP not far from the DINNER subquery.

RE: Counts Puzzle

Muhammad Tariq — Tue, 26 Aug 2008 05:10:09 GMT

hey,here is the details,1) inner join or join, its behavior is same, so there will be no effect if you replace inner join with join.2) parenthesis will not change its behavior, because we have only two conditions in where clause to check, parenthesis will play role when you will have multiple combination of OR and AND operators.so, i believe code is correct, :)

RE: Counts Puzzle

brewmanz — Tue, 26 Aug 2008 04:53:10 GMT

[quote][b]ian treasure (8/26/2008)[/b][hr]Shouldnt this be SELECT C.CATID, COUNT(*)FROM #DATA DINNER JOIN #CATEGORY C ON (C.VAL1 = [u]DINNER.[/u]VAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID(was ON C.VAL1 = D.VAL1)[/quote]You must be like me ... always thinking of food ;-)

RE: Counts Puzzle

ianT — Tue, 26 Aug 2008 03:10:01 GMT

AAAAAA. Knew there was something I'd missed.

RE: Counts Puzzle

skyline666 — Tue, 26 Aug 2008 03:07:15 GMT

[quote][b]ian treasure (8/26/2008)[/b][hr]Shouldnt this be SELECT C.CATID, COUNT(*)FROM #DATA DINNER JOIN #CATEGORY C ON (C.VAL1 = [u]DINNER.[/u]VAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID(was ON C.VAL1 = D.VAL1)[/quote]Nope, its this:SELECT C.CATID, COUNT(*)FROM #DATA D [b]INNER JOIN[/b] #CATEGORY C ON (C.VAL1 = DVAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID

RE: Counts Puzzle

ianT — Tue, 26 Aug 2008 02:45:47 GMT

Shouldnt this be SELECT C.CATID, COUNT(*)FROM #DATA DINNER JOIN #CATEGORY C ON (C.VAL1 = [u]DINNER.[/u]VAL1) OR (C.VAL1 IS NULL)GROUP BY C.CATID(was ON C.VAL1 = D.VAL1)

Counts Puzzle

Muhammad Tariq — Mon, 25 Aug 2008 23:43:14 GMT

Comments posted to this topic are about the item [B]Counts Puzzle[/B]