locked
select Top 5 points RRS feed

  • Question

  • User-1738454658 posted

    I have an asp.net application, of which I in a combobox first select Continent and then can choose different cups.
    The result is presented in an asp: GridView.
    Below how the sql statement looks now.

    Events that may be included in the cup must have been completed (date_event) in the current competition season (date_event BETWEEN '"& startdate &"' And '"& enddate &"').
    The leader / winner of the Cup is based on how many competitions the person has run which is summed up to the total points
    Most points win the cup.

    To my question, so far, I think I got it to work. But if a person has run for example 7 races then I have to filter out 2 races that have the lowest cup points (wcp_point)

    I can only count a maximum of 5 events per competitor, and sort out the competitions that have the lowest cup points.
    I want to count the 5 events that the competitorhas the highest score.

    Someone who can assist me in this problem?

    It will probably be like TOP 5 wcp_point,, but it will be wrong than how I do it .........

                Case 1 : MyDataAdapter = New SqlDataAdapter("SELECT SUM(wcp_point) AS points, Count(class) AS race, firstname, lastname, did FROM wcpcup " &
            "Where continent = @continent " &
            "AND (class IN ('SM1', 'SM1V', 'SM2', 'SM2V', 'PM1', 'PM4', 'CM')) " &
            "AND (date_event BETWEEN '" & startdate & "' And '" & enddate & "')  " &
            "GROUP BY firstname, lastname, did ORDER BY points DESC", MyConnection)
    
                Case 2 : MyDataAdapter = New SqlDataAdapter("SELECT SUM(wcp_point) AS points, Count(class) AS race, firstname, lastname, did FROM wcpcup " &
            "Where continent = @continent " &
            "AND (class IN ('SW1', 'SW1V', 'SW2', 'SW2V', 'PW1', 'PW4', 'CW')) " &
            "AND (date_event BETWEEN '" & startdate & "' And '" & enddate & "')  " &
            "GROUP BY firstname, lastname, did ORDER BY points DESC", MyConnection)

    Monday, June 10, 2019 9:40 AM

All replies

  • User753101303 posted

    Hi,

    So you tried "SELECT TOP 5 SUM(wcp_points) AS points, etc...." but you don't get the expected result? What happens then?

    Not directly related but :
    - search for ADO.NET parameters, you should avoid building a full SQL string yourself
    - as the only difference seeems to be classes it could be better to just define that in each select and then use common code (so that you keep apart really only actual differences between those two cases)

    Monday, June 10, 2019 12:35 PM
  • User-1738454658 posted

    Same result...

    I'll probably start over and try to get to how I filter out for each competitor the 5 competitions that have given the most points.
    Some athletes may only have 1 competition in the table and others have 7-8 competitions
    Each competitor may only count a maximum of 5 competitions, and then those with the highest score...

    Tips how ;-)

    Monday, June 10, 2019 11:28 PM
  • User-1174608757 posted

    Hi Mr Ek,

    According to your description,I still think that you could use top query to get top5 result in the database.So what do you mean about same result?Could you please show me how you use top query?I have made a sample here.I hope it could help you.

    My cmpt table 

    Then you could use top5 to get the point.You could write as below:

    select distinct top 5 point from cmpt order by point desc

    Best Regards

    Wei

    Tuesday, June 11, 2019 6:47 AM
  • User753101303 posted

    If you want to do that for each competitor in the same query you could use https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 to number rows within each group and keep only those having rownym<=5.

    See for example https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group (that suggest also other approach but IMO use this one if you can for clarity).

    Tuesday, June 11, 2019 7:09 AM
  • User-1738454658 posted

    Yes, I want top 5 event for each competitor in the same query

    If i use SELECT TOP 5 SUM(wcp_point) AS points, Count(class) AS race, firstname, lastname.....
    I get top 5 rows (i have 6) and Kalle Karlsson, still has 6 Race. Mr NoName is gone.

    And if I don't use the top 5, the same as the code above, it looks like this...

    I want everyone who has received points to be seen, but Kalle Karlsson can only count 5 Race (now all are counted for Kalle Karlsson) and the competition/race that gave the least points should be counted off.

    Tuesday, June 11, 2019 1:47 PM
  • User-1174608757 posted

    Hi Mr Ek,

    Select Top5 will only get the five results which has max value in one column in table,it will not change the column value.So if you want to get the sum value of 5 race ,you should do it in the table which has  point of each point.According to your table, I could only see the  six rows.

    Best Regards

    Able

    Wednesday, June 12, 2019 8:49 AM