SQL query to retrieve the single line of data with desired values for the columns

duplicates, group-by, sql, sql-server, sql-server-2008

Solution

On Oracle, i tried the following and it should work if you convert the oracle specific analytic functions, also i made some changes in the sample data for a better example :

    WITH t AS (
    SELECT '1111-00' AS acc_id, 'PSG SMB' AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-00' AS acc_id, 'SMB'     AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-00' AS acc_id, 'EB Seg'  AS cust_seg,  1 AS pbu FROM dual UNION ALL
    SELECT '1111-01' AS acc_id, 'PSG SMB' AS cust_seg,  1 AS pbu FROM dual UNION ALL
    SELECT '1111-01' AS acc_id, 'SMB'     AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-01' AS acc_id, 'Ex data' AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-02' AS acc_id, 'PSG Seg' AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-02' AS acc_id, 'Unatten' AS cust_seg, -1 AS pbu FROM dual UNION ALL
    SELECT '1111-02' AS acc_id, 'Channels'AS cust_seg, -1 AS pbu FROM dual )
    --
    SELECT acc_id,
           cust_seg
      FROM (SELECT t.*,
                   row_number() OVER(PARTITION BY acc_id ORDER BY CASE WHEN cust_seg LIKE '%EB%' THEN 1 WHEN pbu = 1 THEN 2 ELSE 3 END ) rnk
              FROM t
             ORDER BY acc_id, CASE WHEN cust_seg LIKE '%EB%' THEN 1 WHEN pbu = 1 THEN 2 ELSE 3 END)
     WHERE rnk = 1 ;

Result :

    ACC_ID                CUST_SEG
    --------------------- ------------------------
    1111-00               EB Seg
    1111-01               PSG SMB
    1111-02               PSG Seg

SQL Server version

    SELECT  *
    FROM    (
              SELECT  *
                      , rn = ROW_NUMBER() OVER (PARTITION BY S_Acc_RowID ORDER BY CASE WHEN LEFT(a.BU_Customer_Segment, 2) = 'EB' THEN 1 WHEN a.PBU = 1 THEN 2 ELSE 3 END)          
              FROM    [SiebelAccount Extract] a
            ) q
    WHERE   rn = 1

and testdata

;WITH [SiebelAccount Extract] (S_Acc_RowID, BU_Customer_Segment, PBU) AS (
  SELECT * FROM (VALUES 
     ('1111-00', 'PSG SMB',  -1)
     , ('1111-00', 'SMB',      -1)
     , ('1111-00', 'EB Seg',    1)
     , ('1111-01', 'PSG SMB',   1)
     , ('1111-01', 'SMB',      -1)
     , ('1111-01', 'EB data',  -1)
     , ('1111-02', 'PSG Seg',  -1)
     , ('1111-02', 'Unattended', -1)
     , ('1111-02', 'Channels', -1)
  ) a (b, c, d)
)
SELECT  *
FROM    (
          SELECT  *
                  , rn = ROW_NUMBER() OVER (PARTITION BY S_Acc_RowID ORDER BY CASE WHEN LEFT(a.BU_Customer_Segment, 2) = 'EB' THEN 1 WHEN a.PBU = 1 THEN 2 ELSE 3 END)          
          FROM    [SiebelAccount Extract] a
        ) q
WHERE   rn = 1

Problem

I have a database table with the following kind of data data ``` S_Acc_RowID BU_Customer_Segment PBU 1111-00 PSG SMB -1 1111-00 SMB -1 1111-00 EB Seg 1 1111-01 PSG SMB 1 1111-01 SMB -1 1111-01 EB data -1 1111-02 PSG Seg -1 1111-02 Unattended -1 1111-02 Channels -1 ``` ---------------- like 7 million rows now I want to extract single row for each Acc ID where the conditions are ``` 1) if the **Acc ID** is having 'EB --' in **CustSeg** then select that **CustSeg** value 2) if **Acc Id** is not having any 'EB -- ' in CustSeg then select **CustSeg** where **PBU** = 1 3) if the both above failed take any one value of the **CustSeg** ``` and the end data I want should be like ``` S_Acc_RowID BU_Customer_Segment 1111-00 EB seg 1111-01 EB Data 1111-02 (any one of three[PSG seg/ UNattended/channels]) ``` I'm using the following query ``` select distinct(A.[S_Acc_RowID]) as [Account_RowID], [EB Customer Segment] = case when LEFT(A.[BU_Customer_Segment],2) = 'EB' then A.[BU_Customer_Segment] when LEFT(A.[BU_Customer_Segment],2) != 'EB' then (select B.[BU_Customer_Segment] from dbo.[SiebelAccount Extract] B where A.[S_Acc_RowID]=B.[S_Acc_RowID] and [PBU] = 1) else A.[BU_Customer_Segment] end, A.[S_Acc_AMID2#] as [AMID Level 2(Acc)], A.[S_Acc_Login_P] as [Sales Team(Acc)], A.[S_Acc_Org_P] as [Country_det], A.[Customer AMID Level 2 Name(ACC)] from dbo.[SiebelAccount Extract] A ``` But it is returning the the data like this ``` S_Acc_RowID BU_Customer_Segment 1111-00 EB seg 1111-01 PSG SMB 1111-01 EB Data 1111-02 null ``` I don't want to display two rows for the ID 1111-01 ..I want only one row with EB please help me with this .. Thanks in advance.. Cheers, Harish

Original source