Thursday, January 17, 2013

Activerelation, stop being so clever.


I nearly flipped by lid today, when doing what should have been a simple activerelation query to get the  'last record'.

Foo.order('column')
SELECT * FROM foo ORDER BY column ASC;


Foo.order('column ASC').last
SELECT TOP 1 * FROM foo ORDER BY column DESC;



Foo.order('column DESC').last
SELECT TOP 1 * FROM foo ORDER BY column ASC;


Foo.order('column DESC').first
SELECT TOP 1 * FROM foo ORDER BY column DESC;


Foo.order('column ASC').last
SELECT TOP 1 * FROM foo ORDER BY column ASC;



Foo.order('column ASC').limit(1)
SELECT TOP 1 * FROM foo ORDER BY column ASC;

Foo.order('column DESC').limit(1)
SELECT TOP 1 * FROM foo ORDER BY column DESC;

Something tells me I'll never like SQL server's TOP 1 syntax, as I spent ages just staring at the end of the queries and wondering "why the heck isn't it choosing the last record, I told it to sort DESC!"

I miss my LIMITs.


Post a Comment