CODEWITHSUNDEEP
sqloracle-databaseselectoracle11g
Zoey Malkov
Zoey Malkov

Reputation: 832

How to group by with a condition

I have the following table and data below. For simplicity sake I have made shape a varchar. Grouping by shape column is NOT an option. Shape can only be selected or joined on id.

If I use the select statement below, it is exactly the result I want. it is just missing the id column:

SELECT badge_id, likes
FROM table1 t1
GROUP BY badge_id, likes;

My question is, if the badge_id and likes combination are the same as another row, I want to show the first instance of id. If the badge_id and likes combination aren't the same as another row, then show me all its unique id's.

CREATE TABLE table1 (
    
    id          NUMBER,

    badge_id    varchar(100) NOT NULL,

    likes       varchar(100) NOT NULL,

    shape        varchar(100) NOT NULL,

    PRIMARY KEY(id)
);

INSERT INTO table1 (id, badge_id, likes, shape) VALUES
(1, 'B001', 'red, blue', 'Rectangle');
INSERT INTO table1 (id, badge_id, likes, shape) VALUES
(2, 'B001', 'red, blue', 'Rectangle');
INSERT INTO table1 (id, badge_id, likes, shape) VALUES
(3, 'B003', 'green, red', 'Triangle');
INSERT INTO table1 (id, badge_id, likes, shape) VALUES
(4, 'B003', 'purple, red', 'Square');
INSERT INTO table1 (id, badge_id, likes, shape) VALUES
(5, 'B555', 'blue, black', 'Circle');

SQL Fiddle: http://sqlfiddle.com/#!4/83e12

Upvotes: 0

Views: 118

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you want to use an analytic function. Something like this fiddle

SELECT *
  FROM (
        SELECT t1.*, 
               row_number() over (partition by badge_id, likes
                                      order by id) rn
          FROM table1 t1
       ) subQ
 WHERE subQ.rn = 1

The partition by says that we want each distinct set of badge_id, likes to have its own set of row numbers. order by id says that the row numbers should follow the id order. So the lowest id for any combination of badge_id, likes will have a row number of 1 assigned. And that's what we filter out in the where clause.

Upvotes: 2

DRapp
DRapp

Reputation: 48139

I THINK all you want is DISTINCT

select distinct
      badge_id,
      likes, 
      shape 
   from 
      table1 
   order by
      badge_id,
      likes

by doing DISTINCT, only one record is returned for badge 8001 (where it has same valules for badge and likes, but also gives you the only shape it has along for the ride.

For your badge 8003, you would see both entries just by the nature of distinct (red,blue) vs (green, red).

Badge 8555 only has one entry so that would just come directly.

Now, a different scenario you did NOT cover. A condition with same badge and like, but the SHAPE was different. In the query above, it WOULD show each one individually such as

id    badge    likes      shape
 6    8765     blue, red  Triangle
 7    8765     blue, red  Square
 8    8765     blue, red  Circle
 9    8765     blue, red  Triangle
10    8765     blue, red  Circle

So what would/should this one show... By using distinct, all 3 combinations would show. since IDs 8 and 10 are the same, it would only show 1... same with IDs 6 and 9

Upvotes: 0

Related Questions