Select only Top 5 Records RRS feed

  • Question

  • Hey all,

    I am really new to SQL and am trying to only pull 5 more recent service calls from a table.  

    Here is my query that works great, but I am getting more calls than I want.

    SELECT dbo.SCCalls.CallNumber as 'EA Call Nbr',

    dbo.SCCalls.EquipSerialNumber as 'Serial Nbr', 

    dbo.all_paths_serv_parts.PRODUCT_NUMBER as 'Part Prod Code', 

    dbo.all_paths_serv_call.CALL_ID as 'OMD Call Nbr', 

    dbo.ShAgents.PrefFullName as 'Tech Name', as 'EA Dispatch Date', 

    omd.dbo.all_paths_invt_descript.DESC1_IDE as 'OMD Part Description', 

    dbo.all_paths_serv_call.SYMPTOM_CODE as 'OMD Symptom Code', 

    dbo.all_paths_serv_call.CALL_TYPE as 'OMD Call Type', 

    dbo.all_paths_serv_call.CALL_DATE_SCA as 'OMD Call Date', 

    dbo.all_paths_serv_call.REPRESENTATIVE as 'OMD Service Tech'

    FROM (((((dbo.SCCalls LEFT OUTER JOIN dbo.ShAgents ON dbo.SCCalls.TechnicianID = dbo.ShAgents.AgentID) 

    LEFT OUTER JOIN dbo.SCDispatches ON dbo.SCCalls.CallID = dbo.SCDispatches.CallID) 

    LEFT OUTER JOIN dbo.all_paths_serv_call oN dbo.SCCalls.EquipSerialNumber = dbo.all_paths_serv_call.SERIAL)

    left OUTER JOIN dbo.all_paths_serv_parts ON dbo.all_paths_serv_call.CALL_ID = dbo.all_paths_serv_parts.CALL_ID)

    left outer join omd.dbo.all_paths_invt_descript on   dbo.all_paths_serv_parts.PRODUCT_NUMBER =  omd.dbo.all_paths_invt_descript.PRODUCT_NUMBER)

    WHERE  dbo.SCCalls.Status  =  'D  ' order by dbo.sccalls.CallNumber

    This query selects all the calls in dispatch table.  For each Call, it looks in the all_paths_serv_call table and selects all the calls in this table that has the same serial number as the call from dispatch.  It then links to the parts table and pulls all the parts used on the call.  This is all working fine, except when it pulls the calls from serve_call, I only want it to get the most recent 5 calls in the table based on all_paths_serv_call.CALL_DATE_SCA.

    Any help would be appreciated.  I saw something about using "with rownumbersadded" but wasnt sure where or how to insert it.

    btw, I am using SQL Server 2012



    Sunday, August 25, 2013 8:28 PM


  • Hello Chuck,

    Add a TOP 5 clause and modify you ORDER BY clause:

    SELECT TOP 5 dbo.SCCalls.CallNumber as 'EA Call Nbr',
    WHERE   dbo.SCCalls.Status  =   'D  '  
    order by all_paths_serv_call.CALL_DATE_SCA DESC
           , dbo.sccalls.CallNumber

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by RohitGarg Monday, August 26, 2013 2:21 PM
    • Marked as answer by Mike YinModerator Sunday, September 1, 2013 12:04 PM
    Monday, August 26, 2013 4:59 AM