CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Psychocryo
Psychocryo

Reputation: 2153

How to get only one record for each duplicate rows of the id in oracle?

suppose i have this table:

group_id | image | image_id |
-----------------------------
23        blob       1
23        blob       2
23        blob       3
21        blob       4
21        blob       5
25        blob       6
25        blob       7

how to get results of only 1 of each group id? in this case,there may be multiple images for one group id, i just want one result of each group_id

i tried distinct but i will only get group_id. max for image also would not work.

Upvotes: 9

Views: 92037

Answers (3)

Durgesh Yadav
Durgesh Yadav

Reputation: 1

select * from 
  (select t1.*,
   ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY group_id desc) as seqnum
   from tablename t1) 
where seqnum=1;

Upvotes: 0

PM 77-1
PM 77-1

Reputation: 13334

There are no standard aggregate functions in Oracle that would work with BLOBs, so GROUP BY solutions won't work.

Try this one based on ROW_NUMBER() in a sub-query.

SELECT inn.group_id, inn.image, inn.image_id
FROM
(
    SELECT t.group_id, t.image, t.image_id, 
        ROW_NUMBER() OVER (PARTITION BY t.group_id ORDER BY t.image_id) num
    FROM theTable t
) inn
WHERE inn.num = 1;

The above should return the first (based on image_id) row for each group.

SQL Fiddle

Upvotes: 26

the_slk
the_slk

Reputation: 2182

SELECT  group_id, image, image_id
FROM    a_table
WHERE   (group_id, image_id) IN
        (
            SELECT  group_id, MIN(image_id)
            FROM    a_table
            GROUP   BY
                    group_id
        )
;

Upvotes: 1

Related Questions