Adding another table to a query but only wanting the top 1 for each record

  • I currently have query that gives me the results i needs BUT i am needing to add a new table/column to the query.

    select name, state, id, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as app,

    version, maddr, city, zip, customers.phone, contacts.sort, contacts.email, install_date, population, customers.notes,

    CASE databasetype WHEN 1 THEN 'Vision' WHEN 2 THEN'SQL' ELSE'Unknown' END AS FileType,

    updt_date from customers

    vleft join contacts on contacts.number = customers.prim_contact

    left join custapps on customers.number = custapps.customer

    where customers.number > 0 and customers.deleted = 0

    and custapps.deleted = 0 and custapps.version > ''

    and (custapps.version in ('W800','W805','W900','W901'))

    and custapps.app in ('AC','AP','AR','BL','BP','CH','CL','CM','CT','EM','FA','GL','HR','IN','PA','PO','PY','RG','SA','SO','SS','ST','UB','US','VM','WO','GLG')

    order by customers.name, customers.state, custapps.app

    Now i am needing to add table patches, column patchnumber but it needs to match up on the customer, applicationname and only the highest created (date/time) columns

    so my current query will give me a listing of customers and all of their licensed application but now i am needing to add the patch information per application.

    ELECT [number]

    ,[customer]

    ,[DownloadID]

    ,[Created]

    ,[UserName]

    ,[ApplicationName]

    ,[Version]

    ,[PatchNumber]

    ,[FileName]

    ,[Status]

    FROM [CIS].[dbo].[patches]

  •  

    SELECT ...same_as_before_except_add_patches_columns...
    FROM customers

    LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num
    FROM [CIS].[dbo].[patches]
    ) AS patches ON patches.customer = customers.customer AND patches.row_num = 1

    left join contacts on contacts.number = customers.prim_contact
    ...rest_of_query_same_as_before...

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • i added that code and got closer but i also needed the applicationname to match the custapps.app column so i added it to the query. however i am only get 1 application patchnumber per customer. so if they have AP, AC, BL only the BL is showing a patchnumber the others are all null.

    select a.name, a.state, a.id, (case when custapps.app = 'GLG' then 'GASB' else custapps.app end) as app, patches.PatchNumber,

    custapps.version, a.maddr, a.city, a.zip, a.phone, contacts.sort, contacts.email, install_date, population, a.notes,

    CASE databasetype WHEN 1 THEN 'Vision' WHEN 2 THEN'SQL' ELSE'Unknown' END AS FileType,

    updt_date from customers a

    left join contacts on contacts.number = a.prim_contact

    left join custapps on a.number = custapps.customer

    LEFT OUTER JOIN (

    SELECT *, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]

    ) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num = 1

    where a.number > 0 and a.deleted = 0

    and custapps.deleted = 0 and custapps.version > ''

    and (custapps.version in ('W800','W805','W900','W901'))

    and custapps.app in ('AC','AP','AR','BL','BP','CH','CL','CM','CT','EM','FA','GL','HR','IN','PA','PO','PY','RG','SA','SO','SS','ST','UB','US','VM','WO','GLG')

    order by a.name, a.state, custapps.app

    Attachments:
    You must be logged in to view attached files.
  • LEFT OUTER JOIN (

    SELECT *, ROW_NUMBER() OVER(PARTITION BY customer, ApplicationName ORDER BY Created DESC) AS row_num FROM [CIS].[dbo].[patches]

    ) AS patches ON patches.customer = a.number and patches.ApplicationName = custapps.app AND patches.row_num = 1

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Thank you so very much. I will definitely do some PARTITION BY learning.

    Thanks...

  • Great.  I guess that confirms it gave you the results you needed :-).

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Yes sir, got some other things to work thru but that was the hurdle.

    you know how it goes when you inherit someone else's project that has left the company a long time ago...

    Thanks.

  • An alternative is to use OUTER APPLY:

    OUTER APPLY (SELECT TOP(1) * 
    FROM [CIS].[dbo].[patches]
    WHERE patches.customer = a.number
    AND patches.ApplicationName = custapps.app
    ORDER BY patches.Created DESC) patches

    An index on:

    [CIS].[dbo].[patches](customer, ApplicationName, Created DESC) INCLUDE (all other referenced columns)

    will help performance.

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply