How to get only one record for each duplicate rows of the id in oracle?
There are no standard aggregate functions in Oracle that would work with BLOB
s, 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_idFROM( 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) innWHERE inn.num = 1;
The above should return the first (based on image_id
) row for each group.
SELECT group_id, image, image_idFROM a_tableWHERE (group_id, image_id) IN ( SELECT group_id, MIN(image_id) FROM a_table GROUP BY group_id );
select * from (select t1.*, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY group_id desc) as seqnum from tablename t1) where seqnum=1;