tag:blogger.com,1999:blog-29286778.post116021840885492538..comments2019-05-25T22:52:18.904-04:00Comments on SQL Garbage Collector: A scenario to ponder #2Omnibuzzhttp://www.blogger.com/profile/04342835880399785393noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-29286778.post-37880328971007215682010-01-28T07:17:00.131-05:002010-01-28T07:17:00.131-05:00Really Nice Logic.........
Thanks
Rahul BharavaReally Nice Logic.........<br />Thanks <br />Rahul BharavaRKBhttps://www.blogger.com/profile/13109984402461586554noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-40568393305470181932007-06-27T12:04:00.000-04:002007-06-27T12:04:00.000-04:00I'm curious as to why the greatest function hasn't...I'm curious as to why the greatest function hasn't been suggested:<BR/><BR/>Select *,greatest(a,b,c) as maxval from maxnum<BR/><BR/>Is it due to performance?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160921055516862402006-10-15T10:04:00.000-04:002006-10-15T10:04:00.000-04:00That was beautifully written Hugo. Will keep a not...That was beautifully written Hugo. Will keep a note of this :)<BR/>-OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160857128269522952006-10-14T16:18:00.000-04:002006-10-14T16:18:00.000-04:00Hi Omnibuzz,Denis' version, slightly optimized for...Hi Omnibuzz,<BR/><BR/>Denis' version, slightly optimized for readability, speen, and shorter to boot:<BR/><BR/>select *,<BR/> case when a > b and a > c then a<BR/> when b > c then b<BR/> else c end maxval<BR/>from maxnumHugo Kornelishttp://sqlblog.com/blogs/hugo_kornelis/default.aspxnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160823939396948582006-10-14T07:05:00.000-04:002006-10-14T07:05:00.000-04:00Denis, you came up with the most maintainable answ...Denis, you came up with the most maintainable answer we can have :) <BR/><BR/>Rob, Your first result with the cross apply was something I didn't think of (atleast not that implementation), and the second reply was more in the lines I thought of..<BR/>Here is the answer which I had in mind, just as Rob's<BR/><BR/>select a,b,c,<BR/>(<BR/>(<BR/>a + b + 2*c + <BR/>abs(a-b)<BR/>) + <BR/>abs(<BR/>a + b - 2*c + abs(a-b)<BR/>)<BR/>)/4<BR/>from maxnum<BR/><BR/>But, I do wonder that, though it feels great to give such convoluted answers with some complex logic and is perfectly fine from looking at it as a puzzle, I feel, from a manageability perspective, we are better off in following what Denis gave. <BR/><BR/>Now, from a performance perspective, is the computation better than the CASE statements? <BR/><BR/>I find that the the QEP doesn't help much in deciding which is better :)<BR/><BR/>-OmniOmnibuzzhttps://www.blogger.com/profile/04342835880399785393noreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160463793404056212006-10-10T03:03:00.000-04:002006-10-10T03:03:00.000-04:00Actually, this one is probably best:select a,b,c,(...Actually, this one is probably best:<BR/><BR/>select a,b,c,((a+b+abs(a-b))/2+c+abs((a+b+abs(a-b))/2-c))/2 as lgst<BR/>from maxnum<BR/><BR/>This is based on the principal that abs(a-b) + a + b = twice the largest of a,b.<BR/><BR/>If you think about it, for a>b, abs(a-b)+a+b = a-b+a+b = a+a<BR/>For b>a, abs(a-b)+a+b = b-a+a+b = b+b.<BR/><BR/>So then I've extended that to include c in the calculations too.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160461739223136012006-10-10T02:28:00.000-04:002006-10-10T02:28:00.000-04:00That's great, and definitely the quickest method. ...That's great, and definitely the quickest method. My first choice too. This one is interesting, as it's a lot easier to expand to more columns:<BR/><BR/>select m.a, m.b, m.c, min(vs.v), avg(vs.v), max(vs.v)<BR/>from<BR/> maxnum m<BR/> cross apply<BR/> (select m.a as v union all select m.b union all select m.c) vs<BR/>group by m.a, m.b, m.c<BR/><BR/>And of course, you could do it with unpivot too.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-29286778.post-1160412109848796732006-10-09T12:41:00.000-04:002006-10-09T12:41:00.000-04:00Here we go, I am sure there is a better wayI added...Here we go, I am sure there is a better way<BR/>I added the last 5 rows just to make sure that it is correct<BR/><BR/>create table maxnum (a int, b int, c int)<BR/><BR/>insert into maxnum values (1,2,3)<BR/>insert into maxnum values (4,3,5)<BR/>insert into maxnum values (23,44,23)<BR/>insert into maxnum values (23,45,45)<BR/>insert into maxnum values (3,2,1)<BR/>insert into maxnum values (3,1,2)<BR/>insert into maxnum values (1,3,2)<BR/>insert into maxnum values (2,1,3)<BR/>insert into maxnum values (2,3,1)<BR/><BR/>select *, case when a > b then <BR/>case when a >c then a else c end<BR/>when b > c then b else c end maxval<BR/>from maxnumDenis the SQL Menacehttp://sqlservercode.blogspot.com/noreply@blogger.com