Unique Records in asp

[Miguel Orrego]

Hi,

How can I count only unique records in a recordset? (sql server 7.0).

When I do a select query for an asp page, I may have more than 1 record with
the same (for example) STATEMENTID. When I count the records Iwant to
register only 1 record increment for each unique STATEMENTID.

How can I do this?

Thanks in advance.

Miguel.

[Aaron Bertrand - MVP]

If you're already looping through every row, then you can keep a counter
yourself.


stCount = 0
stid = -500000
do while not rs.eof
if rs("statementid") <> stid then
stid = rs("statementid")
stCount = stCount + 1
end if
...
rs.movenext
loop


Or, you can run a different query for the count:

SELECT COUNT(*) FROM (SELECT DISTINCT statementid FROM table GROUP BY
statementid)

In SQL Server 2000, I believe you can do SELECT COUNT(DISTINCT statementid)
FROM table






"Miguel Orrego" <miguel@stressedmonkey.net-nospam> wrote in message
news:3f005bde$0$18490$cc9e4d1f@news.dial.pipex.com ...

> Hi,
>
> How can I count only unique records in a recordset? (sql server 7.0).
>
> When I do a select query for an asp page, I may have more than 1 record

with

> the same (for example) STATEMENTID. When I count the records Iwant to
> register only 1 record increment for each unique STATEMENTID.
>
> How can I do this?
>
> Thanks in advance.
>
> Miguel.
>
>

[John Blessing]

"Miguel Orrego" <miguel@stressedmonkey.net-nospam> wrote in message
news:3f005bde$0$18490$cc9e4d1f@news.dial.pipex.com ...

> Hi,
>
> How can I count only unique records in a recordset? (sql server 7.0).
>
> When I do a select query for an asp page, I may have more than 1 record

with

> the same (for example) STATEMENTID. When I count the records Iwant to
> register only 1 record increment for each unique STATEMENTID.
>
> How can I do this?
>
> Thanks in advance.
>
> Miguel.
>
>

SELECT DISTINCT STATEMENTID from ...

This will return only record for each unique statementid value. It will
only work for records which are identical.

If you use

select distinct statementid, anothercolumn from ...

and there are two records with the same value of statementid,but a
different value in anothercolumn then both will be returned.

--
John Blessing
[url]http://www.LbeHelpdesk.com[/url] - Help Desk software at affordable prices
[url]http://www.free-helpdesk.com[/url] - Completely free help desk software !
[url]http://www.lbetoolbox.com[/url] - Remove Duplicates from MS Outlook
[url]http://www.lbesync.com[/url] - Synchronize two Outlook Personal Folders

[Mark Schupp]

select count( distinct STATEMENTID ) from ...

--
Mark Schupp
--
Head of Development
Integrity eLearning
Online Learning Solutions Provider
[email]mschupp@ielearning.com[/email]
[url]http://www.ielearning.com[/url]
714.637.9480 x17


"Miguel Orrego" <miguel@stressedmonkey.net-nospam> wrote in message
news:3f005bde$0$18490$cc9e4d1f@news.dial.pipex.com ...

> Hi,
>
> How can I count only unique records in a recordset? (sql server 7.0).
>
> When I do a select query for an asp page, I may have more than 1 record

with

> the same (for example) STATEMENTID. When I count the records Iwant to
> register only 1 record increment for each unique STATEMENTID.
>
> How can I do this?
>
> Thanks in advance.
>
> Miguel.
>
>