Recent order for each customer


set nocount on;

use

MSDN_forum;

if

object_id(‘dbo.mytable1’) is not null drop table dbo.mytable1;

CREATE

TABLE mytable1 (ID int identity(1,1), myid int, orderdate datetime, val char(10))

insert

into mytable1

SELECT

1, ‘1/1/2008’,‘tools’

UNION

ALL SELECT 1, ‘2/1/2008’,‘books’

UNION

ALL SELECT 1, ‘3/1/2008’,‘Rbooks’

UNION

ALL SELECT 2, ’11/1/2008′,‘mybooks’

UNION

ALL SELECT 2, ’12/1/2008′,‘books’

UNION

ALL SELECT 2, ‘3/1/2008’,‘books’

 

SELECT

myid, CAST(SUBSTRING(binstr,1,8) as DATETIME) as orderdate,

CAST

(SUBSTRING(binstr,9,10) as char(10)) AS val

,

CAST(SUBSTRING(binstr,19,23) as int) AS id

FROM

(

SELECT

myid, MAX(CAST(orderdate as BINARY(8))

+

CAST(val as BINARY(20))

+

CAST(id as BINARY(4))

)

AS binstr

FROM

mytable1

GROUP

BY myid ) AS D;

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s