Hello
I am new with Kexi and tried to do a selection of records avoiding doubles.

In MS Access I was used to such an SQL-command:

Code: Select all

SELECT DISTINCTROW Filenames.MD5, Filenames.FilenameID, Filenames.CompiledID, Filenames.Dateiname, Filenames.Extension, Filenames.DatumImName, Filenames.Pfad, Filenames.geaendertam, Filenames.Dateigroesse, Filenames.Markiert, Filenames.NoIssue, Filenames.Editor, Filenames.Edited
FROM Filenames
WHERE (((Filenames.MD5) In (SELECT [MD5] FROM [Filenames] As Tmp GROUP BY [MD5] HAVING Count(*)>1 )))
ORDER BY Filenames.MD5;

But this does not work with Kexi, as it stops at the second SELECT telling me, that is a reserved word.

Currently I am working on this command, but it fails too:

Code: Select all

SELECT ti.id, ti.path, ti.filename, ti.bytes, ti.date_numerical, ti.hash
FROM list_of_all_files t1
WHERE
    NOT EXISTS (SELECT *
    FROM list_of_all_files t2
    WHERE t1.hash = t2.hash AND t1.id > t2.id)

Can you give me a hint?

I also tried this command, but that fails too:

Code: Select all

SELECT hash, First(id) as Id1, First(path) as path1, First(filename) as filename1, First(date_numerical) as date1 FROM list_of_all_files GROUP [list_of_all_files].hash ORDER BY [list_of_all_files].hash

It tells me, the first dot in the command is a wrong char.