February 2008 Entries
This morning I installed the latest SQL Server 2008 beta on my laptop, just to have a quick look at some of the spatial support. One of the sites I work on has a lot of GPS data, growing all of the time, and performance is something we are continuously working on. So, on an underpowered laptop, with slow discs, I did the following: Created a new column (Location) of type geography, alongside the existing Longitude and Latitude (both float) columns. Update the new Location column, creating a new Point object from the existing coordinates Ran queries to compare...
Having posted last night about row numbers and ranking, I realised in the shower this morning that there's actually an easy way to get the first and last rows per group, just by using ROW_NUMBER. The trick is to PARTITION, meaning that the row number is reset per partition. So I could do: SELECT Employee, TransactionTime,
ROW_NUMBER() OVER (PARTITION BY Employee, dbo.DateOnly(TransactionTime) ORDER BY TransactionTime) AS Row1, ROW_NUMBER() OVER (PARTITION BY Employee, dbo.DateOnly(TransactionTime) ORDER BY TransactionTime DESC) AS Row2
FROM ActualData
The result set is:
Employee
TransactionTime
Row1
Row2
Dave
01 Jan 08 09:00
1
4
Dave
01 Jan 08 13:00
2
3
Dave
01 Jan 08 14:00
3
2
Dave
01 Jan 08 18:00
4
1
Dave
02 Jan 08...
Sometimes things pass you by. I've been using the ROW_NUMBER() function SQL Server 2005 for ages, along with Common Table Expressions, to do server side paging, but had completely missed some other new functions. This is an odd occurrence for me as I tend to always read the new features of any product, but somehow a few of the additions to SQL passed me by. In particular the RANK function, which today saved me a huge headache and will simplify a ton of my queries. Over the past year I've been dealing with a lot of time based data...