Problem with DISTINCT

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Raymond - 30 Jul 2003 15:54 GMT

Hello.

I have problem with the DISTINCT in SQL statement.

The coding like this.

sS ="SELECT DISTINCT t_ID FROM T"
rsS.Open sS, cn, adOpenKeyset, adLockOptimistic

The rsS.RecordCount should be number > 0
(For example :18)
However, I always get rsS.RecordCount = -1

I already do a testing when the
sS ="SELECT t_ID FROM T"
and the rsS.RecordCount = 30

Can anyone help me? Thanks.
Raymond

Reply to this Message

Kent Prokopy - 30 Jul 2003 16:10 GMT

This is a cursor issue not an issue with the key word distinct.

Try setting your cursor to Client side....

> Hello.
>
[quoted text clipped - 15 lines]
> Can anyone help me? Thanks.
> Raymond

Reply to this Message

Raymond - 30 Jul 2003 16:45 GMT

Thanks for Kent reply.

But, ...

Because I just want to count how many of
distinct records on field [t_ID] in table [T].

Can I just pass the SQL statement into the
recordset instead of creating cursor?

Thank you very much for any more help ...
Raymond

>-----Original Message-----
>This is a cursor issue not an issue with the key word distinct.
[quoted text clipped - 22 lines]
>
>.

Reply to this Message

Graham Dobson - 31 Jul 2003 16:15 GMT

> Because I just want to count how many of
> distinct records on field [t_ID] in table [T].
>
> Can I just pass the SQL statement into the
> recordset instead of creating cursor?

How about

SELECT [t_id], Count[t_id]
FROM Table
GROUP BY [t_id]

-- Graham

You might compare the performance of this versus

SELECT DISTINCT [t_id], Count[t_id]
FROM Table

Calling recordset.RecordCount or doing DISTINCT or DISTINCTROW queries are
hardly ever efficient things to do. -- Graham

Reply to this Message

Graham Dobson - 31 Jul 2003 17:04 GMT

On second thought what you might need is:

SELECT COUNT(DISTINCT [t_id]) FROM TABLE

-- Graham

Reply to this Message

VB Forum / Database Access / July 2003

Problem with DISTINCT