How can i get the best record from a table in mysql?
In my database has two tables to store my campaigns details.
campaigns ( table1 )
| id | campaign | status |
|---|---|---|
| 1 | home_c | 1 |
| 2 | flower_sunday | 1 |
| 3 | flower_friday | 0 |
| 4 | lanka_flowers | 1 |
| 5 | lanka_top_flowers | 1 |
campaign_records ( table2 )
| id | campaign_id | user_id | date | status |
|---|---|---|---|---|
| 1 | 1 | 1 | 2017/11/16 | 1 |
| 2 | 1 | 2 | 2017/11/16 | 1 |
| 3 | 1 | 3 | 2017/11/16 | 1 |
| 4 | 1 | 5 | 2017/11/16 | 1 |
| 5 | 2 | 1 | 2017/11/17 | 1 |
| 6 | 2 | 3 | 2017/11/17 | 0 |
| 7 | 3 | 1 | 2017/11/17 | 0 |
| 8 | 3 | 4 | 2017/11/18 | 0 |
| 9 | 3 | 5 | 2017/11/18 | 0 |
| 10 | 4 | 1 | 2017/11/18 | 1 |
| 11 | 4 | 2 | 2017/11/18 | 1 |
| 12 | 1 | 1 | 2017/11/19 | 1 |
| 13 | 1 | 2 | 2017/11/19 | 1 |
| 14 | 1 | 4 | 2017/11/19 | 1 |
| 15 | 1 | 5 | 2017/11/19 | 1 |
| 16 | 2 | 1 | 2017/11/20 | 1 |
- 1 - ok
- 0 - trashed
I want to get the best campaign which one was the most participating users and well as ignore trashing records or campaigns. How can i write a code to get a result like this?
Your Answer
1 Answer
You should have to filter trash records of campaign_records and campaigns tables in very first time by using status column. Then order by id column counting with descending order. now you can get campaigns with max to minimum users participation order. finally you can group by campaign_id. Then you can get best campaign
Try this mysql statement.
SELECT campaign_id FROM campaign_records WHERE campaign_id IN ( SELECT id FROM campaigns WHERE status != '0' ) AND status != '0' GROUP BY campaign_id ORDER BY COUNT(id) DESC LIMIT 1;Try this code to get campaign detail.
SELECT * FROM campaigns as c JOIN campaign_records as r ON c.id = r.campaign_id WHERE r.status != '0' AND c.status != '0' GROUP BY r.campaign_id ORDER BY COUNT(r.id) DESC LIMIT 1;How can you get the best campaigner ?
SELECT user_id,COUNT(id) as campaigns FROM campaign_records WHERE campaign_id IN ( SELECT id FROM campaigns WHERE status != '0' ) AND status != '0' GROUP BY user_id ORDER BY COUNT(id) DESC LIMIT 1;
JOIN OVER 100,000 CODRATE USERS
Who get fresh code scripts from codrate!
Will send your codrate site configuration email in your mail account.
What is the codrate ?
codrate.com is a standard, fast cross browsing and highly versatile site. It is useful for many large number of Program Development Industries. So you can get support form Codrators , who are the codrate's joiners around world to help your program developments, You can answer other codrator's questions. Communicate with them. Share your knowledge with them. Do you have an interest in programming, So publish your articles about programming. It will help to maintain your professional co-profile. Actually codrate.com is not such as a regular web site. It will be gave new experience, best narrow cross-browser view, reduce processing time to receive browsing request, it's mean do not wasting your time to browsing codrate's web pages because it has been upgrade always modern coding ways. So, what do you waiting for ?. Try your own.