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.
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_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
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
)
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With