I have a table with three cols:
Firm No, Date, and Ret.
Code:
Firm No Date Ret
1 20040101 .01
1 20040102 -.01
1 20040103 .01
1 20040104 .02
2 20040102 .01
2 20040103 -.01
3 20040101 -.02
3 20040102 .05
3 20040103 -.06
3 20040104 .07
4 20040101 -.08
4 20040102 .12
4 20040103 .02
I'd like to select all for the returns for the firms which have observations for each day.
There are a number of ways to get the numbr of days. My fav so far is
SELECT COUNT(DISTINCT date) from myTable;
Which will return the number of days.
So far I have't found a way to get it to let me reutns the firmno, date and rets for those which have all obs
(In this example the return woudl ideally be)
Code:
Firm No Date Ret
1 20040101 .01
1 20040102 -.01
1 20040103 .01
1 20040104 .02
3 20040101 -.02
3 20040102 .05
3 20040103 -.06
3 20040104 .07
I've tried code like
Code:
SELECT firmno,date,ret,COUNT(*) as numObs
FROM myTable
GROUP BY firmno
HAVING numObs=MAX(numObs)
with no luck (Syntax error).
Any help would be greatly appreciated. Oh, and SQL is MySQL 4.0.18.
Thanks,
Kevin
dBforums
>
Help with tricky SELECT
03-15-04, 12:16
Linear Mode
