CODEWITHSUNDEEP
sqlsql-server-2008-r2
luisdev
luisdev

Reputation: 568

SQL: selecting unique rows

select
    id,
    attempt,
    question,
    att_number,
    answer,
    timestamp,
    event
from
    mytable

gives me this output in MSSQL 2008 R2:

id,attempt,question,seq_number,answer,timestamp,event
1296040,22059,3813,0,"11960,11961,11959,11958:",1265006717,0
1296165,22059,3813,1,"11960,11961,11959,11958:11960",1265011083,2
1296166,22059,3813,1,"11960,11961,11959,11958:11960",1265011049,6
1296163,22059,3813,1,"11960,11961,11959,11958:11960",1265011037,6
1296164,22059,3813,1,"11960,11961,11959,11958:11960",1265011072,6

How do I select just the unique rows for the attempt, question, att_number, answer and event columns with the LOWEST value for the timestamp column?

I want this:

id,attempt,question,seq_number,answer,timestamp,event
1296040,22059,3813,0,"11960,11961,11959,11958:",1265006717,0
1296165,22059,3813,1,"11960,11961,11959,11958:11960",1265011083,2
1296163,22059,3813,1,"11960,11961,11959,11958:11960",1265011037,6

Upvotes: 2

Views: 376

Answers (5)

Tim Schmelter
Tim Schmelter

Reputation: 460108

You can use a ranking function:

with cte as(
    select id,attempt,question,att_number,answer,timestamp,event,
           rn = Row_Number() Over (
                    Partition By attempt, question, att_number, answer,event 
                    Order By timestamp Asc)
    from mytable
)
select select,id,attempt,question,att_number,answer,timestamp,event
from cte
where rn = 1

If you want all records with the lowest timestamp per group replace Row_Number with Dense_Rank.

Upvotes: 2

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

You'll need to use GROUP BY with MIN:

select MIN(id),
       attempt,
       question,
       seq_number,
       answer,
       MIN(timestamp),
       event
  from mytable
  GROUP BY ATTEMPT, QUESTION, SEQ_NUMBER, ANSWER, EVENT

It's not obvious from the question, but OP wants both MIN(ID) and MIN(TIMESTAMP). Also note that the column names in the example results are NOT the same as the ones in the OP's SQL. I chose to accept that the seq_number in the example results are the same as att_number from the original SELECT statement.

SQLFiddle here.

Share and enjoy.

Upvotes: 1

Robert
Robert

Reputation: 25753

You have to use group by clause.

Below select statement will work for your example.

select        
    min(id), attempt,question,
    att_number, answer, timestamp,
    event
from
    mytable
group by 
    attempt,question,
    att_number, answer, timestamp,
    event

But if you really want to the lowest timestamp, you have to remove id column form select as below

select         
    attempt,question,
    att_number, answer, min(timestamp),
    event
from
    mytable
group by 
    attempt,question,
    att_number, answer,
    event

Upvotes: 1

Loxley
Loxley

Reputation: 1781

select
id,
attempt,
question,
att_number,
answer,
timestamp,
event
from
mytable m 
where m.timestamp=(
    select min(timestamp) 
    from mytable mi 
    where mi.attempt=m.attempt and mi.question=m.question and mi.att_number=m.att_number and mi.answer=m.answer and mi.event=m.event
)

Upvotes: 1

nigel
nigel

Reputation: 99

use distinct and order by statement.

Upvotes: -1

Related Questions