Sorting/Record Count/duplicate elimination/Unique problem- Not an FAQ 2

[Deadline]

Hello,

I have a page where I fetch and display the records for a complicated query. Let us consider, that it fetches colA, colB, and colC.

I am displaying it Table Format, with the provision to sort the records by colA, colB or colC. I mean, the user will click the heading of each column to sort; and when they do that, I simply pass the columnName as the sort key to the same page through QueryString.


In the page, I have a duplicate removal technique, where I am comparing colA of two adjacent rows like this :-

     WHILE NOT objRec.EOF
	IF  temp2 <> objRec.Fields(&quot;colA&quot;)  THEN       
           ....    
              /**Displaying the 
              recordset in table format**/
        END IF
	temp2 = objRec.Fields(&quot;colA&quot;)	 
	objRec.MoveNext	  
     WEND

At the end of table I am displaying the recordcount.

Here is where I face the problem ; for each time the user clicks on the heading of the column, the sort order varies and therefore the above loop returns different number of records for every sort made, because, the above loop will be logically correct only if the sorting is based on colA. For others, the loop, though works, is logically incorrect; I mean, as I said, it returns different number of rows each time.

Kindly help me to avoid this ugly situation and help me in displaying same number of records, whatever the sort key is.


Thank you...
RR

 

[wushutwist]

Please show your SQL String. The only thing that should be changing is the sort order (ORDER BY clause) not the names of the columns in the SQL String (which is kind of what I expect to see). Wushutwist

 

[Deadline]

Wushutwist,

THank you..

You are right. Here is how I do it *partial*

SELECT CASE UCase(Request.QueryString("ORDER"))
	CASE "0"
		strSQL = strSQL & " ORDER BY colA,colB " 
	CASE "1"
		strSQL = strSQL & " ORDER BY colB,colA " 
	CASE "2"
		strSQL = strSQL & " ORDER BY colC,colA "
	CASE ELSE
		strSQL = StrSQL & " ORDER BY colA "
END SELECT

And here is my partial table code :

Response.Write &quot;<th  align=center><A HREF=&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)& &quot;?ORDER=0>COLUMN A</A></tH>&quot;
Response.Write &quot;<th  align=center><A HREF=&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)& &quot;?ORDER=1>COLUMN B</A></tH>&quot;
Response.Write &quot;<th  align=center><A HREF=&quot; & Request.ServerVariables(&quot;SCRIPT_NAME&quot;)& &quot;?ORDER=2>COLUMN C</A></tH>&quot;

Thank you...
RR

 

[Deadline]

Hello...
Can anyone help me in this ? Thank you...
RR

 

[shaddow]

U could use to eliminate the repeated values
SELECT DISTINCT...
and please if u need help tell me more.... ________

George

 

[Deadline]

Shaddow,
Thank you very much. I already am using DISTINCT. But consider a set of records like this:

colA    colB    colC
~~~~~~~~~~~~~~~~~~~~~
   1     XX      100
   2     XX      200
   2     YY      100

Here even using DISTINCT will get 3 records, agree ?

Now let us say I

ORDER BY colA

. Now when I use the loop as in first posting, I will fetch all the rows and display the rows, ONLY when two adjacent rows' colA value are not equal, I mean, in our example rows, I will be displaying the first two rows only. The third one will not be displayed. BUT the recordcount will always show 3 records.

I hope, I had expressed my problem correctly.. Can you help me out of this ?



Thank you...
RR

 

[shaddow]

This records loks diffrent for me...
What you mean by diffrent... ________

George

 

[shaddow]

I've found a sollution to your problem...
I see that your algorithm works just for colA
If u want to gneralize your algorithm use
the Fields property like this
objRec.Fields("fieldname&quot u just put objRec.Fields(filedindexposition)

if u have
select cola,colb,colc...

fileds(0) ->cola
fileds(1) ->colb
fileds(2) ->colc

And u could use your Request.QueryString("ORDER&quot variable to see what is the record u sorting for...
and u could use your code with few moddifications...

recposition=int(Request.QueryString("ORDER&quot)
WHILE NOT objRec.EOF
IF temp2 <> objRec.Fields(recposition) THEN
....
/**Displaying the
recordset in table format**/
END IF
temp2 = objRec.Fields(recposition)
objRec.MoveNext
WEND

hope this helps you...
Iu have more questions please ask... ________

George