#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    22
    Rep Power
    0

    Need help with a query


    i am not sure how to phrase this

    i want to pull all records from the DB where i end up with the most recent record posted for a particular case.

    for example, the table to be queried is 'notes'

    'notes' has 50,000 records, representing 1000 records. let's then say that is an average of 50 notes per case.

    each case has a unique case_id number. each record has a unique id number

    what i want to do is look at all 1000 unique cases, with their respective set of 50 records, find the data for the last record posted (to be exact, the specific contents of one specific field), and move to the next, until i have 1000 unique records returned with their most recent date_field posted showing.
  2. #2
  3. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Hmm, you jumped around with your terms a bit (and this probably should go in the MySQL forum) but I think you'd want something like this:

    (based on one of my DBs)
    SELECT t.subject, t.created, (SELECT p.created FROM post_01 p WHERE t.topic_id=p.topic_id ORDER BY created DESC LIMIT 1) AS last_entry FROM topic_01 t;

    Or tailored closer to your description:
    SELECT c.subject, (SELECT n.created FROM note n WHERE c.case_id=n.case_id ORDER BY n.created DESC LIMIT 1) AS last_note_timestamp FROM case c;

    Table/column names would obviously need changed.

    Comments on this post

    • requinix agrees : annnd moved
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    22
    Rep Power
    0
    Maybe an example will make it easier

    DATA looks like this (3 columns: id, case_id, date_notated); the name of the table is 'notes'

    id case_id date_notated
    1 25 06/01/2010
    2 25 06/21/2011
    3 25 09/03/2012
    4 25 05/01/2013
    5 26 05/02/2013
    6 26 05/04/2013
    7 26 05/05/2013
    8 26 05/07/2013


    Desired Result

    id case_id last_post
    4 25 05/01/2013
    8 26 05/07/2013

    as you can see i looked all all entries for case_id 25 and returned the last post; same for case_id 26


    how do i write a query to get the result?

    thanks!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by inosent1
    Desired Result

    id case_id last_post
    4 25 05/01/2013
    8 26 05/07/2013
    how important is it that you get the id? wouldn't case_id and last_post suffice?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by inosent1
    Maybe an example will make it easier

    DATA looks like this (3 columns: id, case_id, date_notated); the name of the table is 'notes'

    id case_id date_notated
    1 25 06/01/2010
    2 25 06/21/2011
    3 25 09/03/2012
    4 25 05/01/2013
    5 26 05/02/2013
    6 26 05/04/2013
    7 26 05/05/2013
    8 26 05/07/2013


    Desired Result

    id case_id last_post
    4 25 05/01/2013
    8 26 05/07/2013

    as you can see i looked all all entries for case_id 25 and returned the last post; same for case_id 26


    how do i write a query to get the result?

    thanks!
    If you only need to get a single column from your "notes" table (ie, the timestamp of when the most recent entry was added) then the query structure I provided before will work.

    If you need multiple columns from that table, well... that's tougher. I usually go into code logic at that point but there might be some JOIN trickery that'd work, too. But that's outside my comfort zone to advise on.
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    22
    Rep Power
    0
    all i need is the case_id and the most recent timestamp
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2009
    Posts
    22
    Rep Power
    0
    "SELECT c.subject, (SELECT n.created FROM note n WHERE c.case_id=n.case_id ORDER BY n.created DESC LIMIT 1) AS last_note_timestamp FROM case c;"

    my fields are:

    id, case_id, date_notated

    the table is:

    notes

    SELECT c.subject, (SELECT n.id FROM notes n WHERE c.case_id=n.case_id ORDER BY n.id DESC LIMIT 1) AS date_notated FROM case_id c;

    ???

    is that right?

    what is "c.subject"

    as for the rest of the query, is this what the finished product should look like?

    PHP Code:

    <table bgcolor="#ffffff" valign=top border=1 width=100% align="center" class="sortable" id="sample">
    <thead><tr>
    <th><font color="black">ID</th>
    <th><font color="black">Date</th>
    </tr></thead>

    <?
    query
    ="SELECT c.subject, (SELECT n.id FROM notes n WHERE c.case_id=n.case_id ORDER BY n.id DESC LIMIT 1) AS date_notated FROM case_id c";
    $result=mysql_query ($query);
    $num=mysql_num_rows ($result);

    $i=0;
    while (
    $i $num) {
    $case_id mysql_result($result,$i,"case_id");
    $date_notated=mysql_result($result,$i,"date_notated");

    ?>

    <td  valign=top><?php echo "$case_id"?> 
    <td  valign=top> <?php echo "$date_notated"?>

    </tr>

    <?php
    ++$i;

    ?>
    </table>
    (as coded above it is not working ...)


    Thanks again
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by inosent1
    all i need is the case_id and the most recent timestamp
    Code:
    SELECT case_id
         , MAX(date_notated) AS last_post
      FROM notes
    GROUP
        BY case_id
    vwalah
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by inosent1
    what is "c.subject"
    Just a filler for whatever data you want to pull from the "case" table. I guess you're only after c.case_id. I thought you might have a more descriptive "summary" field or something.

    Originally Posted by r937
    Code:
    SELECT case_id
         , MAX(date_notated) AS last_post
      FROM notes
    GROUP
        BY case_id
    vwalah
    Yep. If you don't need any info unique to the "case" table then it doesn't even need to be in the mix.

    But keep in mind that if it's possible to have a case without any notes, the case ID won't be in the list at all.

IMN logo majestic logo threadwatch logo seochat tools logo