2

I got the races to work then I added another query in addition to the same statement but when I ran the query and it only show student number, dob, gender and ethnicity but did not show another select with races add to. I cannot figure it out.

select s.student_number,s.dob,s.gender,s.ethnicity
from students s, 
(select lastfirst,grade_level,
   max(case when rn = 1 then racecd end) race1, -- find 1st row
   max(case when rn = 2 then racecd end) race2, -- find 2nd row
   max(case when rn = 3 then racecd end) race3  -- find 3rd row
from
 (
   select
      s.lastfirst,s.grade_level,sr.racecd,
      row_number() -- one row per race, numbered 1,2,3,...
      over (partition by sr.studentid 
            order by sr.racecd) rn
   from students s
   join studentrace sr
     on s.id=sr.studentid
   where s.enroll_status=0
 )
 group by lastfirst,grade_level
 order by grade_level asc)

closed as unclear what you're asking by ypercubeᵀᴹ, Philᵀᴹ, Mark Sinkinson, Colin 't Hart, dezso Oct 13 '15 at 10:51

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    What are the 2 selects? What exactly are you trying your query to do? – ypercubeᵀᴹ Oct 13 '15 at 8:53
0

you need to reference the race fields in the initial SELECT statement (before the FROM clause) with an alias to the inner select. I'm not quite sure what it is that you're trying to accomplish, but this is certainly more on track with what you desire. Try this for starters

SELECT
    s.student_number,
    s.dob,
    s.gender,
    s.ethnicity,
    RaceTable.race1,
    RaceTable.race2, 
    RaceTable.race3
FROM students s INNER JOIN
    (SELECT
        lastfirst, grade_level,
        max(case when rn = 1 then racecd end) race1, -- find 1st row
        max(case when rn = 2 then racecd end) race2, -- find 2nd row
        max(case when rn = 3 then racecd end) race3  -- find 3rd row
     FROM
         (SELECT
             st.lastfirst,st.grade_level,sr.racecd,
             st.student_number,st.dob,st.gender,st.ethnicity,
             row_number() -- one row per race, numbered 1,2,3,...
                 over (partition by sr.studentid 
                 order by sr.racecd) rn
          FROM students st
          JOIN studentrace sr
              ON st.id=sr.studentid
          WHERE st.enroll_status=0
         ) AS RTinner
     GROUP BY lastfirst,grade_level
  -- ORDER BY grade_level asc
    ) AS RaceTable
ON s.lastfirst=Racetable.lastfirst AND
   s.grade_level=Racetable.grade_level
  • note, I believe this will fail too because rn is not among the GROUP BY fields. also, the grade_level sort isn't doing anything for you because it's not being returned by the outer query either. – mpag Oct 13 '15 at 2:22

Not the answer you're looking for? Browse other questions tagged or ask your own question.