Archive for the 'sql' Category

Querying the Latest Record

Published
by
admin
on May 23, 2008
in sql
. 0 Comments

It was my long standing problem when querying the latest history of a record. The scenario is that I have a parent table and its transaction log (say, history records) are stored in a child table linked by the parent’s primary key id. The parent table stores the main information regarding a transaction while the child table stores historical data of what happened in the parent’s table info.

parent-child relationship

Continue reading ‘Querying the Latest Record’

Returning the ID of a Newly Created Row in Oracle’s PL-SQL

Published
by
admin
on June 22, 2007
in sql
. 2 Comments

Having years of background developing applications using MSSQL Server as the database, I encountered a roadblock when my stored procedure is required to return the id for my newly created row in oracle.

Immediate Work-Around

Since all my tables have a timestamp field, the most obvious thing to do is to query the topmost row of my table sorted by the timestamp field. For added accuracy, I also filtered my query against the userid which created the affected row.

Continue reading ‘Returning the ID of a Newly Created Row in Oracle’s PL-SQL’

Dynamically number rows in a SELECT T-SQL statement (SQL Server 2000)

Published
by
admin
on July 10, 2006
in sql
. 10 Comments

I encountered some TSQL Select problem where I want my result set to have a “number” on the first column. If for example you have a simple select statement

USE Northwind
SELECT      CustomerID
        ,   CompanyName
FROM        Customers
ORDER BY    CustomerID

which outputs

Continue reading ‘Dynamically number rows in a SELECT T-SQL statement (SQL Server 2000)’