DISTINCT problem with ORDER BY

rbuser

In the dataview designer, I have a table T with 2 fields F1, F2. I want
to see only F1. I put F1 in the Fields tab and F2 in the Order tab and
in the Table tab I check DISTINCT. In the SQL tab, it generate a good
SQL expression.

SELECT DISTINCT T.F1
FROM T T
ORDER BY T.F2

But, when I preview the table, it generate a wrong SQL expression.

SELECT DISTINCT T.F1, T.F2
FROM T T
ORDER BY T.F2

Where is my problem ?

Thank you

digitalmetaphors

The SQL generation is working as designed. The field in the ORDER BY usually
has to also be in the SELECT clause. Since it isn't supported on all
databases, we had to add to the select clause behind the scenes to avoid
database engine errors. The actual SQL that is going to be sent to the
server is what you see when you hit Ctrl-click over a dataview.

Cheers,

Jim Bennett
Digital Metaphors

rbuser

I found a solution, but I don't know if it is good and if I will have a
side effect.

In the daMagicSQL unit in the TdaMagicSQL.GetOrderByFields method, I put
this 2 lines in comment.

// if (lField = nil) then
// lField := AddMagicSelectField(lTable, lOrderByField);

digitalmetaphors

Changing our source would do the trick too. We usually don't suggest this as
it may cause other problems and we can't support the problems they cause.
However, if it works that is cool so it doesn't add the select field behind
the scenes. Just make sure this SQL is supported on other databases if you
try this on other projects using a different database:)


Cheers,

Jim Bennett
Digital Metaphors