Performing SQL select distinct except BLOB field

Question

I need to select some DISTINCT fields on a table, but I need to exclude blobs from the distinct, since distinct does not consider BLOB fields.

I tried looking for something here, but this is what I found and it doesn't work for me,

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 3, column 76.
(.
----------------------------------
SQLCODE: -104
SQLSTATE: 42000
GDSCODE: 335544569

This is the query I used (I modified names since it's from work)

SELECT Field1, Field2, Field3, Blob1, Blob2
FROM (     
  SELECT Field1, Field2, Field3, Blob1, Blob2, row_number() over (partition by Field1, Field2, Field3 order by Field1) RW
  FROM Table
) WHERE RW = 1;

Is there any other way to do it other than the one suggested in that article?

Answer 1

The query you found in the other question is a syntax error in Firebird 2.5, but would likely work in Firebird 3.0 (though I didn't verify that), as that is the version that introduced window functions like row_number().

However, if you just want a blob (doesn't matter which one) for the combination of fields, then you can do:

select a.field1, a.field2, a.field3,
  (select first 1 blob1 from table1 where field1 = a.field1 and field2 = a.field2 and field3 = a.field3) as blob1,
  (select first 1 blob2 from table1 where field1 = a.field1 and field2 = a.field2 and field3 = a.field3) as blob2
from (
  select distinct field1, field2, field3
  from table1
) a

or

select field1, field2, field3, min(blob1) as blob1, min(blob2) as blob2
from table1
group by field1, field2, field3

This query might result in blob1 and blob2 from different rows.

If you want all the different blobs, then you could do something like the following (this isn't 100% fool-proof, as hash can produce collisions, and it will likely be extremely slow):

select b.field1, b.field2, b.field3, b.blob1, b.blob2
from (
  select distinct field1, field2, field3, hash(blob1) as hash1, hash(blob2) as hash2
  from table1
) a
inner join table1 b
  on b.field1 = a.field1 and b.field2 = a.field2 and b.field3 = a.field3
    and hash(b.blob1) = a.hash1 and hash(b.blob2) = a.hash2