| Author |
 Topic Search  Topic Options
|
theSCIENTIST 
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Problem with SELECT COUNT DISTINCT...
Posted: 22 August 2005 at 2:15pm |
|
Right, I've been turning my head with this one, and can't figure how to do it.
Scenario:
I'm SELECTing DISTINCT from a table, it returns records fine, but I need to COUNT how many it returns, I can count in the recorset loop doing a c = c + 1 at the end I have my count, however, in this loop I have an array that needs to be populated at the same time, this array needs to be dynamically sized before being filled, so I can't just pre array(20), as it may return more that that, giving it a high value it's not good programming, so I need to know the COUNT from the DISTINCT records returned before I go into the loop, so I can [ Redim array(count) ] and get it sized perfectly.
Question:
Does anyone know how to do a COUNT in the same statement as a normal SELECT DISTINCT ... ?
I'm using ASP and MySQL, not sure if MSSQL can use COUNT, but I'm also willing to hear from all of you that may have an idea on how to do this with MSSQL, as I can get ideas from it.
Thanks.
Edited by theSCIENTIST - 23 August 2005 at 3:49am
|
|
|
 |
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 August 2005 at 3:53am |
|
Ok, I found the solution, it turns out one can use [ SELECT COUNT(DISTINCT fld_Whatever) AS count FROM tbl_Whatever; ], funny thing thou, I tried this before in many different combinations and it was always erroring out.
I then asked here because I thought and was really convinced, one couln't use the COUNT with the DISTINCT, anyway my fault.
|
|
|
|
Phat
Senior Member
Joined: 23 February 2003
Status: Offline
Points: 386
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 August 2005 at 4:27am |
|
Can't you use Redim Preserve?
Not sure how good it is on memory though.
|
|
|
|
theSCIENTIST
Senior Member
Joined: 31 July 2003
Location: United Kingdom
Status: Offline
Points: 440
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 August 2005 at 6:28am |
|
I don't want to preserve anything, I just want to set my array from the start with the correct size of what it will hold.
Redim Preserve is used if you want to resize the array but retain any data already in it, which is not the case here.
Yeah, Redim Preserve has a somewhat slow performance, due to the nature of the operation, but nothing to worry about on a fast or moderaly used server, unless the server is very slow/old or the data to be preserved is extenssive and the array multi-dimensional, but overall can be used without any noticable impact.
|
|
|
|
