how to fetch data top 4 record in mysql

Question

CREATE TABLE `EventList` (
  `GroupID` int(11) NOT NULL ,

    `eventID` int(11) NOT NULL ,
  `EMPNAME` varchar(20) NOT NULL,
  `EMPAGE` int(11) NOT NULL,
  `SALARY` bigint(20) NOT NULL,
  `ADDRESS` varchar(20) NOT NULL,
  `empaddress` varchar(255) DEFAULT NULL,
  `file_data` tinyblob

) 


insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,2,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,5,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,7,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,8,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (1,9,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (2,15,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (2,16,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,19,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,22,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,24,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,27,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (3,29,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,31,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,32,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,33,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,34,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,35,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,36,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,37,"anil",5,556,'del','del','//document')
insert into eventlist(GroupID,eventID,EMPNAME,EMPAGE,SALARY,ADDRESS,empaddress,file_data) value (0,39,"anil",5,556,'del','del','//document')

this is my query for table Structure:

GroupID eventID EMPNAME EMPAGE  SALARY  ADDRESS empaddress  file_data
1          2    anil    5       556     del      del         ...
1          5    anil    5       556     del      del         ...
1          7    anil    5       556     del      del         ...
1          8    anil    5       556    del       del         ...
1          2    anil    5       556     del      del         ...
2          15   anil    5       556     del      del         ...
2          16   anil    5       556     del      del         ...
3          19   anil    5       556    del       del         ...
3          22   anil    5       556     del      del         ...
3          24   anil    5       556     del      del         ...
3          27   anil    5       556     del      del         ...
3          29   anil    5       556    del       del         ...
0          31   anil    5       556     del      del         ...
0          32   anil    5       556     del      del         ...
0          33   anil    5       556     del      del         ...
0          34   anil    5       556    del       del         ...
0          35   anil    5       556    del       del         ...
0          36   anil    5       556    del       del         ...
0          37   anil    5       556    del       del         ...

currently Data is Coming i have write query to fetch such like this for each group we have to fetch only 4 event if less than 4 event then only we have to fetch only 2 record

desire output i want

GroupID   eventID    EMPNAME    EMPAGE  SALARY  ADDRESS empaddress ist
    1          2    anil    5    556     del     del      ...      1
    1          5    anil    5    556     del     del      ...      1 
    1          7    anil    5    556     del     del      ...      1  
    1          2    anil    5    556     del     del      ...      1 
    2          15   anil    5    556     del     del      ...      0 
    2          16   anil    5    556     del     del      ...      0 
    3          19   anil    5    556     del     del      ...      1
    3          22   anil    5    556     del     del      ...      1 
    3          24   anil    5    556     del     del      ...      1
    3          29   anil    5    556     del     del      ...      1
    0          31   anil    5    556     del     del      ...      1 
    0          32   anil    5    556     del     del      ...      1 
    0          33   anil    5    556     del     del      ...      1 
    0          34   anil    5    556     del     del      ...      1

i want record like this where ist if particular group has more than 4 record then it should be 1 please suggest me how write query for this so that i can get output like this.

@EhsanT i am confuesd how to write query for getting like that output — Mhanaz Syed, Nov 19 '16 at 05:26
select GroupID ,eventID ,EMPNAME ,EMPAGE ,SALARY from employee where eventID limit 4 am trying but this will not work — Mhanaz Syed, Nov 19 '16 at 05:28
Please read the answers to [this question](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results). The OP of this question had same situation and there are many different ways to achieve this. Give them a try and if you needed more help on how to adopt them on your table, please let me know. then I can help you on that — EhsanT, Nov 19 '16 at 05:58
Thanx its working but how i will get ist also how i will apply this query for this — Mhanaz Syed, Nov 19 '16 at 06:08
The reason I gave that link to you was that you know there are many different ways to achieve this. using `union` will not be the solution for you. because I think you may have different dynamic values for `GroupID`. but if you have only values between 0 to 3 for `GroupID` then you can use `union` if not, then you have to use self-join solution — EhsanT, Nov 19 '16 at 06:11
yes you are correct i have to get group id also dynamically please suggest me i am not much think know about mysql i just started work on mysql — Mhanaz Syed, Nov 19 '16 at 06:14
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128506/discussion-between-mhanaz-syed-and-ehsant). — Mhanaz Syed, Nov 19 '16 at 06:15

score 2 · Accepted Answer · edited May 23 '17 at 12:32

2

OK as I mentioned in my comment, it's better first to read the solutions provided in this question

But the query which will suite your need would be something like this:

SELECT a.*, 
  IF((SELECT COUNT(*) FROM eventlist AS c
      WHERE c.GroupID = a.GroupID) > 4, 1, 0) AS ist
FROM eventlist AS a
WHERE 
  (SELECT COUNT(*) FROM eventlist AS b 
  WHERE b.GroupID = a.GroupID and b.eventID >= a.eventID) <= 4
ORDER BY a.GroupID, a.eventID

edited May 23 '17 at 12:32

Community

1
1

answered Nov 19 '16 at 06:15

EhsanT

2,077
3
27
31

great thanx its work but how i will display is thing please suggest me – Mhanaz Syed Nov 19 '16 at 06:22
Oh, I did not see that part of the question. let me change the answer and add it to the query in a few minutes – EhsanT Nov 19 '16 at 06:24
4, 1, 0 why u have given this – Mhanaz Syed Nov 19 '16 at 06:33
Please read `if statement` documentation. 4 is not related to `if`, 4 is the number of records you want to show. 1 is the value which `if` will return in case of true, 0 is value which `if` will return in case of false. so if you wanted to show _apple_ and _orange_ in the _ist_ field instead of 0 and 1, it would be like this: `IF((query) > 4, 'apple', 'orange') AS ist` – EhsanT Nov 19 '16 at 06:36
Thanx great superb – Mhanaz Syed Nov 19 '16 at 06:41
can i ask u from where i need some more help if u are free? – Mhanaz Syed Nov 19 '16 at 06:44
You always can post your questions on this. But first please do your research, and try you best and if you were not able to solve your problem, then post it here. – EhsanT Nov 19 '16 at 06:46
now here we have to apply filter also can u suggest me how i i can apply this – Mhanaz Syed Nov 19 '16 at 06:50
depending on what type of filter you have in mind, you have to use `where clause` in the query which the filter should apply. As I said before, you can first try to do it yourself and if you have problems or errors in you query, then you can post a new question and ask for guidance and help... – EhsanT Nov 19 '16 at 06:54
dynamic where clause we have to apply filter any field wise – Mhanaz Syed Nov 19 '16 at 07:12
OK then, please post a new question based on your new question. It's better that way. Because summing all your questions and posting them all in one question may be a bad idea. it's better to post your questions one by one in case you need a quick answer. – EhsanT Nov 19 '16 at 07:15
when i try to excute this query SELECT a.*, IF((SELECT COUNT(*) FROM eventlist AS c WHERE c.GroupID = a.GroupID) > 4, 1, 0) AS ist FROM eventlist AS a WHERE (SELECT COUNT(*) FROM eventlist AS b WHERE b.GroupID = a.GroupID and b.eventID >= a.eventID and a.eventID=1 ) <= 4 ORDER BY a.GroupID, a.eventID it return all record – Mhanaz Syed Nov 19 '16 at 07:47
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128523/discussion-between-mhanaz-syed-and-ehsant). – Mhanaz Syed Nov 19 '16 at 10:18

how to fetch data top 4 record in mysql

1 Answers1