i have table like this

id tanggal airline h_jual
1 2008-08-03 2 458888
2 2008-08-05 2 500000
3 2008-08-05 1 500000
4 2008-08-05 3 709999
5 2008-05-13 1 500000
6 2008-07-13 3 709999
7 2008-06-13 3 500000
8 2008-01-13 1 458888
9 2008-01-13 1 500000
10 2008-02-13 3 458888
11 2008-03-13 3 458888
12 2008-04-13 2 458888
13 2008-09-14 4 458888
14 2008-09-14 2 1200000
15 2008-09-14 4 1200000
16 2008-01-14 2 709999
17 2008-01-17 1 1200000
18 2008-01-17 2 1200000
19 2008-01-17 3 1200000
20 2008-01-17 4 1200000
21 2008-02-17 1 1200000

i wrote like this
Code:

SELECT distinct year(tanggal) as year, month(tanggal) as month, count(id) as jml_transaksi, airline as airline FROM `table` where year(tanggal)='2008' group by year(tanggal), month(tanggal), airline order by tanggal asc


i got:

year |month |jml_transaksi |airline
2008 1 3 1
2008 1 2 2
2008 1 1 3
2008 1 1 4
2008 2 2 3
2008 2 1 1
2008 2 1 2
2008 2 1 4
2008 3 2 3
2008 3 1 4
2008 3 1 1
2008 3 1 2
2008 4 2 2
2008 4 1 1
2008 4 2 3
2008 4 1 4
2008 5 2 1
2008 5 1 2
2008 5 1 3
2008 5 1 4
2008 6 2 3
2008 6 1 4
2008 6 1 1
2008 6 1 2
2008 7 2 3
2008 7 1 1
2008 7 1 2
2008 7 1 4
2008 8 3 2
2008 8 2 3

i want got max in count(id) out? like in month 1 2008 i got 3 for maximum number even for 1,2,3,4 airline, how to get this result? any idea?
i try this
Code:

SELECT distinct year(tanggal) as year, month(tanggal) as month, count(id) as jml_transaksi, airline as airline, max(count(id)) FROM `keuangan` where year(tanggal)='2008' group by year(tanggal), month(tanggal), airline order by tanggal asc

but error!

Thanks

I'm not sure but I think you can't use distinct and count with a group by on the right hand side of the statement.

Wow I think you are over-killing this one.

How about:

SELECT * FROM table ORDER BY date DESC LIMIT 1

Originally Posted by just_a_pod

Wow I think you are over-killing this one.

How about:

SELECT * FROM table ORDER BY date DESC LIMIT 1

yes, i think i'm over-killing for this one. but in here i want extract year and month from date. i want group every airline table have own result.

i think distinct can't use in here to, i just try any possibilities. but i still can get what i want.

i hope my explanation for this problem is clear enough.

anybody can helpme

Select Max(jml_transaksi) As TheValue From (
SELECT distinct year(tanggal) as year, month(tanggal) as month, count(id) as jml_transaksi, airline as airline FROM `table` where year(tanggal)='2008' group by year(tanggal), month(tanggal), airline order by tanggal asc
) As InnerQueryDefinition

SELECT MAX(field) AS value from(here's your select query) as defineVar;