#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2016
    Posts
    2
    Rep Power
    0

    Delphi SQL query retreiving 4 records of each group


    The code below should GROUP the rider\horse combination and take only four records of each rider and give a score for the TOP5 rider/horse combination. I tried to insert LIMIT 4 but get syntax error.
    The code does GROUP but add all rider/horse score together for the group and not the highest 4 records scores for that rider/horse group.


    Select TOP 5 rider_id,rider_name,rider_surname,horse_name,
    sum(score) as Total from tblCompetition
    WHERE decipline='Dress'
    GROUP by rider_id,rider_name,rider_surname,horse_name
    ORDER by sum(score) DESC
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,116
    Rep Power
    2010
    Sounds like you are using MS SQL as that database use TOP.
    LIMIT is used by other databases like MySQL.

    Based on you query, you should get the sum of score for each riders performance with a specific horse, and then return the top 5 combination of rider/horse which yields the highest score (read: best performance).
    This match with what you say for "add all rider/horse score together for the group".

    I am not sure what you want when you say:
    not the highest 4 records scores for that rider/horse group.
    Maybe you can give some example of the data you have and what result you expect?

    Furthermore, it looks like your table could use an optimization using normalization.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    2
    Rep Power
    0
    Try this query:
    Code:
    Select TOP 5 rider_id, rider_name, rider_surname, horse_name, sum(score) as Total 
    from
      (Select TOP 4 rider_id, rider_name, rider_surname, horse_name, score 
       From tbleCompetition
       Order by score DESC) subquery1
    Group by rider_id, rider_name, rider_surname, horse_name
    Order by 5 DESC
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2016
    Posts
    2
    Rep Power
    0
    a group is rider and horse combination,.
    if the rider horse has 6 entries and points for each entry that will be grouped together and points added, that is what the code does now.

    The code must not add all the entries only take the first 4 highest points(score) and add the points (score) discard the rest.

    Entries for rider\horse combination and order by rider_id, score
    rider_id name surname horse score
    001 a a a 5
    001 a a a 5
    001 a a a 5
    001 a a a 2
    001 a a a 2
    001 a a a 2

    002 b b b 5
    002 b b b 5
    002 b b b 5
    002 b b b 4
    002 b b b 4
    002 b b b 3

    my current code creates group for each rider\horse and adds the score, which is incorrect
    001 a a a 21
    002 b b b 26

    what it should do and expected is to take only the first 4 records of each group and add them
    this what is to be expected
    001 a a a 17
    002 b b b 19

    I am using Access database and TQuery to query the database which works fine up to this point

IMN logo majestic logo threadwatch logo seochat tools logo