DISTINCT RECORDs from ADO RecordSet

[kprit]

Is it possible to extract DISTICT record from ADO Recordset?

e.g. I have a recordset having fields cust_name,year,value1,value2....etc. I want distinct cust_name,year from this recordset. I don't want to extract this from database.

Pls reply....ASAP

Thanks

 

[JeffTullin]

>>I want distinct cust_name,year from this recordset. I don't want to extract this from database.
<<
This is a bit unclear.
What do you mean by 'dont want to extract this...'?

Do you want a recordset that contains only the distinct values?
If so, open the recordset using a SQL query that includes the word 'distinct', instead of opening the recordset using the table name.

egand I apologise for this not being the correct syntax, I am not near a source of ADO reference material ...)

use
Set myRecSet = openrecordset(&quot;Select distinct cust_name,year,value1,value2 from MYTABLE;&quot

instead of
Set myRecSet = openrecordset(&quot;MYTABLE&quot

 

[kprit]

Hi JeffTullin

No no...i don't want this.....

Actually I already have a recordset. I want distinct records from Recordset. Means some way to filter distinct records from recordset and put them into an array.

I know that I can get distinct records using &quot;SELECT DISTINCT....&quot;.

Thanks

 

[SemperFiDownUnda]

While ADO has basic features like SORT and FILTER. This is not something provided by ADO. This really is a DBMS function. One that not all DBMS provide I might add. I'm guessing your recordset is a disconnected recordset. If it is not then reissue the query with the DISTINCT keyword in the SELECT clause.

You could sort by all columns then manually ignore any record that has the same values for all columns as the previous record.

 

[JeffTullin]

This may be a bit extreme, but:

You might consider putting the results into a collection rather than an array.
If you create a class to hold the values, when you add the object to the collection, you can assemble the key from the values in the table.
If the key exists, you will get a trappable error when you try to add the object, and you can then do a resume next.
The end result will be a collection with only distinct values.