filtering rows(help)!!

[Chuck]

to any who can help:

Here are some rows in a table with their lettered columns:
A B C D E

012345A e 2001-01-01 00:00:00.000 2001-01-02 00:00:00.000 0
012345A e 2001-01-02 00:00:00.000 2001-01-03 00:00:00.000 0
012345A e 2001-01-03 00:00:00.000 2001-01-04 00:00:00.000 0
012345A e 2001-01-04 00:00:00.000 2001-01-05 00:00:00.000 0
012345A e 2001-01-19 00:00:00.000 2001-01-20 00:00:00.000 0
012345A e 2001-01-20 00:00:00.000 2001-01-21 00:00:00.000 0
012345A e 2001-01-24 00:00:00.000 2001-01-25 00:00:00.000 0
012345A e 2001-01-25 00:00:00.000 2001-01-26 00:00:00.000 0
012345A e 2001-01-25 00:00:00.000 2001-01-26 00:00:00.000 1
012345A e 2001-01-26 00:00:00.000 2001-01-27 00:00:00.000 0

if you notice on the 8 and 9th rows the only difference between them is in
the E column(0 and 1). What I am trying to do here is to display all with
max(E). So in the above example, I should display rows 1-7,9,10 (8th row
will not display because the 9th row has 1 in the E column). this is the
query I have been using on SQL Server 2000 but I keep on displaying all the
rows:

SELECT A,B,C,D,max(E)
FROM <table>
WHERE ( A = &#39;012345A&#39; ) AND
( B >= &#39;01/01/2001&#39; ) AND
( C <= &#39;01/31/2001&#39; )
GROUP BY A,
B,
C,
D

any solutions?

TIA

[Kurt]

Chuck,

Your statment:
&#34;What I am trying to do here is to display all with max(E)..&#34;


Max(E) is 1. And there is only one row with E=1. If you are expecting to
display all rows with Max(E) you should only get one row. What am I missing here? Can you explain?


------------
Chuck at 6/15/01 10:44:57 AM


to any who can help:

Here are some rows in a table with their lettered columns:
A B C D E

012345A e 2001-01-01 00:00:00.000 2001-01-02 00:00:00.000 0
012345A e 2001-01-02 00:00:00.000 2001-01-03 00:00:00.000 0
012345A e 2001-01-03 00:00:00.000 2001-01-04 00:00:00.000 0
012345A e 2001-01-04 00:00:00.000 2001-01-05 00:00:00.000 0
012345A e 2001-01-19 00:00:00.000 2001-01-20 00:00:00.000 0
012345A e 2001-01-20 00:00:00.000 2001-01-21 00:00:00.000 0
012345A e 2001-01-24 00:00:00.000 2001-01-25 00:00:00.000 0
012345A e 2001-01-25 00:00:00.000 2001-01-26 00:00:00.000 0
012345A e 2001-01-25 00:00:00.000 2001-01-26 00:00:00.000 1
012345A e 2001-01-26 00:00:00.000 2001-01-27 00:00:00.000 0

if you notice on the 8 and 9th rows the only difference between them is in
the E column(0 and 1). What I am trying to do here is to display all with
max(E). So in the above example, I should display rows 1-7,9,10 (8th row
will not display because the 9th row has 1 in the E column). this is the
query I have been using on SQL Server 2000 but I keep on displaying all the
rows:

SELECT A,B,C,D,max(E)
FROM <table>
WHERE ( A = &#39;012345A&#39; ) AND
( B >= &#39;01/01/2001&#39; ) AND
( C <= &#39;01/31/2001&#39; )
GROUP BY A,
B,
C,
D

any solutions?

TIA