Geek asks and answers

Group by column, select last value

I execute a query in a table that tracks the results of a test taken by students. A test consists of several sections, and there is a column for each section. Each row is an instance of a test taken by a student. Sections can either be taken immediately or divided into several attempts. For example, a student can take one section today, and tomorrow - tomorrow. In addition, the student is allowed to sort out any part of the test.

Student example :

StudentID WritingSection ReadingSection MathSection DateTaken
1 65 85 54 4/1/2013 14:53
1 98 NULL NULL 8/8/2013 13:13
1 NULL NULL 38 5/3/2013 12:43

A NULLmeans that the section has not been entered for this test instance, and the evaluation of the second section means that the section has been restored.

I need a query that is grouped using StudentIDsuch that there is only one row for each student, and the last score for each section is returned. I am looking for an effective way to solve this problem, since we have many thousands of testing attempts in the database.

Expected Result:

StudentID WritingSection ReadingSection MathSection DateTaken
1 98 85 38 5/3/2013 12:43

EDIT: There were a lot of good decisions. I want to experiment with each next week a little earlier, before choosing an answer. Thanks everyone!

+3
source
6

, , , :) MOST RECENT. , .

, , , , , , -, , :)

, :

, - PIVOT UNPIVOT:

SELECT StudentID, [WritingSection], [ReadingSection], [MathSection], MAX(DateTaken) DateTaken
FROM (
  SELECT StudentID, Subject, DateTaken, Score
  FROM (
    SELECT StudentID, Subject, DateTaken, Score
      , row_number() OVER (PARTITION BY StudentID, Subject ORDER BY DateTaken DESC) as rowNum
    FROM Students s
    UNPIVOT (
      Score FOR Subject IN ([WritingSection],[ReadingSection],[MathSection])
    ) u
  ) x
  WHERE x.rowNum = 1
) y
PIVOT (
  MAX(Score) FOR Subject IN ([WritingSection],[ReadingSection],[MathSection])
) p
GROUP BY StudentID, [WritingSection], [ReadingSection], [MathSection]

(x) SQL UNPIVOT ( , ).

(y) , . ( SQL, , row_number() WHERE ).

, , (1 ), SQL PIVOT. - . , , , ( , " " ). , 3 DateTakens, .

, , - .

+2

. . max() min() .

, , NULL. :

select s.StudentId,
       max(case when ws_seqnum = 1 then WritingSection end) as WritingSection,
       max(case when rs_seqnum = 1 then ReadingSection end) as ReadingSection,
       max(case when ms_seqnum = 1 then MathSection end) as MathSection,
       max(DateTaken) as DateTaken
from (select s.*,
             row_number() over (partition by studentid
                                order by (case when WritingSection is not null then 0 else 1 end), DateTaken desc
                               ) as ws_seqnum,
             row_number() over (partition by studentid
                                order by (case when ReadingSection is not null then 0 else 1 end), DateTaken desc
                               ) as rs_seqnum,
             row_number() over (partition by studentid
                                order by (case when MathSection is not null then 0 else 1 end), DateTaken desc
                               ) as ms_seqnum
      from student s
     ) s
where StudentId = 1
group by StudentId;

where . , .

, , . , /, . ( , , , .)

+2

DateTaken?

SELECT max (DateTaken) FROM TABLE_NAME WHERE StudentID = 1

-, ?

SELECT WritingSection FROM TABLE_NAME WHERE StudentID = 1 DateTaken = (SELECT max (DateTaken) FROM TABLE_NAME                                    WHERE StudentID = 1 WritingSection IS NOT NULL)

ReadingSection MathSection?

0

Joe - , . - . , Oracle:

SELECT a.StudentID, a.DateTaken
  FROM (  SELECT StudentID,
             DateTaken,
             ROW_NUMBER ()
                OVER (PARTITION BY StudentID ORDER BY DateTaken DESC)
                rn
        FROM pto.test
    ORDER BY DateTaken DESC) a
 WHERE a.rn = 1

, row_number() 1 . select rn = 1... , , . , , . , ...

0

SQL - . , :

SELECT s.*
FROM Students s
JOIN (
  SELECT StudentID, MAX(DateTaken) as MaxDateTaken
  FROM Students
  GROUP BY StudentID
) f ON s.StudentID = f.StudentID AND s.DateTaken = f.MaxDateTaken

Joining a date field is not super ideal (it breaks in the case of relations for MAX) or fast (depending on how the table is indexed). If you have an int rowID that is unique to all rows, it would be preferable:

SELECT s.*
FROM Students s
JOIN (
  SELECT rowID
  FROM (
    SELECT StudentID, rowID, row_number() OVER (PARTITION BY StudentID ORDER BY DateTaken DESC) as rowNumber
    FROM Students
  ) x
  WHERE x.rowNumber = 1
) f ON s.rowID = f.rowID
0
source 
SELECT student.studentid, 
       WRITE.writingsection, 
       READ.readingsection, 
       math.mathsection, 
       student.datetaken 
FROM 
-- list of students / max dates taken 
(SELECT studentid, 
        Max(datetaken) datetaken 
 FROM   test_record 
 GROUP  BY studentid) student, 
-- greatest date for student with a writingsection score (dont care what the date is here, just that the score comes from the greatest date) 
(SELECT studentid, 
        writingsection 
 FROM   test_record t 
 WHERE  writingsection IS NOT NULL 
        AND datetaken = (SELECT Max(datetaken) 
                         FROM   test_record 
                         WHERE  studentid = t.studentid 
                                AND writingsection IS NOT NULL)) WRITE, 
(SELECT studentid, 
        readingsection 
 FROM   test_record t 
 WHERE  readingsection IS NOT NULL 
        AND datetaken = (SELECT Max(datetaken) 
                         FROM   test_record 
                         WHERE  studentid = t.studentid 
                                AND readingsection IS NOT NULL)) READ, 
(SELECT studentid, 
        mathsection 
 FROM   test_record t 
 WHERE  mathsection IS NOT NULL 
        AND datetaken = (SELECT Max(datetaken) 
                         FROM   test_record 
                         WHERE  studentid = t.studentid 
                                AND mathsection IS NOT NULL)) math 
WHERE 
  -- outer join in case a student has no score recorded for one or more of the sections  
  student.studentid = READ.studentid(+) 
  AND student.studentid = WRITE.studentid(+) 
  AND student.studentid = math.studentid(+);
0
source

More articles:


All Articles