CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Mihran Hovsepyan
Mihran Hovsepyan

Reputation: 11088

Can't write query using SQL Server

I am writing SQL Server database, where I have a relation UserTypedText(userId, date, textId, speed). (which user when types which text in which speed) Where (userId, date) is key. So I want to query from this table last 10 (sorted by date decreasing order) rows for each user. If user has not typed 10 texts, then get all information about his types. Can you please help me with this query? It is very important to do all this via single query, to not UNION results of results.

Upvotes: 0

Views: 586

Answers (4)

Patches
Patches

Reputation: 1125

You can do it with a join on your user table:

SELECT T.*
FROM Users AS U INNER JOIN (SELECT TOP 10 *
                       FROM UserTypedText as T
                       Where T.userID = U.userID
                       Order By date DESC)

Upvotes: 0

AdaTheDev
AdaTheDev

Reputation: 147224

From SQL 2005 and later, you can use ROW_NUMBER with PARTITION BY to do this:

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) AS RowNo, *
FROM UserTypedText
) x
WHERE x.RowNo <= 10

Upvotes: 2

Callie J
Callie J

Reputation: 31296

For SQL Server 2005/2008, we can use CTEs and ROW_NUMBER() to get what we want... this is untested, but should work:

;WITH numbered AS (
    SELECT userId, date, textId, speed, 
            ROW_NUMBER() OVER (PARTITION BY userId ORDER BY date DESC) AS user_row_number
        FROM UserTypedText
)
SELECT userId, date, textId, speed 
    FROM numbered 
    WHERE user_row_number <= 10 
    ORDER BY userId, date

This returns the top 10 for every user in a single result set, ordered by user and date.

Upvotes: 3

Oded
Oded

Reputation: 498982

I would write a stored procedure that would take the user Id of the user you are interested in, and for each user:

SELECT TOP 10 *
FROM UserTypedText
WHERE userId = @userId
ORDER BY date

Where @userId is the passed in parameter.

Upvotes: 1

Related Questions