Keith Devens .com

Thursday, February 28, 2008

'Limit' clause in MSSQL

Solution 1: SQL Server Equivalent To MySQL And PostgreSQL Limit Clause - Joseph Scott’s Blog. Note: assumes unique keys.

SELECT TOP n *

FROM tablename

WHERE key NOT IN (

    SELECT TOP x key

    FROM tablename

    ORDER BY key

)

Solution 2: Finally a LIMIT clause for MS SQL. (Same solution recommended here, with a small explanation.)

select * from (

 select top 10 emp_id,lname,fname from (

    select top 30 emp_id,lname,fname

    from employee

    order by lname asc

 ) as newtbl order by lname desc

) as newtbl2 order by lname asc

Edit: The above is in SQL Server 2k... for 2005 there's a new row_number() function.

Permalink | 2 Comments | Tags: Microsoft, SQL | id 8382

11:24 (utc-6)
edited: 17:17 (utc-6)

Tuesday, October 16, 2007

DBSlayer (via).

The DBacesslayer aka DBSlayer aka Släyer (as we like to call it when we're feeling ironically heavy metal) is a lightweight database abstraction layer suitable for high-load websites where you need the scalable advantages of connection pooling. Written in C for speed, DBSlayer talks to clients via JSON over HTTP, meaning it's simple to monitor and can swiftly interoperate with any web framework you choose.

¶ (0) Tags: [Databases, Json]

Tuesday, May 30, 2006

SQL Server Dumper. Let's you dump SQL Server tables like mysqldump lets you do for MySQL. Unfortunately, it's not scriptable. I'm not sure what SQL Server has built-in to dump tables, but if you need something just once or infrequently this should work for you.

¶ (0) Tags: [Databases, Software]

Monday, March 13, 2006

IBM developerWorks: Exploring Active Record.

Active Record [is] the persistence engine behind Ruby on Rails. Active Record bucks many Java conventions, from the typical configuration mechanisms to fundamental architectural choices. The result is a framework that embraces radical compromises and fosters radical productivity.

To read.

¶ (1) Tags: [Object-Relational mapping, Ruby on Rails, To Read]

Saturday, August 27, 2005

MySQL Reference Manual :: A.2.8 MySQL server has gone away. If you get that error while trying to do a large import, change the max_allowed_packet setting to something big enough to be able to cover your largest SQL statement in the import file. This is usually a problem when you export your data with MySQL's extended SQL insert format, since an entire table results in just one insert statement.

¶ (0) Tags: [MySQL]

Tuesday, March 9, 2004

Boo MySQL

I'm normalizing some database tables more than I was hoping to get away with. I would have gotten away with it too, if it wasn't for you meddling kids... er, if I didn't need the ability to rename what I was planning to make a key.

Because MySQL doesn't support subqueries (if there was ever a case of worse-is-better, it's PHP and MySQL), the following very simple query:

UPDATE Bookmark_Keywords SET Bookmark_Keyword_Id = (

   SELECT Bookmark_Keyword_Id

   FROM Bookmark_Keyword_Info

   WHERE Bookmark_Keyword_Name = Bookmark_Keyword

)

is going to require that I write procedural code to update the table. Bust.

Update: For future reference, here's the code I had to write:


<?php
$sql = 'SELECT DISTINCT Bookmark_Keyword_Info.Bookmark_Keyword_Id id, Bookmark_Keyword_Name name
   FROM Bookmark_Keyword_Info
   INNER JOIN Bookmark_Keywords ON(Bookmark_Keyword_Name = Bookmark_Keyword)
   ORDER BY Bookmark_Keyword_Name'; //reformatted to fit layout
$rs_id = mysql_query($sql, $conn) or die("Couldn't select your keywords: ".mysql_error());
while($row = mysql_fetch_assoc($rs_id)){
    $sql = "UPDATE Bookmark_Keywords
        SET Bookmark_Keyword_Id = $row[id]
        WHERE Bookmark_Keyword = '$row[name]'"; // reformatted to fit layout
    print_r($row);
    echo '<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
    print_r($sql);
    mysql_query($sql, $conn) or die("Couldn't update your table: ".mysql_error());
    echo '<br>';
}
?>

Any formatting in there (print_r, echo), etc., was for me to see that the right thing was going to happen before I uncommented the mysql_query line. And of course, this is just "one-off" code... for instance, I didn't addslashes() on $row[name] because I knew none of my names had slashes or quotes in them.

Permalink | 2 Comments | Tags: MySQL, PHP, Programming | id 4767

04:48 (utc-5)
edited: 12:21 (utc-5)

May 2008

Sun

Mon

Tue

Wed

Thu

Fri

Sat

Tag: Databases

Thursday, February 28, 2008

'Limit' clause in MSSQL

Friday, October 26, 2007

Tuesday, October 16, 2007

Monday, September 17, 2007

Monday, July 9, 2007

Monday, June 25, 2007

Thursday, January 18, 2007

Friday, January 12, 2007

Tuesday, May 30, 2006

Monday, May 1, 2006

Monday, March 13, 2006

Thursday, February 23, 2006

Saturday, February 11, 2006

Friday, November 25, 2005

Sunday, September 25, 2005

Saturday, August 27, 2005

Tuesday, March 9, 2004

Boo MySQL

Recent comments