A Blog on Analytics and Marketing

SAS, Marketing, Predictive Modeling, Statistics

Archive for February, 2009

SQL: Select the first/last row within a group in SQL

Posted by phillippeng on February 24, 2009

In SAS, you can first sort the dataset and then use first. or last. statement to select the first/last row within a group. There’s no such command in SQL. Below is my way to select the first/last row within a group in SQL.

Code:

select a.* into table_out
from table_in a,
(select id, min(rank) as minrank  from table_in group by id) b
where a.id = b.id and a.rank = b.minrank

I first create table b which is equivalent to sort the table_in and then match to itself. If I want to select the last row, I can then use max() function.

If min rank is not unique within each ID group, the above code will then choose all the rows with a rank equal to the min rank. To only select the first/last row within a group in SQL, we can create a global unique identifier and then match it. Below is the code:

select *, identity(int,1,1) as rowid into #temp from table_in
where condition_Expression order by id, rank

select a.*
into table_out
from table_in a,
(select id, min(rowid) as minrowid  from #temp group by id) b
where a.rowid = b.minrowid

In this example, we first use the indentity() function to create a new globally unique identifier rowid. Then we pick the row with minimum rowid. Be careful that you should sort your data first.

Added: March 9, 2009

We can use this to dedup the data within a group.

Posted in SQL | Tagged: , | 1 Comment »

SQL: Common Tasks

Posted by phillippeng on February 23, 2009

I use this  for SQL Server.

1. select a random sample from a table in SQL

select top 100 *
from table_name
order by newid()

Note: the trick is to use newid() function.

2. Rename a table in SQL:
use database_name;
Go
EXEC sp_rename ‘tablename_old’, ‘tablename_new’;
GO

3. Rename a column:
EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’

4. Drop a column:
alter table tablename
drop column col1, column2

Posted in SQL, Uncategorized | Tagged: , | Leave a Comment »