Getting the n-th record in the SQL database where the table is result of another query

Question

I have number of users, each taking 10 questions and leave some time stamps. What I ideally want to do is to find the complete time of the n-th user where n is determined by the ascending order of time stamps, not the user ids.

This will not work, but to give you a feel of what I need I would be very happy if this could run:

SELECT <N-TH> MAX(u.time) AS maxTime FROM Users u
Group BY u.userId   ORDER BY maxTime

Any thoughts? right now I am running

SELECT MAX(u.time) AS maxTime FROM Users u
Group BY u.userId   ORDER BY maxTime

get the result as array and then find the n-th entry.

possible duplicate of [Row Offset in SQL Server](http://stackoverflow.com/questions/187998/row-offset-in-sql-server) — fancyPants, Jun 07 '13 at 13:33

Dave · Answer 1 · 2013-06-10T00:41:17.853

0

Many possible ways but just off cuff...could use CTEs or subquery. Try

SELECT MAX([maxTime]) AS [LastEntry] FROM (
    SELECT MAX(u.time) AS maxTime,u.userId 
    FROM Users u 
    GROUP BY u.userId,u.time) t

OK, try this...

WITH CTE ([maxTime], [userId ]) AS (
    SELECT MAX([time]) AS [maxTime],
        [userId]
    FROM Users
    GROUP BY [userId])
    SELECT MAX([maxTime]) AS [maxTime]
    FROM CTE;

HTH

Dave

edited Jun 10 '13 at 00:41

answered Jun 07 '13 at 13:35

Dave

740
1
6
17

Unfortunately, the grouping commands cannot be used in a sub query – AKG Jun 07 '13 at 14:37

Edper · Answer 2 · 2013-06-07T15:29:33.810

0

Have you tried using ROW_NUMBER() function

SELECT * FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY maxTime) as RowNum, MAX(u.time) AS maxTime FROM Users u
Group BY u.userId
) A
WHERE RowNum = NthNumber
ORDER BY maxTime

where NthNumber is the ranking number.

edited Jun 07 '13 at 15:29

answered Jun 07 '13 at 13:38

Edper

9,144
1
27
46

Unfortunately, order by cannot be used in sub-queries. – AKG Jun 07 '13 at 14:44
You're right @user1288502 actually I just forget to remove it. But it could now be sorted if it is used outside which my query already had. – Edper Jun 07 '13 at 15:32

score 0 · Answer 3 · answered Jun 07 '13 at 13:44

GROUP BY UserId, and apply ROW_NUMBER based on max(u.time) to create a ranking pseudo-column that you can then filter on

select min_utime, max_utime, userId from (
select min(u.time) min_utime, max(u.time) max_utime, u.userId,
row_number() over (order by max(u.time) asc) as ranker
from Users u
group by u.userId
) Z  where ranker  = @n

score 0 · Answer 4 · answered Jun 09 '13 at 13:09

Get the first 10 rows as sorted by the maximum time:

SELECT TOP 10 userId, MAX(time) AS maxtime
FROM Users
GROUP BY userId
ORDER BY maxtime ASC

Then just reverse the order and take the top row:

SELECT TOP 1 *
FROM (
  SELECT TOP 10 userId, MAX(time) AS maxtime
  FROM Users
  GROUP BY userId
  ORDER BY maxtime ASC
) s
ORDER BY maxtime DESC;

Getting the n-th record in the SQL database where the table is result of another query

4 Answers4