SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Thread: max date

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

    max date

    I have 2 tables


    Code:
    id|name
    -+----------
    1|st1
    2|an2
    1|st1
    2|an2
    --------------
    
    id|date
    -+----------
    1|2002
    2|2003
    1|2010
    2|2011
    --------------
    i want to retrive max date with name like this
    Code:
    name|date
    ---+----------
    st1 |2010
    an2|2011
    --------------
    any help??
    Last edited by ScallioXTX; May 26, 2013 at 11:05. Reason: Wrapped table examples in [code][/code]

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT table1.name
         , MAX(table2.date) AS maxdate
      FROM table1
    INNER
      JOIN table2
        ON table2.id = table1.id
    GROUP
        BY table1.name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I've been given the wrong example
    the correct example

    id|name
    -+----------
    1|st1
    2|st2
    3|an2
    4|an4
    --------------

    id|date|group
    -+----------
    1|2002|St
    2|2003|St
    3|2010|an
    4|2011|an
    --------------


    retrive

    id|date|group
    -+----------
    st2|2003
    an4|2011
    --------------

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by altarek View Post
    Sorry I've been given the wrong example
    what you just posted makes no sense

    please explain in words how you think the results can be achieved

    what happened to st1 and an2?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    table1 (book issues)

    table2(book name and publishdate of issue)


    I want to Retrieve the latest issue and its pubdate or ( all of the issues for the last(max) year)

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can you please give the exact table specifications

    for example, if this is mysql, plese do a SHOW CREATE TABLE for each table
    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)
    CREATE TABLE IF NOT EXISTS `articles` (
    `articleid` int(11) NOT NULL auto_increment,
    `title` mediumtext,
    `booktitle` mediumtext,
    `pubdate` smallint(6) default NULL,
    PRIMARY KEY (`articleid`))
    ) ;


    CREATE TABLE IF NOT EXISTS `issues` (
    `issueid` int(11) NOT NULL auto_increment,
    `articleid` int(11) NOT NULL default '0',
    `issue` varchar(255) default NULL,

    PRIMARY KEY (`issueid`),
    ) ;


    i want to retrive last issue of book(issue) and pubdate

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, thanks for that

    i am still having trouble understanding what it represents

    so each article can have multiple issues?

    also, what does "last issue of book(issue)" mean? what's in that VARCHAR(255) column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no each book have issues

    book1(issue column)(book1,v1 -- book1,v2 -- book1,v3 | book2,v1 -- book2,v2 -- book2,v3) and each issue have articles

    and booktitle at table1 is name of the book

    so i need retrieve (book1,v3 --- book2,v3) and thier dates(pubdate)

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i am sorry, i cannot continue trying to figure out your tables

    nothing really makes sense to me, sorry
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •