Another way, if you're using MySql 4 and up (since anything less does not
support sub selects)
could be to try this:
SELECT Date, COUNT(Date) AS Cnt
FROM MyTable
GROUP BY Date
HAVING COUNT(Date) >= (SELECT COUNT(B.TypeID) AS Cnt2
FROM MyTable
GROUP BY TypeID
ORDER BY Cnt2 DESC
LIMIT 0,1)
I haven't tried this query out since I'm not using mysql, but a slight
variation of it does work in MSSql Server
Ciao
---
Rob
**************************
Rob Cherry
mailto:[EMAIL PROTECTED]
+27 21 447 7440
Jam Warehouse RSA
Smart Business Innovation
http://www.jamwarehouse.com
**************************
-----Original Message-----
From: Hu Qinan [mailto:[EMAIL PROTECTED]
Sent: Monday, March 24, 2003 2:08 PM
To: [EMAIL PROTECTED]
Subject: group by quesion?
How to write this sql?
I have a table:
Date | Low High
------------------------------
...
28 | 1 7
-------------------------
29 | 1 7
29 | 2 3
29 | 4 10
----------------------------
30 | 1 7
30 | 2 3
30 | 4 10
-----------------------------
31 | 0 4
31 | 6 8
I try to extract the "Date" which has the most frequently occured
combination of (Low, High).
For example, Date- 29, 30 should be extracted since ((1, 7), (2, 3), (4,
10)) occured more often than (1, 7) and ((0, 4), (6, 8)).
I can't use
GROUP BY low, high
since then (1, 7) will be identified as the most frequently occured Low and
High.
If it is not clear, please ask me.
All information contained in this email is confidential and may be used by
the intended recipient only.
All information contained in this email is confidential and may be used by the
intended recipient only.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
