CODEWITHSUNDEEP
sqlsql-server
James Parish
James Parish

Reputation: 337

SQL Top 1 in XML Path ('')

The following code:

    select(

    select m.Code + '   ' + cast(m.Completed as varchar(max)) + '   '+ cast(ol.Billed as varchar(max)) + '  ' + cast(m.Delete as varchar (max)) 
    from matterdetails as m
    full join officeledger as ol on ol.id=m.id
    order by ol.Billed desc 

    for xml path(''))

returns the results

    Code       Completed     Billed        Deleted
    --------   -----------   -----------   -----------
    HK168/03   Mar 30 2012   Aug 17 2011   Mar 30 2012
    HK168/03   Mar 30 2012   Feb 24 2011   Mar 30 2012
    HK168/03   Mar 30 2012   Dec 23 2010   Mar 30 2012
    FT080/03   Apr  4 2012   Mar 29 2012   Apr  4 2012
    FT080/03   Apr  4 2012   Feb  9 2012   Apr  4 2012
    FT080/03   Apr  4 2012   Oct 20 2011   Apr  4 2012

    etc.

whereas i require the results

    Code       Completed     Billed        Deleted
    --------   -----------   -----------   -----------
    HK168/03   Mar 30 2012   Aug 17 2011   Mar 30 2012
    FT080/03   Apr  4 2012   Mar 29 2012   Apr  4 2012

    etc.

I know that i have to insert a select top 1 somewhere to limit the ol.Billed results, but i am unsure where. Any advice would be great.

Upvotes: 0

Views: 358

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239694

You could replace:

full join officeledger as ol on ol.id=m.id

with:

full join (select id,MIN(Billed) as Billed from officeledger group by id) as ol = ol.id = m.id

There should also be a way to do this with ROW_NUMBER(), but I won't write it unless you need it.

Upvotes: 1

Gonzalo.-
Gonzalo.-

Reputation: 12672

try this: It will join with the max Billed Date for each Id

   select(
    select m.Code + '   ' + cast(m.Completed as varchar(max)) + '   '+ cast(ol.Billed as varchar(max)) + '  ' + cast(m.Delete as varchar (max)) 
    matterdetails as m
    full join (select id, Max(billed) as 'Billed' from officeledger) as ol on ol.id=m.id
        order by ol.Billed desc 
for xml path(''))

Upvotes: 1

Related Questions