Results 1 to 9 of 9

Thread: A sql question

  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: A sql question

    Col1 Col 2 Col3 Col4 Col5 Col6
    a b c x1 12/02/2011 144
    a b c x1 9/30/2011 207
    a b c y1 9/7/2011 230
    …………

    Col6 = current date – col5 (a date db field)
    But I want to only show the first row with the latest (max col5) and remove row 2 & 3
    The report needs to display the latest date col5 if col1 and col 2 have the same values. Col4 can have the same value or different value. Col1 to Col 4 are defined as string.
    Who to write “where” clause to accomplish this ?
    Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dbforums2012 View Post
    The report needs to display the latest date col5 if col1 and col 2 have the same values.
    Code:
    SELECT col1
         , col2
         , MAX(col5) AS latest
      FROM daTable
    GROUP
        BY col1
         , col2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by dbforums2012 View Post
    Col1 Col 2 Col3 Col4 Col5 Col6
    a b c x1 12/02/2011 144
    a b c x1 9/30/2011 207
    a b c y1 9/7/2011 230
    …………

    Col6 = current date – col5 (a date db field)
    But I want to only show the first row with the latest (max col5) and remove row 2 & 3
    The report needs to display the latest date col5 if col1 and col 2 have the same values. Col4 can have the same value or different value. Col1 to Col 4 are defined as string.
    Who to write “where” clause to accomplish this ?
    Thanks!
    I think so:
    max(Col1) = min(Col1) and max(Col2) = min(Col2)...

    Lenny

  4. #4
    Join Date
    Apr 2012
    Posts
    3

    A sql question

    I tried both suggestions, but they don't get result I want.

    i.e.

    Let's remove col6
    Col1 Col 2 Col3 Col4 Col5
    a b c x1 9/30/2011
    a b c y1 9/7/2011

    Group works if x1 = y1, but it doesn't work if x1 <> y1.
    Thanks for your reply!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's all still too vague

    can you give actual values for your sample data?

    also, just a wee bit more data, so that we can see what you're actually trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want col4 to be the value correponding to MAX(col5),
    try this

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      sample_data
    (col1 , col2 , col3 , col4 , col5) AS (
    VALUES
      ( 'a' , 'b' , 'c' , 'y1' , DATE('12/02/2011') )
    , ( 'a' , 'b' , 'c' , 'z1' , DATE('09/30/2011') )
    , ( 'a' , 'b' , 'c' , 'x1' , DATE('09/07/2011') )
    )
    SELECT col1 , col2 , col3 , col4 , col5
     FROM (SELECT t.*
                , ROW_NUMBER()
                     OVER( PARTITION BY col1 , col2 , col3
                               ORDER BY col5 DESC ) AS r_num
            FROM  sample_data t
          )
     WHERE r_num = 1
    ;
    ------------------------------------------------------------------------------
    
    COL1 COL2 COL3 COL4 COL5      
    ---- ---- ---- ---- ----------
    a    b    c    y1   2011-12-02
    
      1 record(s) selected.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by r937 View Post
    that's all still too vague

    can you give actual values for your sample data?

    also, just a wee bit more data, so that we can see what you're actually trying to do
    I agree with r937.
    Too little data may lead to incomplete solutions.

    For example, the following query retutned same result as my last query.
    But, this query might be not the required one.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
      sample_data
    (col1 , col2 , col3 , col4 , col5) AS (
    VALUES
      ( 'a' , 'b' , 'c' , 'y1' , DATE('12/02/2011') )
    , ( 'a' , 'b' , 'c' , 'z1' , DATE('09/30/2011') )
    , ( 'a' , 'b' , 'c' , 'x1' , DATE('09/07/2011') )
    )
    SELECT t.*
     FROM  sample_data t
     ORDER BY
           col5 DESC
     FETCH FIRST 1 ROW ONLY
    ;
    ------------------------------------------------------------------------------
    
    COL1 COL2 COL3 COL4 COL5      
    ---- ---- ---- ---- ----------
    a    b    c    y1   2011-12-02
    
      1 record(s) selected.

  8. #8
    Join Date
    Apr 2012
    Posts
    3

    A sql question

    Thanks so much for all your reply.
    It helps.

    One of solutions is as follow:

    select

    col1, col2, col3, col4, col5, col6

    from t1, t2....

    where cond1 and cond2..
    and
    col5 in
    (select max(col5a) from .... (having the same join as above) where col1=col1a and col2 = col2a)

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Yes! It must be a right answer, too.

    But, performance of the query using ROW_NUMBER OLAP specification must be better than the query using IN (subquery).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •