Apache11 Apache11 - 5 months ago 7
SQL Question

displaying questions

hello am having student data i want to display only first questions of respective candidates from every section...

Answer

Solution mimics SQL SERVER Row_Number() function. Google mysql over order by points in the right direction.

/*create temporary table t(can_id int,    status varchar(20));

insert into t values            
(001 ,   'section 1 question 1'),    
(001 ,   'section 1 question 2'),   
(001 ,   'section 1 question 3'),  
(001 ,   'section 1 question 4'),    
(001 ,   'section 2 question 1'),    
(001 ,   'section 2 question 2'),    
(001 ,   'section 2 question 3'),    
(001 ,   'section 2 question 4'),    
(001 ,   'section 3 question 1'),    
(001 ,   'section 3 question 2'),    
(001 ,   'section 3 question 3'),    
(002 ,   'section 1 question 1'),
(002 ,   'section 1 question 2'),
(002 ,   'section 1 question 3'),
(002 ,   'section 2 question 1'),
(002 ,   'section 2 question 2'),
(002 ,   'section 2 question 3'),
(002 ,   'section 3 question 1'),
(002 ,   'section 3 question 2'),
(002 ,   'section 3 question 3'),
(002 ,   'section 3 question 4');

*/

select s.tcanid as 'can.id', substring(s.tstatus,11,11) as Question
from
(
select   t.can_id as tcanid, t.status as tstatus ,
            @sno :=if(@previd = concat(t.can_id,substring(t.status,1,9)),@sno + 1,1) as canid
          ,@previd:=concat(t.can_id,substring(t.status,1,9)) prevcanid
from t,
(select @sno:= 0) as sn
,(select @Previd:= '') as p
) s
where s.canid <= 3
Comments