score:1
Accepted 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'
More questions
- Selecting 7th Person (row) and get 7 as result
- Sum of values in column by row and also selecting other columns in the result set
- SQL Server Get values of top three records and display in one row per person
- how to get header and detail row from a result set?
- Get previous and next row from rows selected with (WHERE) conditions
- Subtract two dates in SQL and get days of the result
- MS SQL 2008 - get all table names and their row counts in a DB
- SQL: Repeat a result row multiple times, and number the rows
- Selecting every nth row from SQL Server 2008 query result where table does not have row id column
- pyodbc on SQL Server - How can I do an insert and get the row ID back?
- how to union two queries in stored procedure to get result in one row
- dapper: Get result and count at same time using QueryMultiple
- Split and get second row as value
- How to get row number based on unique field and sorted by another field
- Tsql - get entire row information with max and group by
- Get "next" row from SQL Server database and flag it in single transaction
- how to get row with max value and it matches from two other tables?
- Group by a column and build a single result row based on condition
- selecting row from one table and insert into other table
- Get the time difference between the first and last row
- How to get SQL result view from row to separate columns
- Get MAX and MIN in a row SQL
- Get sum of amount records as each row is month and column is week using sql?
- Get every second row as a result table in t-sql
- How do you get to limits of 8060 bytes per row and 8000 per (varchar, nvarchar) value?
- SQL Server- JSON object contains both strings and arrays: how to get result with single query?
- How to get max and min of columns per row for pivoted table?
- Get the count of the words `href` and `nofollow` from each row of a table in SQL server
- How to execute statement for each row and return entire result
- Get the rows ONLY where the time difference between the current and the following row is under 5 minutes
More questions with similar tag
- Find next greater number with zeros in sql number
- Why does "SELECT DISTINCT a, b FROM..." return fewer records than "SELECT DISTINCT A + '|' + B FROM..."?
- SQL Profiler - What could be improved?
- How to identify a database when it's being moved to another server/instance
- SQL Server aggregator
- Changing column order with SQL ALTER FUNCTION creates strange results in dependent functions
- How can I "dynamically" split a varchar column by specific characters?
- Specific hierarchy geography
- Unable to parse string using OPENJSON
- Test connection failed because of an error in initializing provider. The 'OraOLEDB.Oracle.1' provider is not registered on the local machine
- How do a join three tables where one table doesn't match the ID of the other two?
- how to skip dynamic header rows in ssis
- Why triggers try to insert NULL value when using a field from 'inserted' table?
- Is it possible/good to specify Fill factor on a SQL Server clustered index via EF Core 5?
- Using Recursive query in function
- Sql sum() function based on input table values
- SQL - How to perform a calculation based on values
- Why ajax error is coming in my Data table in .Net?
- How can I squash these SQL Server results into one row per customer key
- How to add .mdf file in Visual Studio 2019?
- How to parse XML using SP_XML_PREPAREDOCUMENT for UTF-8 in SQL Server
- SQl import BCP error
- A better database design on SQL Server?
- Display multiple single values in a single column
- How to assign id zero while selecting the default in dropdownlist
- How to edit a SQL Server XML data field with asp.net Dynamic Data
- Sql Server transactions - usage recommendations
- Query to list SQL Server stored procedures along with lines of code for each procedure
- SQL Server: How to select top rows of a group based on value of the column of that group?
- LINQ to Entities and null strings
Source:
stackoverflow.com