filtering specific rows problems(big problem)


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: filtering specific rows problems(big problem)

  1. #1
    Chuck Guest

    filtering specific rows problems(big problem)


    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


  2. #2
    DaveSatz Guest

    Re: filtering specific rows problems(big problem)

    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
    >




  3. #3
    Chuck Guest

    Re: filtering specific rows problems(big problem)


    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
    >>

    >
    >



  4. #4
    Rohit Wason Guest

    Re: filtering specific rows problems(big problem)


    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center