mysql - include only the first and last groups in query results -


given schema

enter image description here

the following query

select a.user_id,   a.id,   a.date_created,   avg(ai.level) level assessment   join assessment_item ai on a.id = ai.assessment_id group a.user_id, a.id; 

returns these results

user_id, a.id, a.date_created,        level 1,       99,   "2015-07-13 18:26:00", 4.0000   1,       98,   "2015-07-13 19:04:58", 6.0000   13,      9,    "2015-07-13 18:26:00", 2.0000   13,      11,   "2015-07-13 19:04:58", 3.0000   

i change query such earliest results returned each user. in other words, following should returned instead

user_id, a.id, a.date_created,        level 1,       99,   "2015-07-13 18:26:00", 4.0000 13,      9,    "2015-07-13 18:26:00", 2.0000 

i think need add having clause, i'm struggling figure out exact syntax.

i have done this, except small difference wanted first 5 per group. usage case reporting - means time running query / creation of temp table not constraint.

the solution had:

  • create new table columns id( reference original table) , id can unique/primary
  • insert ignore tbl1 (id) select min(id) original_tbl id not in (select id tbl1) group user_id
  • repeat step 2 many times required( in case 5 times). new table table have ids want show
  • now run join on tbl1 , original table give required result

note: might not best solution, worked me when had share report in 2-3hours in weekend. , data size had around 1m records


Comments

Popular posts from this blog

javascript - Using jquery append to add option values into a select element not working -

Android soft keyboard reverts to default keyboard on orientation change -

Rendering JButton to get the JCheckBox behavior in a JTable by using images does not update my table -