SQL Select Command Help

[Guest]

I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this table. I need to get the most recent report for each client for each bureau. There are several bureaus so my output will look something like this.

ReportAN, ClientId, BureauAN, DateEntered
4, 2, 2, 6/10/2004
6, 2, 3, 6/1/2004
7, 3, 2, 6/5/2004

What would my select statement look like for this? Thanks.

 

[William Ryan eMVP]

SELECT ClientID
, BureauID
, MAX(DateEntered)
FROM ReportTable
Group By ClientID
Order By --whatever Field you want ordered by
With Rollup --Or Cube

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this

table. I need to get the most recent report for each client for each bureau.
There are several bureaus so my output will look something like this.

 

[William Ryan eMVP]

In addition, you may want to just do this client side and use
DataTable.Compute http://www.knowdotnet.com/articles/expressions.html
You can do some pretty advanced grouping with this too.

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/

I have a table that's as follows.

Report Table
ReportAN bigint (autonumber),
ClientId int,
BureauAN tinyint
DateEntered smalldatetime,

The ClientId is our customer, they will have several reports in this

table. I need to get the most recent report for each client for each bureau.
There are several bureaus so my output will look something like this.