Help with SQL Select Distinct query

Databases
1

I can’t quite get my head round a SqLite Select syntax that I need…

Say I have a table (TB1) with 5 fields (ID, FLD1, FLD3, FLD4)

FLD1 contains duplicate values that I want to filter out. ie I want to return only the first UNIQUE ocurrence of data in FLD1 - but, here’s the problem, I want to return ALL columns of the selected rows.

If I say:-
“SELECT DISTINCT FLD1 FROM TB1”
I get exactly the rows that I want - but I only get the one column (FLD1). I want ALL columns for those rows.

Could somebody please explain how?

2

SELECT MIN(FLD1), FLD2, FLD3, FLD4 FROM TB1 GROUP BY FLD1

3

I presume that MIN does the same as DISTINCT - in other words it means it should return the minimum different results from that column - (distinct values only?)

Thanks.

4

I think you want distinct not MIN,

Maybe this will help (scroll down a bit)…

link text

And: SQLite.org search for Distinct.

5

The technique with MIN combined with GROUP BY may well produce the correct result - I will give it a try…

However, I have discovered the following code works fine and return exactly the RecordSet that I want:-
“SELECT * FROM (SELECT * FROM TB1 ORDER BY ID DESC) GROUP BY FLD1 ORDER BY ID”

But if used in a SQLitePreparedStatement it generates a syntax error during compiling.

6

assuming that ID is an incremental value, MIN should be on ID.

you can use it like:

select a.*
from TB1 a
join (select MIN(ID) as ID, FLD1 from TB1 group by FLD1) b on b.ID = a.ID
order by a.ID asc;