-
filtering rows(help)!!
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 = '012345A' ) AND
( B >= '01/01/2001' ) AND
( C <= '01/31/2001' )
GROUP BY A,
B,
C,
D
any solutions?
TIA
-
filtering rows(help)!! (reply)
Chuck,
Your statment:
"What I am trying to do here is to display all with max(E).."
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 = '012345A' ) AND
( B >= '01/01/2001' ) AND
( C <= '01/31/2001' )
GROUP BY A,
B,
C,
D
any solutions?
TIA
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|