0
id  serviceid   name  cost   date
201  15          X      50    25.12.2016
201  15          Y      55    29.11.2016
201  120         Z      50    27.11.2016
201  19          w      50    22 .11.2016
201  158         p      50    23.11.2016
201  18          q      50    21.11.2016
201  16          rs     50    24.11.2016
201  81          rs     50    2.11.2016
202  18          X      50    25.12.2016
202  18          Y      55    29.11.2016
202  15          Z      50    27.11.2016
202  19          w      50    22 .11.2016
203  15          p      50    23.11.2016
203  18          q      50    21.11.2016
203  16          rs     50    24.11.2016
0    81          rs     50    2.11.2016

Desire Output :

id  serviceid   name  cost   date
201  15          X      50    25.12.2016
201  15          Y      55    29.11.2016
201  120         Z      50    27.11.2016
201  16          rs     50    24.11.2016

202  18          X      50    25.12.2016
202  18          Y      55    29.11.2016
202  15          Z      50    27.11.2016
202  19          w      50    22 .11.2016

203  15          p      50    23.11.2016
203  18          q      50    21.11.2016
203  16          rs     50    24.11.2016

0    81          rs     50    2.11.2016

i want to display each record 4 - 4 record service for each id i am trying to apply using self Join but there is Problem coming please tell or suggest me how to ac-chive for this.

SELECT a.*
FROM mytable AS a
WHERE 
  (SELECT COUNT(*) FROM mytable AS b 
  WHERE b.id = a.id and b.serviceid >= a.serviceid) <= 4
ORDER BY a.id   , a.date

this query am trying but i am unable to fetch it for each id there should top 4 service id based on date.

Srjoan Jee
  • 17
  • 3
  • This will require simulating row number, which MySQL doesn't have. But besides that, you need to give us the logic by which 4 records per group are being retained. – Tim Biegeleisen Nov 22 '16 at 05:01
  • yes i have to get for each id top 4 serviceid based on top date like my desire output – Srjoan Jee Nov 22 '16 at 05:03
  • @TimBiegeleisen it is not possible in mysql ? can u please suggest me – Srjoan Jee Nov 22 '16 at 05:10
  • You most likely would need some dynamic SQL to handle this: http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Tim Biegeleisen Nov 22 '16 at 05:14
  • @TimBiegeleisen i have tried this but each time it gives 4 suppose i want to increase this data then i am unable to increase – Srjoan Jee Nov 22 '16 at 05:24

1 Answers1

0

To make your desired result, I think you should use mysql variable like this:

select
    t1.*
from mytable t1
join (
    select
        `id`,
        `serviceid`,
        `name`,
        `cost`,
        `date`,
        @rowno := case when @grp = `id` then @rowno + 1 else 1 end as rowno,
        @grp := `id`

    from mytable
    cross join (select @rowno:=0, @grp:=null) v
    order by `id`, `date` desc) t2
on t1.id = t2.id
and t1.`date` = t2.`date`
and t2.rowno < 5

and this seems to be a classic top x record in each group issue, take a look of How to select the first/least/max row per group in SQL

Blank
  • 12,308
  • 1
  • 14
  • 32