【Question】

We have a query like this:

 

SELECT u.email, st.value as score,

 

date_format(FROM_UNIXTIME(st.timemodified),'%d-%m-%Y %H:%i:%s' ) as fecha,

 

IF(st.value >= 70, 'YES', 'NO') as result

 

FROM  user u

 

LEFT  JOIN scorm_scoes_track st ON st.userid = u.id

 

LEFT  JOIN scorm_scoes as ss ON ss.scorm = st.scormid

 

WHERE (st.element='cmi.score.raw'  OR st.element='cmi.core.score.raw')

 

ORDER  BY u.id, st.timemodified

 

+------------------+------------+---------------------+----------+

 

| email | score | fecha | result |

 

+------------------+------------+---------------------+----------+

 

| test@test.es | 45| 14-03-2015  17:10:18 | NO |

 

| test@test.es | 65| 14-03-2015  17:12:42 | NO |

 

| test@test.es | 70| 14-03-2015  17:15:04 | YES |

 

| test@test.es | 60| 14-03-2015  17:17:16 | NO |

 

| test@test.es | 65| 14-03-2015  17:17:16 | NO |

 

User passes the test if result is greater/equal than 70. We need results until the value 70 (when test is passed) is shown. In this case, we have this result:

| test@test.es | 45| 14-03-2015  17:10:18 | NO |

 

| test@test.es | 65| 14-03-2015  17:12:42 | NO |

 

| test@test.es | 70| 14-03-2015  17:15:04 | YES |

 

Records where score >70 are discarded.

 

【Answer】

Order-based operations are one of SQL weaknesses. Its window functions generate unreadable queries. An alternative is SPL (Structured Process Language) that generates intuitive and easy to understand code:

	A
1	$select * from tb1 order by   score
2	=A1.group(email).conj(~.to(~.pselect(score==70)))

A1: Data retrieval.

A2: Group email, use conj function to concatenate records and get thoses meeting the specified condition. pselect function gets the sequence number of the first eligible record; to function gets members in a specified interval; “~” represents a group.

esProc SPL can handle almost all complicated intra-group calculations and order-based calculations effortlessly. It can be easily embedded into a Java application via its own JDBC interface. See How to Call an SPL Script in Java.