-
Recordset filter Q's
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....
-
Re: Recordset filter Q's
Why don't you use "Select Distinct ..." to get unique records to begin with?
-
Re: Recordset filter Q's
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
-
Re: Recordset filter Q's
Wouldn't using a GROUP BY clause be better than a SELECT DISTINCT?
-
Re: Recordset filter Q's
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.
-
Re: Recordset filter Q's
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
-
Re: Recordset filter Q's
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.
-
Re: Recordset filter Q's
Try it....
Code:
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:
Code:
b c a
a c b
a a c
a b c
And it is important to the point.
When changed to the DISTINCT
Code:
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