mysql - include only the first and last groups in query results -
given schema
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
Post a Comment