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

  • roy.tollison

    SSCommitted

    Points: 1743

    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]

  • ScottPletcher

    SSC Guru

    Points: 98441

     

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • roy.tollison

    SSCommitted

    Points: 1743

    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.
  • ScottPletcher

    SSC Guru

    Points: 98441

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • roy.tollison

    SSCommitted

    Points: 1743

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

    Thanks...

  • ScottPletcher

    SSC Guru

    Points: 98441

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

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • roy.tollison

    SSCommitted

    Points: 1743

    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.

  • Jonathan AC Roberts

    SSCoach

    Points: 17298

    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 8 (of 8 total)

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