Mhanaz Syed Mhanaz Syed - 13 days ago 11
MySQL Question

how to fetch data top 4 record in mysql

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.

Answer

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
Comments