SitePoint Sponsor

User Tag List

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

    noob needs sql help

    I'm using the following query to find a list of records by the latest date per category. So if there were four categories I would need a list of four categories with their latest corresponding dates. The category field is col5 and the date field is col6. I've got the date sorted our but not the category. It currently only returns the latest date no matter what cateogry and only returns 1 record. I'm not sure how to get it to return the dates for each unique category.
    DB=postgresql 7.4 Any help would be appreciated!

    SELECT
    tbl42.col1,
    tbl42.col3,
    tbl42.col4,
    tbl42.col5,
    tbl42.col6,
    tbl42.col7,
    tbl42.col8,
    tbl42.col10,
    tbl42.col11,
    tbl42.col12
    FROM tbl42
    WHERE (((tbl42.col3)=recordid7) AND ((tbl42.col12)=0)) and tbl42.col6 = (select max(col6) from tbl42)
    ORDER BY tbl42.col6 DESC

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    FROM tbl42 as foo
    WHERE col3 = recordid7
      AND col12 = 0
      and col6 = 
          ( 
          select max(col6) 
            from tbl42
           where col5 = foo.col5
          )
    you may also want to remove the (unnecessary) table prefixes on all the columns in the SELECT and ORDER BY lists, or else change them to foo.
    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)
    Thanks for the help! That was messing me up for a while.

    Quote Originally Posted by r937
    Code:
    FROM tbl42 as foo
    WHERE col3 = recordid7
      AND col12 = 0
      and col6 = 
          ( 
          select max(col6) 
            from tbl42
           where col5 = foo.col5
          )
    you may also want to remove the (unnecessary) table prefixes on all the columns in the SELECT and ORDER BY lists, or else change them to foo.


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
  •