I want to select the 7th person (Pierre Gasly (for example)) Score list Season F1, so I can return number 7 in my C# application.
x
SELECT CONCAT(strVoorNaam, strTussenVoegsel, ' ', strAchterNaam) AS Naam, SUM(CAST(tblPunten.strScore AS INT)) AS score
FROM tblPunten
JOIN tblPuntenCoureur ON tblPuntenCoureur.PuntenID = tblPunten.ID
JOIN tblPersoon ON tblPersoon.ID = tblPuntenCoureur.PersoonID
JOIN tblSeizoen ON tblSeizoen.ID = tblPuntenCoureur.SeizoenID
WHERE tblPunten.ID != 12 AND tblPunten.ID != 13 AND tblPunten.ID != 14 AND tblPunten.ID != 15
AND tblPunten.ID != 16 AND tblPunten.ID != 17 AND tblPunten.ID != 18 AND tblPunten.ID != 19 AND tblSeizoen.intJaartal = 2019
GROUP BY CONCAT(strVoorNaam, strTussenVoegsel, ' ', strAchterNaam), tblPersoon.strAchterNaam
ORDER BY score DESC
Advertisement
Answer
Use a row limiting clause:
select
concat(strvoornaam, strtussenvoegsel, ' ', strachternaam) as naam,
sum(cast(pu.strscore as int)) as score
from tblpunten pu
join tblpuntencoureur pc on pc.puntenid = pu.id
join tblpersoon pe on pe.id = pc.persoonid
join tblseizoen se on se.id = pc.seizoenid
where pu.id not between 12 and 19 and se.intjaartal = 2019
group by strvoornaam, strtussenvoegsel, strachternaam, pe.strachternaam
order by score desc
offset 6 rows fetch next 1 row only
This gives you the seventh row in the resultset.
Side notes:
- table aliases help keeping the query concise and easier to write
- you should be qualifying all columns that come into play in the query
On the other hand, if you are trying to get the rank of a specific person, then that’s different. You can use rank()
instead:
select *
from (
select
concat(strvoornaam, strtussenvoegsel, ' ', strachternaam) as naam,
sum(cast(pu.strscore as int)) as score,
rank() over(order by sum(cast(pu.strscore as int)) desc) rn
from tblpunten pu
join tblpuntencoureur pc on pc.puntenid = pu.id
join tblpersoon pe on pe.id = pc.persoonid
join tblseizoen se on se.id = pc.seizoenid
where pu.id not between 12 and 19 and se.intjaartal = 2019
group by strvoornaam, strtussenvoegsel, strachternaam, pe.strachternaam
) t
where naam = 'Pierre Gasly'