filtering specific rows problems(big problem)

[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 = '012345A' ) AND
( b >= '01/01/2001' ) AND
( c <= '01/31/2001' )
GROUP BY a,
b,
c,
d

any solutions?

TIA

[DaveSatz]

something does not sound right since if you are grouping as you are there
can be only 1 row per a,b,c,d

try:
SELECT a,b,c,d,max(e)
FROM <table>
GROUP BY a,
b,
c,
d
HAVING ( a = '012345A' ) AND
( b >= '01/01/2001' ) AND
( c <= '01/31/2001' )

--
HTH,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
(Please reply to group only - emails answered rarely)
-----------------------------------------------------------------

"Chuck" <eyeballs@bayoucity.net> wrote in message
news:3b2a11bf$1@news.devx.com...
>
> 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
>

[Chuck]

Dave,
that didn't work. It got the same result as the previous query I posted.
Do you think i need to do some extra steps here or is there a command or
condition that I am missing?

TIA

"DaveSatz" <davidsatz@yahoo.com> wrote:
>something does not sound right since if you are grouping as you are there
>can be only 1 row per a,b,c,d
>
>try:
>SELECT a,b,c,d,max(e)
>FROM <table>
> GROUP BY a,
> b,
> c,
> d
>HAVING ( a = '012345A' ) AND
> ( b >= '01/01/2001' ) AND
> ( c <= '01/31/2001' )
>
>--
>HTH,
>David Satz
>Principal Software Engineer
>Hyperion Solutions
>->Using SQL Server 7.0 SP3/6.5 SP5a/Cold Fusion 4.5.1 SP2/VSS
>(Please reply to group only - emails answered rarely)
>-----------------------------------------------------------------
>
>"Chuck" <eyeballs@bayoucity.net> wrote in message
>news:3b2a11bf$1@news.devx.com...
>>
>> 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
>>
>
>

[Rohit Wason]

Chuck,

Try this:

SELECT a,b,c,d,e
FROM <table>
WHERE e IN ( SELECT MAX(e)
FROM <table>
GROUP BY a,b,c,d
)

Cheers,
Rohit