Wednesday 27 August 2008

How to de-dupe a record set using a temporary table (MSSQL)

Following on from the SQL Fire we witnessed a while ago I've just wrote this rather snazzy little query that does something similar.

The Problem:
The original problem is we have a little game with a scoreboard and one very dedicated player. Well, not only one player, many players but one is more determined to win than the rest. So to keep the small scoreboard looking inviting we decided to only show each player once, and to only show that players highest score.

I really wanted to do this in SQL as I figured it’s sorting and ordering abilities would be far easier than writing something in C#.

The Solutions:
Select the data into a temporary table which has a unique index on the user ID. The order of the select will ensure that only the highest score for each user will make it into the temporary table. Then select everything out of said temptable.

The Practical:


-- CREATE THE TEMPORARY TABLE
CREATE TABLE #tmpTable (
strFirstName NVARCHAR(255),
strLastName NVARCHAR(255),
intScore INT,
intUserID_PK INT
)

-- ADD THE UNIQUE INDEX
CREATE UNIQUE INDEX ix0 ON #tmpTable (intUserID_PK) WITH (ignore_dup_key = ON)

-- INSERT THE DATA
INSERT INTO #tmpTable SELECT strFirstName,strLastName,strValue, intUserID_FK
FROM tblScores

-- THE ORDER IS THE WRONG WAY TO WHAT YOU THINK
-- IT SHOULD BE, ASC NOT DESC...
-- TRY IT THE OTHER WAY AND YOU SHOULD GET THE LOWEST SCORES
ORDER BY intScore ASC
-- SELECT THE DATA
SELECT * FROM #tmpTable
-- WITH THE CORRECT ORDER BY
ORDER BY intScore DESC
GO -- CALL GO SO THIS BATCH GETS EXECUTED AND THE TABLE IS NOLONGER USED
DROP TABLE #tmpTable
GO -- DROP THE TABLE SO IT IS NOLONGER IN MEMORY


Update: If you use this within a stored procedure you will need to remove the GO statements or the SP won't compile

Tuesday 26 August 2008

Code updated, Blog not-so-updated

After a comment that an anonymous reader made I have revised and updated the ASP.NET Image Upload and Resize article. So BIG thanks to Anonymous for that one!

I''m struggling to finish the couple of posts i've started about recent activities so in absence of any real new content why not check out some of my other C# posts.

Monday 11 August 2008

Photoshop CS3 (Wndows XP) Crashes when I open more than one document.

The last couple of weeks at work have been HELL! All of a sudden Photoshop started bombing out any time I tried to have more than one image open at a time.

If I opened 2 or more images at the same time... CRASH!
If I created two new images... CRASH!
If I created one image then opened another from disk... CRASH!
If I opened an image from disk then created a new one... CRASH!

Why? Because I'd recently deleted a printer and set a new one as my default.

I just reinstalled Photoshop.. still the same.

Simply changing the default printer however; That sorted it out...
Come on Adobe, such an amature piece of codeing... How can opening multiple documents rely on the default printer... sort it out!

Check out http://kb.adobe.com/selfservice/viewContent.do?externalId=kb402704

Saturday 2 August 2008

Saturday Morning: Website Security Time...

Recently a couple of our sites have been subject to SQL Injection Attacks but non have suffered so hard as the one I'm currently securing.

I spent all day yesterday on it and I've been up since 9AM when the head IT guy from our client called (and woke) me.

So after I get this thing done, and then enjoy what is left of my weekend I plan on writing up my learnings and educating my co-workers. Keep an eye out for a blog post or two.