Recordset filter Q's

[juliemac]

I create a clone of the main RS and want to filter it. I built the code to step through an array.

<code>
byteslice=trim(cstr(splitdata(i)))
FilterX= "distinct " & byteslice
objrst_filter.filter= FilterX
</code>

I need to filter for distinct entries. Can this be done? The system error says its the wrong arguement type etc....

[RhinoBull]

Why don't you use "Select Distinct ..." to get unique records to begin with?

[techgnome]

Filter doesn't work that way. Filter works in the same way as the WHERE clause of a SQL statement.....

RB's got the better idea there.

-tg

[Hack]

Wouldn't using a GROUP BY clause be better than a SELECT DISTINCT?

[RhinoBull]

No, Group By will only group all records based on the list or fields included in the Group By clause.
Select Distinct takes care of uniqueness.

If you have something like this in the table

a,b,c
a,a,c
a,c,b
b,c,a
a,b,c
a,c,b

select all fields using Group By will return the following:

Select Field1,Field2,Field3 From Table1
Group By Field3,Field1,Field2

b,c,a
a,c,b
a,c,b
a,a,c
a,b,c
a,b,c

Select Distinct * From Table1 ... will result in this:

a,a,c
a,b,c
a,c,b
b,c,a

So, the difference could be significant.

[techgnome]

Point of order....
you would have the following with Group By
b,c,a
a,c,b
a,a,c
a,b,c

not
b,c,a
a,c,b
a,c,b
a,a,c
a,b,c
a,b,c

The "a,c,b" and "a,b,c" would get "Grouped" together.
And if you notice... the result is the same. There is also a performance difference. Group By will happen faster than a Distinct.

-tg

[RhinoBull]

Nope, records will be groupped based on how fields are listed in the Group By clause so what I posted was correct but not really important in the point I was making.

[techgnome]

Try it....

DECLARE @tmpTable TABLE (
	Field1 varchar(1),
	Field2 varchar(1),
	Field3 varchar(1))
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('a','b','c')
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('a','a','c')
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('a','c','b')
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('b','c','a')
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('a','b','c')
INSERT INTO @tmpTable
	(Field1, Field2, Field3)
VALUES
	('a','c','b')
Select Field1,Field2,Field3 From @tmpTable
Group By Field3,Field1,Field2

Select DISTINCT Field1,Field2,Field3 From @tmpTable

The select I pulled right from the posting...unchanged (except for the table name)

These are the results I got:

b	c	a
a	c	b
a	a	c
a	b	c

And it is important to the point.

When changed to the DISTINCT

a	a	c
a	b	c
a	c	b
b	c	a

Same results. Granted, the ordering is different, but that too can be adjusted.

-tg