Author |
Topic |
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-08 : 18:48:57
|
There have been no new Reader Challenges in a long time. I'm bored and wanted something challenging to play with. Got any difficult T-SQL problems, or know a good place with unsolved problems? |
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-09 : 00:03:06
|
How do you create an endless loop in T-SQL? How are you going to prove it? Go ahead, prove it.....I dare you!!!!MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-07-09 : 02:11:32
|
kselvia - write the code then and ask derrick to run it on his prodcution box to prove it.Duane. |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-09 : 04:18:11
|
Reminds me of the time I tried to open the event log in EM and got impatient waiting for it so I disconnected. (I was dialed up at the time.) Browsing to the log dir showed a 350MB log file growing by the several MB/second with 'Unable to send results to the client' messages (My disconnected session browsing the log being the client in question!) I had to shutdown the server before the log filled up the hard drive. |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-11 : 19:39:23
|
Here is a pretty good question posted on another forum. What is a set-based solution to solve this problem? I don't know the right answer. I know a few ways to do it, but there must be better ways.----------------------I have my table like this. The zip code column has zip code in 9 digits. Zipcode Code056790000 A101056800000 A101056810000 A101056820000 A101056890000 A101056900000 A102056901111 A102056901168 A103056910000 A102056920000 A102.......I have data like 100000 records in this view/table. I think the data format in the table is clear, if not i can provide more infoThis is how i want the output to be.... I can have two columns to so i can have it like ZipStart ZipEnd Code056790000 056890000 A101056900000 056901111 A102056901168 056901168 A103056910000 056920000 A102 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-11 : 21:22:04
|
I had it up to the point of creating the run groups but couldn't get from there to the answer. Can you demonstrate with this?create table zip33 (zip varchar(9), code varchar(4))insert zip33 select '056790000' ,'A101'insert zip33 select '056800000' ,'A101'insert zip33 select '056810000' ,'A101'insert zip33 select '056820000' ,'A101'insert zip33 select '056890000' ,'A101'insert zip33 select '056900000' ,'A102'insert zip33 select '056901111' ,'A102'insert zip33 select '056901168' ,'A103'insert zip33 select '056910000' ,'A102'insert zip33 select '056920000' ,'A102' |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-11 : 21:30:04
|
here you go:select code, min(zip) as ZipStart, max(zip) as ZipEndfrom(select a.*, (select count(*) from zip33 b where a.code <> b.code and b.zip < a.zip) as RunGroupfrom zip33 a) agroup by code, rungrouporder by min(zip)- Jeff |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-11 : 21:39:25
|
Oh I see. Very cool I need to learn how to think like that. :) I was creating a running counter select a.*, (select count(*) from zip33 b where a.code = b.code and b.zip < a.zip) as RunGroupfrom zip33 a and that was getting me nowhere;) Thanks alot.For the record, I'll post your solution for the original poster rather than mine that used a couple of temp tables.[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21054849.html[/url]--Ken |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-11 : 23:23:05
|
It always amazes me how many forums there are out there. :)MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-07-12 : 19:58:14
|
Jeff, The original questioner came back with:----------------I tried using this code for my data but it takes a lot of time. I have around 50,000 rows in my view and the query takes more than two minutes to run. Could it be because the view has the data randomly spread in it..? Actualy the query has been running from 3 minutes now and no results yet......I tried running the inner code(subquery) and that takes more then a minute and does not return any results...Help!---------------What is plan B when there are 50,000 rows? 50,000 subqueries may take some time --KenYour Kung-Fu is not strong. -- 'The Core' |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-07-13 : 08:08:48
|
I would have thought it would be that slow. Are there any indexes involved? Should there be?Corey |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-07-13 : 09:08:29
|
yes, that is the major drawback -- it is not really intended for large sets of data. The thing is, by definition when you determine "streaks" in data you should only be appending newer rows in a particular sequence into your table ... that is, data should be added in the order of day1, day2, day3 and not day3,day1,day2 . If that is the case, a "posting" process as opposed to re-querying the entire transaction table would make sense -- keep track of new entries into the table, and then add to a summary table -- perhaps with a trigger. then for this reporting, use the summary table. that would be a fairly easy trigger to write.if data is added randomly, or not always in order, then it doesn't seem to make sense as to why you would want to calculate and return the results asked for (to me, anyway).- Jeff |
|
|
pawankkmr
Starting Member
4 Posts |
Posted - 2015-05-05 : 08:11:59
|
An effective one ...;WITH CTE AS( SELECT * , ROW_NUMBER() OVER (ORDER BY %%Physloc%%) - DENSE_RANK() OVER ( PARTITION BY code ORDER BY zip) rnk FROM zip33)SELECT MIN(Code) Code , MIN(zip) ZipStart , MAX(zip) ZipEnd FROM CTE GROUP BY rnkThanks !Pawan Kumar KhowalMSBISkills.com |
|
|
|