You must Sign In to post a response.
  • Category: SQL Server

    Inner join sql qry for same table with condition


    tid customername category engineer
    1 aaaaaa complaint king
    2 bbbbb complaint queen
    3 aaaaaa recomplaint queen

    i need report below like

    condition = recomplaint

    tid customername enginner previousengineer
    3 aaaaaa queen king

    qry will taken recomplaint and previous engineer only. not take before previous engineer

    am using qry like this

    Select CCN.Refno,CCN.CustomerID,CCN.CompPerName,CCN.TakenEngineer,CC.TakenEngineer From CustomerComplaint as CCN inner join CustomerComplaintNew as CC On CCN.CustomerID =CC.CustomerID and CCN.WarrantyType='RECOMPLAINT' AND CC.RefNo < CCN.RefNo

    But it will take previous all engineer
  • #769785
    You can use sub query to get the desired record. Use that sub query as condition and select the top record in an order.

    Exp: SELECT TOP 1 FROM tablaA WHERE id < (SELECT id from tableA where CONDITION) ORDER BY id DESC / ASC.

  • Sign In to post your comments