CODEWITHSUNDEEP
sqloracle-databasegroup-by
decorooney
decorooney

Reputation: 23

Group by and Join on same table

I have a table which contains following records.

         acct_id | org_id | terr_id | segment 4       
         --------------------------------------
         123     | A      | 12345   | US
         123     | A      | 45678   | CA
         456     | B      | ABC     | US
         456     | B      | XYZ     | US

I need to group by on acct_id and org_id and get the record with max(segment 4).. While grouping by I need to omit terr_id in group by and I need it in the output for the associated max(segment 4). How can I do it without joining back to the same table again?

o/p needs to be

         acct_id | org_id | terr_id | segment 4       
         --------------------------------------
         123     | A      | 12345   | US
         456     | B      | ABC or XYZ  | US

Upvotes: 0

Views: 5261

Answers (2)

xQbert
xQbert

Reputation: 35323

WITH CTE AS (
   SELECT Distinct acct_ID
        , Org_ID
        , Terr_ID
        , Segment4
        , row_number() over (partition by ACCT_ID, Org_ID order by terr_ID asc) RN
   FROM Table
   WHERE Terr_ID <> '45678')
SELECT * 
FROM cte 
WHERE RN = 1

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

You could use an inner join with grouped by for max(segment_4)

  select acct_id , org_id , terr_id , segment_4
  from my_table m
  inner join (

      select acct_id, org_id, max(segment_4 ) as my_segment_4
      from my_table 
      group by acct_id, org_id 
  )  t o t.acct_id  = m.acct_id 
              and t.org_id = m.org_id 
                  and t.my_segment_4 = m.segment_4

Upvotes: 2

Related Questions