Is there a better way.

Question

Is there a better way.

douglas.allison-1136597

SSC-Addicted

Points: 444
More actions
March 25, 2010 at 10:41 am

#99124

Sorry to be a noob bit is there a better way to mark up a record in a recordset that is closest to the average?
The table is like this proof_of_concept
practicerandom_num
a10
b21
c23
d66
e185
f45
g154
h34
i22
Using the code below
BEGIN
SET NOCOUNT ON;
declare @avg as int -- declare the average integer
set @avg=(SELECT AVG(random_num) from tst_proof)--get the average of the data
select *,abs(random_num-(@avg)) as [count_from],'N' as [midpoint],random_num-(@avg) as [real_val] into #tmp1 from tst_proof
order by abs(random_num-(@avg))
update #tmp1
set midpoint='Y'
where practice=(select top 1 practice from #tmp1)
select * from #tmp1 order by random_num desc
end
it comes back with the correct answer but it just seems messy
e185123N123
g15492N92
d664Y4
f4517N-17
h3428N-28
c2339N-39
i2240N-40
b2141N-41
a1052N-52
So d is closest to the average and is flagged accordingly.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Greg Snidow SSCoach Points: 16106 More actions · Answer 1

Greetings. I'm not sure what you mean by 'messy', as it is kind of subjective. If you are talking about what your code looks like to the eye, then what you consider messy may not be what someone else considers messy. However, if you are of the opinion that your own code is messy, then you should try different styles of formatting it so it is in an easy to follow lay out. Now, if by messy you mean it is taking multiple steps to achieve one end result, which is updating your table, then there are ways to do that, as I've done below using a derived table.

IF OBJECT_ID('TempDB..#tst_proof','u') IS NOT NULL

DROP TABLE #tst_proof

CREATE TABLE #tst_proof

(

practice CHAR(1),

random_num INT,

midpoint CHAR(1),

real_val INT

)

INSERT INTO #tst_proof (practice,random_num)

SELECT 'a',10 UNION ALL

SELECT 'b', 21 UNION ALL

SELECT 'c', 23 UNION ALL

SELECT 'd', 66 UNION ALL

SELECT 'e', 185 UNION ALL

SELECT 'f', 45 UNION ALL

SELECT 'g', 154 UNION ALL

SELECT 'h', 34 UNION ALL

SELECT 'i', 22

DECLARE @avg FLOAT

SELECT @avg = (SELECT AVG(random_num) FROM #tst_proof)

UPDATE tp

SET midpoint = CASE WHEN t1.practice IS NOT NULL THEN 'Y' ELSE 'N' END,

real_val = random_num - @avg

FROM #tst_proof tp LEFT OUTER JOIN

(--derived table t1 finds the average, and can

--be joined to the table you are trying to update.

SELECT TOP 1

practice,

av = ABS(random_num - @avg)

FROM #tst_proof

ORDER BY ABS(random_num - @avg)

) t1

ON tp.practice = t1.practice

SELECT

*

FROM #tst_proof

ORDER BY random_num DESC

Now, you might look at that and think "that is way messier than my code", but to me it makes perfect sense. Also, I should note that this solution may not be best if you have a table with many records, and there could be several with values that are an equal distance to the mean. Then, how would you decide which one to flag as the closest to the mean? Or would you want to flag all of them as closest to the mean? Just some things to think about.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

douglas.allison-1136597 SSC-Addicted Points: 444 More actions · Answer 2

Hi Greg

Thanks for that the formatting is not the messy part as when I see it on Manager ets it's all tastefully indented etc.

Will have a look laters at yours to see if the costs are any better.

Cheers

Doug

Garadin One Orange Chip Points: 29613 More actions · Answer 3

douglas.allison-1136597 (3/29/2010)
Hi Greg
Thanks for that the formatting is not the messy part as when I see it on Manager ets it's all tastefully indented etc.
Will have a look laters at yours to see if the costs are any better.
Cheers
Doug

When you paste code in:

Select it all in SSMS, copy/paste it into the browser.

Highlight the code you pasted and then on the left click on the link. It will surround your code with tags and format it for the forums like Gregs.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Two things to be wary of:

- ORDER BY doesn't guarantee the order of inserting into a table. At best - you can count on it to guarantee the identity order if you have an identity column on the table you're inserting into.

- The TOP n predicate can return unreliable results unless you provide it with a matching ORDER BY.

So - you may find your results to be highly reliable on a single-user dev environment, with a *boom* occurring when this hits production.

The good news is - Greg's code avoids both issues, so you should probably lean towards his code for both. You may however consider using TOP 1 WITH TIES (since you could have several values closests to the avegage), or else the update process will arbtrarily pick one for you.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?