Selecting unique rows

[Andy Dufresne]

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

gives me this output:

id attempt question att_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


Thanks

[Gert-Jan Strik]

That's what aggregates are for:

SELECT id, attemp, question, att_number, answer
, MIN(timestamp) AS min_timestamp
, MIN(event) AS min_event -- or maybe you want SUM(event)
FROM mytable
GROUP BY id, attemp, question, att_number, answer

--
Gert-Jan

Post by Andy Dufresne
select
id,
attempt,
question,
att_number,
answer,
timestamp,
event
from
mytable
id attempt question att_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?
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
Thanks

[Erland Sommarskog]

Post by Andy Dufresne
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?

WITH numbering AS (
SELECT attempt, question, att_number, answer, timestamp, event,
rowno = row_number() OVER (
PARTITION BY attempt, question, att_number, answer, event
ORDER BY timestamp)
FROM mytable
)
SELECT attempt, question, att_number, answer, timestamp, event, timestamp
FROM numbering
WHERE rowno = 1

--
Erland Sommarskog, Stockholm, ***@sommarskog.se