2

I Have a table like this:

Date   | Expedition Name | Invoice
___________________________________________
4/6/12 | Starsindo       | X001908
4/6/12 | Starsindo       | X001900
4/6/12 | De Ros indah    | X002008
4/6/12 | De Ros indah    | X002108
4/6/12 | De Ros indah    | X002208
4/6/12 | Starsindo       | X002308
4/6/12 | Starsindo       | X002408
4/6/12 | Starsindo       | X002508 
4/6/12 | Japex           | X002608

How to make a query with mysql to display the names of the three expeditions that most often appear in the same date?

I just created this query, but still confused:

Select  * From tb_exp_expor  Order By nama_exp_expor DESC
Tisho
  • 8,320
  • 6
  • 44
  • 52
MudMan23
  • 821
  • 3
  • 10
  • 17
  • what is nama_exp_expor in your query? – pratik garg Jul 17 '12 at 07:48
  • What are the results you expect of your query? For your example case I would expect all expedition names to be returned once, is that correct? – Josien Jul 17 '12 at 07:49
  • @praktik garg, nama_exp_expor is mean Expedition name, sory for my mistake. – MudMan23 Jul 17 '12 at 08:19
  • @Josien , I will make the diagram from the result of the query. and I hope the query result like this 1|Starsindo 2|De ROs indah 3|Japex, asc from the Maximal result until the Minimal Result – MudMan23 Jul 17 '12 at 08:23
  • And what should happen if several ExpeditionNames occur the same number of times? In your example, what should the query return when there is a fourth ExpeditionNames 'NewEx' that occurs 1 time like 'Japex'? – Josien Jul 17 '12 at 09:00

2 Answers2

7
 SELECT count(*) as counta,Expedition_name,Date 
    FROM tbl_exp_expor 
    group by Expedition_name,Date
    Order by counta DESC
    limit 3
sel
  • 4,982
  • 1
  • 16
  • 22
  • It Works, but the same Expedition name still not in one group, The result of query: SELECT count(*) as counta,Expedition_name,Date FROM tbl_exp_expor group by Expedition_name,Date Order by counta DESC limit 3 – MudMan23 Jul 17 '12 at 08:42
  • 1
    Can i have the sample data of those that is not working? Please refer to [SQL FIDDLE](http://sqlfiddle.com/#!2/7d634/2). The query is working for the data you attached. – sel Jul 17 '12 at 08:55
  • oke, thanks, it work., By the way, can you tell me the php function for acsess the spesific row, ex: 5 for Starsindo it will include for pie diagram. and 3 for Ros indah. Usually i write this $data=mysql_fetch_array($sql). And for acces data i write this $data[expedtion_name]. How to write only row 2 in PHP? – MudMan23 Jul 17 '12 at 09:39
  • @Septiyo: I think you'd best make a new question for that :-) – Josien Jul 17 '12 at 09:41
0

I think this question and answer (by danben) on StackOverflow may help you:
mysql: Using LIMIT within GROUP BY to get N results per group?

It contains a link to this article:
How to select the first/least/max row per group in SQL

Josien
  • 13,079
  • 5
  • 36
  • 53