Unique index on more than one column
lazarus / 16-Databases

----------------------------------------------------------------------
kuloun · 2011-03-13 08:41

I am trying to filter data in DBF file using TDBF component and
indexes. I need following result:

Code: [Select]TABLE:
COL1        | COL2     |
something   | type 1   |
nothing     | type 1   |
something   | type 2   |
nothing     | type 2   |
anything    | type 2   |
anything    | type 2   |
something   | type 1   |
something   | type 1   |
nothing     | type 1   |

RESULT I NEED:
something   | type 1   |
something   | type 2   |
nothing     | type 1   |
nothing     | type 2   |
anything    | type 2   |

I tried to use unique indexes but i never get the right result.

I would imagine something like this:
TDBF.AddIndex('test','COL1,COL2',[ixUnique]);

but obviously it doesn't work.

I think I will have to connect to the DBF file twice and use cycles to
go through the table and select right data, but I would rather use
some faster way, if it exists, because the table is large (over 60 000
records, 52MB)

Thank you for any ideas.

----------------------------------------------------------------------
Old Dog · 2011-03-14 03:23

TDBF.AddIndex('test','COL1 + COL2',[ixDecending]);

should get you closer, but will probably put 'type 2' before 'type 1'
(following your order of 'something, nothing, anything')

----------------------------------------------------------------------
kuloun · 2011-03-14 08:30

Wow, that looks easy. 
I knew there must be some really easy way to do it.
I don't really need to order it. I just need to filter it.

I will give it a try this afternoon.
I was stuck on it for hours. Thank you.

----------------------------------------------------------------------
kuloun · 2011-03-15 20:09

It works!

Code: [Select]TDBF.AddIndex('test','substr(COL1 +
COL2,1,99)',[ixUnique]);
I could not make it work, because
Code: [Select]substr(COL1 + COL2,0,99)returns empty result.

Problem solved, thank you.