SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    retrive last date

    I have table p_id
    I want to have the last date from duplicte cid

    at this table i want to have dates (2010-5-6,2010-7-5)for duplicted cid(1,3)
    and year(2010-3-2) for cid(2)
    +--+----------+
    |cid|did |
    +--+---------+
    | 1 | 2010-5-6|
    | 2 | 2010-3-2|
    | 3 | 2010-7-5|
    | 3 | 2009-5-2|
    | 1 | 2009-5-4|
    +--+----------+

    any help?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    So you want the MAX date for each cid?

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes ,or the last row inserted for each cid

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If the last inserted row for that id has the biggest date, you better stick with the MAX(did)

    Check out the link I posted, and try to figure out how it works. If you can't get it to work, post your query here and we'll help you out.

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT con.name,con.con_id,con.constat,MAX(part.pardate) AS mxyear FROM part,con WHERE part.con_id=con.con_id  GROUP BY part.con_id

    this query retrieve the max date and the others from another row

    if there is another way to retrieve last row for the same id

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think this is what you're looking for...
    Code:
    SELECT con.name
         , con.con_id
         , con.constat
         , part.pardate
         , part.other_columns
      FROM con
    INNER
      JOIN ( SELECT con_id
                  , MAX(pardate) AS max_pardate
               FROM part
             GROUP
                 BY con_id ) AS m
        ON m.con_id = con.con_id
    INNER
      JOIN part
        ON part.con_id = m.con_id 
       AND part.pardate = m.max_pardate
    it's the other_columns that makes this a challenging problem

    this is usually called the "row having the groupwise max" -- in other words, your con_id is the grouping column, MAX(pardate) determines the latest row, and then you must join back to the part table to get the other columns

    see also this recent thread which had the same problem

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Thanks this query works good


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
  •