How to select unique row according to last accessed time

Question

I want to get only distinct rows with their respective AnsId,

like Qid: 01 have last AnsId: 01

I have following data structure,

Qid  Time                  AnsId
01   2011-09-26 12:55:10   01
02   2011-09-26 12:58:32   03
03   2011-09-26 12:59:05   02
01   2011-09-26 01:02:10   01
03   2011-09-26 01:30:10   01
02   2011-09-26 01:59:10   02

I have written following query but it returns all the rows:

SELECT DISTINCT Qid, Time, AnsId
FROM table
ORDER BY Time DESC

Then what is missing part in the select query?

If you want _DISTINCT_ records, you have written the right query: Time is part of resulting record, so you get ALL records... — Marco, Sep 26 '11 at 08:32

score 2 · Accepted Answer · answered Sep 26 '11 at 08:33

2

You could use row_number() to find the last answer per Qid:

select  *
from    (
        select  row_number() over (partition by Qid order by Time desc) as rn
        ,       *
        from    YourTable
        ) as SubQueryAlias
where   rn = 1

The subquery is required because SQL Server doesn't allow row_number directly in a where.

answered Sep 26 '11 at 08:33

Andomar

216,619
41
352
379

Thanks for the answer, I have tested the code, but it is not returning rows in descending order of Time column.. Any help? – Sep 26 '11 at 09:02
@DarshanJoshi - Just add `order by Time desc` at the end if the query. – Mikael Eriksson Sep 26 '11 at 09:17
Still getting the rows in ascending order. – Sep 26 '11 at 09:23
@DarshanJoshi You do know that `12:55:10` is just after lunch and that `01:02:10` is in the middle of the night where a lot of people are sleeping? – Mikael Eriksson Sep 26 '11 at 09:26

Mikael Eriksson · Answer 2 · 2011-09-26T09:16:47.783

1

declare @T table
(
  Qid char(2),
  [Time] datetime,
  AnsId char(2)
)

insert into @T values  
('01',   '2011-09-26 12:55:10',   '01'),
('02',   '2011-09-26 12:58:32',   '03'),
('03',   '2011-09-26 12:59:05',   '02'),
('01',   '2011-09-26 01:02:10',   '01'),
('03',   '2011-09-26 01:30:10',   '01'),
('02',   '2011-09-26 01:59:10',   '02')

select T.Qid,
       T.[Time],
       T.AnsId
from 
    (
      select T.Qid,
             T.[Time],
             T.AnsId,
             row_number() over(partition by T.Qid order by T.[Time] desc) as rn
      from @T as T
    ) as T
where T.rn = 1
order by T.[Time] desc

Result:

Qid  Time                    AnsId
---- ----------------------- -----
03   2011-09-26 12:59:05.000 02
02   2011-09-26 12:58:32.000 03
01   2011-09-26 12:55:10.000 01

edited Sep 26 '11 at 09:16

answered Sep 26 '11 at 08:37

Mikael Eriksson

128,815
20
186
261

Thanks, the query has returned rows sorted by Time column, but is it possible to get only unique Qid rows? – Sep 26 '11 at 09:18
@DarshanJoshi - Yes, just remove `, T.AnsId` from the `partition by`. Was not clear to me if you wanted that or not. Now this answer is the same as the one provided by Andomar except for the `order by` clause. – Mikael Eriksson Sep 26 '11 at 09:19

How to select unique row according to last accessed time

2 Answers2