Prevent Duplicates in ASP Array

I want to use GetRows() in this query but I want to ensure that no meet included multiple times.  The goal is to speed the query up.  This "works" but is way slow and inefficient.  Thanks in advance!

    Set rs = Server.CreateObject("ADODB.Recordset")
    sql = "SELECT m.MeetsID, m.MeetName, m.MeetDate, m.MeetSite FROM Meets m INNER JOIN MeetTeams mt ON m.MeetsID = mt.MeetsID INNER JOIN Roster r ON "
    sql = sql & "mt.TeamsID = r.TeamsID INNER JOIN IndRslts ir ON ir.MeetsID = mt.MeetsID WHERE ir.RosterID IN (" & sSelectQS & ") AND ir.Place > 0 "
    sql = sql & "AND (m.MeetDate >= '" & dBegDate & "' AND m.MeetDate <= '" & dEndDate & "') AND Sport = '" & sSport & "' ORDER BY m.MeetSite DESC"
    rs.Open sql, conn2, 1, 2
    Do While Not rs.EOF
        If x = 0 Then
            AllMeets(0, x) = rs(0).Value
            AllMeets(1, x) = Replace(rs(1).Value, "''", "'") & " (" & Month(rs(2).Value) & "/" & Year(rs(2).Value) & ")"
            AllMeets(2, x) = rs(2).Value
            AllMeets(3, x) = Replace(rs(3).Value, "''", "'")
            x = x + 1
            ReDim Preserve AllMeets(3, x)
        Else
            For z = 0 To UBound(AllMeets, 2) - 1
                If CLng(rs(0).Value) = CLng(AllMeets(0, z)) Then
                    Exit For
                Else
                    If z = UBound(AllMeets, 2) - 1 Then
                        AllMeets(0, x) = rs(0).Value
                        AllMeets(1, x) = Replace(rs(1).Value, "''", "'") & " (" & Month(rs(2).Value) & "/" & Year(rs(2).Value) & ")"
                        AllMeets(2, x) = rs(2).Value
                        AllMeets(3, x) = Replace(rs(3).Value, "''", "'")
                        x = x + 1
                        ReDim Preserve AllMeets(3, x)
                    End If
                End If
            Next
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

Open in new window

Bob SchneiderCo-OwnerAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
Just replace these 4 lines..and it should work for you.

sql = "SELECT m.MeetsID, m.MeetName, m.MeetDate, m.MeetSite FROM Meets m INNER JOIN MeetTeams mt ON m.MeetsID = mt.MeetsID INNER JOIN Roster r ON "
sql = sql & "mt.TeamsID = r.TeamsID INNER JOIN IndRslts ir ON ir.MeetsID = mt.MeetsID WHERE ir.RosterID IN (" & sSelectQS & ") AND ir.Place > 0 "
sql = sql & "AND (m.MeetDate >= '" & dBegDate & "' AND m.MeetDate <= '" & dEndDate & "') AND Sport = '" & sSport & "' "
sql = " ;WITH CTE AS ( " & sql & " ,CTE1 AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY MeetsID ORDER BY MeetDate DESC) rnk FROM CTE ) SELECT MeetsID, MeetName,  MeetDate, MeetSite FROM FROM CTE1 WHERE rnk = 1 ORDER BY MeetSite DESC "

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Which columns are duplicate.?

Can you show some input rows and the output you are expecting. We can handle that in SQL itself. Removing duplicates in SQL is very easy and efficient since it follows the set based approach.
0
 
Bob SchneiderCo-OwnerAuthor Commented:
This query culls all Nordic ski meets participated in by a group of skiers over a period of time.  They have not all competed in all meets during this period of time.  I want to get all meet info (m.MeetsID, m.MeetName, m.MeetDate, m.MeetSite) for every meet that at least one of these skiers participated in but I don't want the meet listed multiple times just because multiple skiers skied in the same meet.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Pawan KumarDatabase ExpertCommented:
Please adjust your query like below -

;WITH CTE AS
(
      /*Your Final Query here*/
)
,CTE1 AS
(
      SELECT * , ROW_NUMBER() OVER (PARTITION BY MeetsID ORDER BY MeetDate DESC) rnk FROM CTE
)
SELECT MeetsID, MeetName,  MeetDate, MeetSite FROM
FROM CTE1
WHERE rnk = 1
0
 
Bob SchneiderCo-OwnerAuthor Commented:
Thank you.  What is CTE1 and what do you mean "my final query"?  The actual sql query I am currently using?
0
 
Pawan KumarDatabase ExpertCommented:
Please refer my last comment... use that and see if it is working..
Shall explain you how it is working.
0
 
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Bob, for this question, we really only need to concentrate on lines 2,3 and 4 (the sql) and keep the question limited to ms sql server and not worry about the asp part.

You can test out the supplied sql in manger studio. It may help to include a few rows of fake sample data for each table and the table layouts for the Experts to review.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.