SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help on sql statement

    I'm currently using this sql statement to return a set of records but I'd like to split it into two different sql statements. tbl35.col3 is a date field. I'd like everything in the query to stay the same except in query1 I'd like only the record with the most recent date to be returned. And then in a second sql statement(query2) I'd like to return all the records except for the record with the most recent date.

    If anyone can help out a beginner it would be greatly appreciated. Thanks.

    SELECT
    [tbl18].[col2] AS col2b,
    [tbl18].[col23] AS col23b,
    [tbl35].[col1],
    [tbl35].[col2],
    [tbl35].[col3],
    [tbl35].[col13],
    [tbl35].[col17]
    FROM tbl35 INNER JOIN tbl18 ON [tbl35].[col2]=[tbl18].[col1]
    WHERE ([tbl35].[col13]=recordid1) And ([tbl35].[col17]=0)
    ORDER BY [tbl35].[col3] DESC;

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    which version of which database, please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Postgresql 7.4

    Quote Originally Posted by r937
    which version of which database, please?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    query 1 --
    Code:
    select tbl18.col2 AS col2b
         , tbl18.col23 AS col23b
         , tbl35.col1
         , tbl35.col2
         , tbl35.col3
         , tbl35.col13
         , tbl35.col17
      from tbl35 
    inner 
      join tbl18 
        on tbl35.col2
         = tbl18.col1
     where tbl35.col13 = recordid1 
       and tbl35.col17 = 0
       and tbl35.col3
         = ( select max(col3)
               from tbl35
           )
    order 
        by tbl35.col3 desc
    for query 2, change the equal sign in front of the subquery to less than
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's working perfectly. Thanks for the help!!!


Bookmarks

Posting Permissions

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