Archive | November, 2013

The King is dead, Long Live the King

8 Nov

So, SQLPass 2013 has finished. Of course, all I read about it from those that attended are good things and having attended the 2012 conference I can believe them.

So, when are you going to start planning for SQLPass November 2014?

Yes, it might be 11 months away but look at what the fees have increased to at that point:
SQLPass01
After December 6th it goes up by $300 – almost a third. Then it climbs steadily in several steps from that point onward. That’s a lot of money to save just by making the decision early.

If you’re paying for it yourself (which is how I do it) then that’s more money to be spent on the rest of the organising that goes with this – flights, hotels, food and suchlike.
If your company is paying for it I’m sure they’d appreciate an early request to spend a little less – then you both win.

Of course, paying this early means that you have no idea what the content will be but I wouldn’t worry about that. I’ve held off registering for some of the smaller conferences before, because there may be only a handful of speakers and even less presentations of interest to myself.
But this is the largest event of this type – there is no shortage of high-quality speakers. If you think you have a SQL Server related interest that nobody here can help you with then I suspect quite a few of the speakers would like the challenge of talking to you.

Pay early and then wait to see what you’ve bought – in this case it’s the best way to do it.

http://www.sqlpass.org/summit/2013/About/WhoShouldAttend/AttendeesShareTheirExperience.aspx

Row_Number – Basic Example

4 Nov

Seriously, you shouldn’t get excited about a function, command or whatever. It’s just a tool for a job and not the reason you live.
But then in SQL 2005 Row_Number was introduced.
I didn’t do a great deal of work with SQL 2000 but I do remember the fun to be had when trying to find something as simple as the maximum or minimum value for something for a particular person or product. Another common problem – trying to remove duplicated data was certainly more fun than should be legally allowed. Subqueries abound and T-SQL code tied itself into nice little knots to achieve something that shouldn’t really be that difficult.

So lets start with a very basic example, just adding a sequential number the rows of a table:

CREATE TABLE #test1( 
	TestID	INT); 

INSERT INTO #test1 
        ( TestID ) 
VALUES  (3), 
	(4), 
	(7), 
	(9), 
	(15), 
	(22); 

Nothing exciting in that. If we now SELECT from that and add a ‘count’ that changes every time TestID changes:

SELECT TestID, 
       ROW_NUMBER() OVER (ORDER BY TestID) 
FROM #test1; 

The result has the TestID followed by the Row_Number result:
Row_Number01
Not a wild amount of use in this case but supposing we had several rows with the same ID and different values alongside, and we want to select the row for each ID that has the highest amount stored against it.

CREATE TABLE #test2( 
	TestID		INT, 
	TestAmount	INT 
	); 

INSERT INTO #test2
        ( TestID, TestAmount )
VALUES  ( 1, 100), 
	( 1, 101), 
	( 1, 95), 
	( 2, 15), 
	( 3, 12), 
	( 3, 1), 
	( 4, 800), 
	( 4, 123), 
	( 4, 192), 
	( 4, 55); 

Now look at the result of a Row_Number query where I’ve ordered it by the TestAmount and partitioned it by the TestID. The PARTITION BY effectively tells the function how to seperate the data, in this case count within the same value for TestID. When TestID changes the count will reset.

SELECT TestID, 
	TestAmount, 
	ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestAmount DESC) AS [Sequence] 
FROM #test2; 

The result of this query is:
Row_Number02
From this you can see that the highest value has been allocated a Sequence value of 1. Knowing this it is easy to filter on that value.
Combine it with a CTE and you can select the rows with the highest value:

WITH SortedAmounts AS ( 
	SELECT TestID, 
		TestAmount, 
		ROW_NUMBER() OVER (PARTITION BY TestID ORDER BY TestAmount DESC) AS [Sequence] 
	FROM #test2 
	) 
SELECT  TestID, 
		TestAmount 
FROM SortedAmounts  
WHERE Sequence = 1; 

With this result:
Row_Number03