while loop?

Question

Post reply

while loop?

achen9291

SSC-Addicted

Points: 460
More actions
June 27, 2012 at 1:57 pm

#260646

I'm trying to loop this set of code so that it gets the first, second, third,... tenth data in multiple blocks.
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2)
Group by ClientUserName, DestHost order by counts desc
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top 1 ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
this will get the first set of data and then a second set of data into another block. how can i make a loop so that i can get it to run up to 10 blocks. from first to second to third set of data etc.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 1

Use CROSS APPLY

Select b.*

from #ProxyLog_count_2 a

CROSS APPLY (Select ClientUserName, DestHost, count(DestHost) counts

from #ProxyLog_record

where ClientUserName = a.ClientUserName) b

achen9291 SSC-Addicted Points: 460 More actions · Answer 2

Can you tell me what the "a" and "b" stand for? and what the purpose of cross apply is?

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 3

achen9291 (6/27/2012)
Can you tell me what the "a" and "b" stand for? and what the purpose of cross apply is?

"a" and 'b" are alias

to understand CROSS APPLY check this link[/url]

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

achen9291 (6/27/2012)
I'm trying to loop this set of code so that it gets the first, second, third,... tenth data in multiple blocks.
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2)
Group by ClientUserName, DestHost order by counts desc
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in
(Select top 1 ClientUserName from #ProxyLog_count_2))
Group by ClientUserName, DestHost order by counts desc
this will get the first set of data and then a second set of data into another block. how can i make a loop so that i can get it to run up to 10 blocks. from first to second to third set of data etc.

I did not understand your requirement.Please provide table DDL, sample data and required output.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

achen9291 SSC-Addicted Points: 460 More actions · Answer 5

right here it has 3 blocks. i need it to display 10 of them. from the first data in #proxylog_count_2 then get the 2nd and third, etc.

the code above only displays 2 blocks for example.

achen9291 SSC-Addicted Points: 460 More actions · Answer 6

basically from the #proxylog_count_2 table. it displays clientusernames in a certain order. each block contains a different username. starting from number 1 in that table. i need it to loop so it can get 10 different blocks from the top 10. each in their own block.

achen9291 SSC-Addicted Points: 460 More actions · Answer 7

In section 5, it shows 3 sets of codes each for each block which displays in the screen shot. i need a loop so that i don't have to make a total of 10 sets. instead i want 1 or 2 sets of code which will loop it so that it can display 10 sets instead of me doing it 10 times.

Select ClientUserName, count(ClientUserName) counts into #ProxyLog_count_2 from #ProxyLog_count Group by ClientUserName order by Counts desc

--5. Top 3 user internet usage

Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record

where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2)

Group by ClientUserName, DestHost order by counts desc

Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record

where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in

(Select top 1 ClientUserName from #ProxyLog_count_2))

Group by ClientUserName, DestHost order by counts desc

Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record

where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2 where ClientUserName not in

(Select top 2 ClientUserName from #ProxyLog_count_2))

Group by ClientUserName, DestHost order by counts desc

rhythmk SSCertifiable Points: 7271 More actions · Answer 8

achen9291 (6/27/2012)
right here it has 3 blocks. i need it to display 10 of them. from the first data in #proxylog_count_2 then get the 2nd and third, etc.
the code above only displays 2 blocks for example.

Please provide table structure.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

achen9291 SSC-Addicted Points: 460 More actions · Answer 9

Sorry I don't know how to provide a table structure. How may I do this?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

Are you trying to get it to return multiple datasets back to the calling component or are you trying to find a way to perform this loop so a single dataset with all the blocks are returned? Multiple dataset returns is unusual which might be where the confusion is coming in with the others trying to help, but I've done them for different requirements.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

achen9291 SSC-Addicted Points: 460 More actions · Answer 11

I would have to say the first option. Multiple datasets. I'm confused myself :/

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

achen9291 (6/27/2012)
I would have to say the first option. Multiple datasets. I'm confused myself :/

Alright, it's not pretty but if you actually need multiple unique datasets returned what you want in this case is a cursor. Open a cursor up against proxylog_count_2 for a list of ClientUserName and feed them as a where clause to a select statement inside the cursor loop one at a time. When your external structure calls the procedure, it'll return one dataset for each of the ClientUserNames from ProxyLog_Count_2.

This is abnormal coding, so you're aware. You'll want to use a number of options for the cursor to reduce lock time against the database and you'll want to make sure the resultant front end recipient is expecting a dynamic number of datasets returned. If you want a hard number of results, say the top 10, use a Top 10 in the cursor declaration.

I'm usually against cursor coding but this is one of those instances where you really want to use it for what you want as a result. Under most circumstances you'd simply pull all this data at once and then return that with the clientUserName to the front end instead of multiple datasets.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

Evil Kraig F (6/27/2012)
achen9291 (6/27/2012)
I would have to say the first option. Multiple datasets. I'm confused myself :/
Alright, it's not pretty but if you actually need multiple unique datasets returned what you want in this case is a cursor. Open a cursor up against proxylog_count_2 for a list of ClientUserName and feed them as a where clause to a select statement inside the cursor loop one at a time. When your external structure calls the procedure, it'll return one dataset for each of the ClientUserNames from ProxyLog_Count_2.
This is abnormal coding, so you're aware. You'll want to use a number of options for the cursor to reduce lock time against the database and you'll want to make sure the resultant front end recipient is expecting a dynamic number of datasets returned. If you want a hard number of results, say the top 10, use a Top 10 in the cursor declaration.
I'm usually against cursor coding but this is one of those instances where you really want to use it for what you want as a result. Under most circumstances you'd simply pull all this data at once and then return that with the clientUserName to the front end instead of multiple datasets.

Wouldn't another option to be create a VIEW (or CTE) in front of each of the 3 statements that display results with a ROW_NUMBER() and then process in a loop rows 1-10, 11-20, 21-30, etc.?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

rhythmk SSCertifiable Points: 7271 More actions · Answer 14

achen9291 (6/27/2012)
Sorry I don't know how to provide a table structure. How may I do this?

hmm.....don't worry have a look on link in my signature for creating sample data.

However to get the actual structure follow below steps.

1. From SSMS connect to your DB

2. Expand Tables node

3. Goo to the table.

4. Right click and you will get option 'Script table as'

5. There you will get option Create to

6. Select any option and you will get actual structure with ready Create script.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂