i'm trying to imagine whats hapeneing there

but your code didnt work when i pasted it pasted error below

But basically Your doing another query and joining to All...

Thanks very much

its not quite there yet

i have modified a little using the clients.ID

ROW_NUMBER() over(partition by l.id order by c.dateopened Desc) num

and also

order by l.id DESC

This rownumber function is sequentially...

oh sorry i didnt realise you waqnted me to lay it out in the desired output

I had origionally put in the order it was which was Date desc

Here is...

okay this is my result set and my query is below,

Oh B*** i forgot to put in ClientID in the result set

Select'35372PF','Birks T','HPOT','1.Active','19/03/2012','01/01/1900' Union All

Select'35625PF','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900' Union All

Select'35625FH','Gregson M','HRNT','1.Active','14/03/2012','01/01/1900'...

cheers mate, no I havent solved it yet in terms of i havent had time to really look into partitinoning yet,

it was more an investigation on my part

I just carried...

adding this on the end is working slightly better

[Code]

&rs:Command=render%20&rs:format%20=xml

[/code]

I am trying to "render" the report as xml

i have tried doing this

http://sqlserver/reportserver?%2fReports%2fMyreport&ref=21599fh&rs%%5Bb%5D3aFormat=XML%5B/b%5D

but I get a dialoge asking me if i want to open or save the xml...

There is just under 1.8 million in one of the dbs im playing with which is productIDs

and 1.32 million in another db which is Legal precedents

theyre both things im playing...

Thanks grasshopper I will stick with my big table for now, and find out about partitioned tables.

Thanks for your kind assistance and appologies for my delayed response

Actually the data is all one table, But there are nearly two million records

I was looking to try and make...