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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.