How do you group joined records on output?

[tjock]

I want to output a standard joined table recordset and group rows on a unique id field (specified 1st in Order By). Just like a query for a forum thread with responses would return multple rows. (SELECT TOP or DISTINCT doesn't work).

I'm new to ASP, but in CF it's as simple as:
<cfoutput... group="id"> (after you Order By ID)
Ideas?

 

[Sheco]

Are you trying to change which rows get into your recordset OR are you trying to change the logic the ASP applies to each row?

 

[tjock]

I'm trying to change the logic the ASP applies to each row. For example, the ASP output now is:

while not EOF...
3 rows joined on ID 1
4 rows joined on ID 2
3 rows joined on ID 3
5 rows joined on ID 4
.. and son on..

I just want to display the first row for each unique ID.

 

[DotNetGnat]

something like this:

Do until rs.EOF
Dim tempid
tempid=rs("id")
'display the id
'display related records for that id
rs.MoveNext
If tempid<>rs("id") Then exit Do  
loop

the above is just a pseudo code...hope it gives you some idea...if you still get any errors post your code...

-DNG

 

[Tarwn]

I have to admit I am sitlloon my first cup of coffee and a little confused. When I read your previous post it seemed like you only wanted one record for each unique id (not all the records grouped, ordered, or anything else).

If that is the case then you should be able to handle this ins your SQL statement. Something like:

SELECT MT1.IdField, MT1.OtherField 
FROM MyTable MT1
WHERE MT1.OtherField IN (SELECT TOP 1 MT2.OtherField FROM MyTable MT2 WHERE MT2.IdField = MT1.IdField ORDER BY MT2.OtherField DESC)

If I am awake enough then what that should do is select records from the table where a secondary field is in that subselect. Since we are doing a TOP 1 we ought to only have one record in the sub-select. The only issue would be times when you have two records with the same unique ID and same secondary value. I know there is a way around this but I don't have enough coffee in my system yet.

-T