SitePoint Sponsor

User Tag List

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

    need help with sub query

    I'm using postgresql 7.4

    I'm trying to return a listing of records as follows:

    SELECT
    tbl43.col1,
    tbl43.col3,
    tbl43.col5,
    tbl39.col2,
    tbl43.col6,
    tbl43.col8,
    tbl43.col12
    FROM tbl43 INNER JOIN tbl39 ON tbl43.col5 = tbl39.col1
    WHERE (((tbl43.col3)=11) AND ((tbl43.col12)=0));

    But, I need to tweak it so that only the latest date(tbl43.col6), is returned for each unique category(tbl43.col5). Does anyone know how to do this. I'm thinking I need a subquery on tbl43.col6 but I'm not sure how to pinpoint only the max date per category and not the max date for all records.

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2003
    Location
    Los Angeles
    Posts
    199
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's hard to decipher these tbl43, tbl39, col1..N... but I'll take a stab at what I think you are trying to do. If what I think you are doing the tbl43 might for instance be a table of articles and tbl39 is a table of categories. Each article is referencing a category though the relation tbl43.col5 --> tbl39.col1. If this is true and you are only interested in selecting the most recent article based on the article category then you'd just need to add this to your query:

    ORDER BY tbl43.col6 DESC LIMIT 1

    Assuming tlb43.col6 is the creation date.

  3. #3
    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)
    SELECT
    tbl43X.col1,
    tbl43X.col3,
    tbl43X.col5,
    tbl39.col2,
    tbl43X.col6,
    tbl43X.col8,
    tbl43X.col12
    FROM tbl43 as tbl43X INNER JOIN tbl39 ON tbl43X.col5 = tbl39.col1
    WHERE tbl43X.col3=11
    AND tbl43X.col12=0
    and tbl43X.col6 =
    (select max(tbl43.col6) from tbl43 where
    where col5 = tbl43X.col5)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried out the query r937 but I'm still having some problems. I believe the subquery is only limiting to 1 date instead of each max date per category. I could be wrong though. I'll try to put some fake data here that I have and the output I'm getting and the ouput I'm looking for. Maybe it'll help out a little more with the description of the problem.

    tbl39
    col1 = autonumber
    col2 = description(text)
    example data:
    2 Truck
    1 Car
    7 Boat
    4 Motorcycle
    6 SUV

    tbl43
    col1=autonumber
    col3=salesrep id(integer)
    col5=contract type(integer)
    col6=commission effective date(date)
    col8=commission rate(numeric)
    col12=deleted?(true/false)
    example data:
    col1 col3 col5 col6 col8 col12
    3 5 7 08/12/2004 12.9 1
    19 11 7 12/06/2004 13. 0
    17 11 7 12/06/2004 10. 1
    21 11 4 12/06/2004 99. 0
    22 11 6 12/06/2004 3. 0
    20 11 4 12/18/2004 13. 0
    15 2 4 12/25/2004 12.8 0
    1 5 7 08/09/2004 10. 1
    16 2 7 12/01/2004 10. 0
    4 5 1 08/13/2004 133. 0
    7 5 4 08/19/2004 10. 0
    8 5 1 08/09/2004 123. 0


    I tried out the suggested qry(it's missing one of the unique categories) but I'm only getting the following output:

    col1 col3 col5 col2 col6 col8 col12
    22 11 6 SUV 12/6/2004 3 0
    19 11 7 BOAT 12/6/2004 13 0

    What I need to be getting is:
    col1 col3 col5 col2 col6 col8 col12
    22 11 6 SUV 12/6/2004 3 0
    19 11 7 BOAT 12/6/2004 13 0
    20 11 4 CYCLE 12/18/2004 13 0

    Is it using only one max date, instead of using one max date per category?

    I noticed the tabbing all disappears when you view this post but if you hit the reply with quote button, the formatting reappears.

  5. #5
    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)
    the subquery is a correlated subquery and returns the max col6 for each col5

    however, i forgot to put the filtering conditions into the subquery as well
    Code:
    select tbl43X.col1
         , tbl43X.col3
         , tbl43X.col5
         , tbl39.col2
         , tbl43X.col6
         , tbl43X.col8
         , tbl43X.col12
      from tbl43 as tbl43X 
    inner 
      join tbl39 
        on tbl43X.col5 
         = tbl39.col1
     where tbl43X.col3 = 11 
       and tbl43X.col12 = 0
       and tbl43X.col6 = 
         ( select max(tbl43.col6) 
             from tbl43 
            where col5 = tbl43X.col5
              and col3 = 11 
              and col12 = 0 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    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, I think it's working now.


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
  •