select the most current record


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: select the most current record

  1. #1
    Join Date
    Jul 2012
    Posts
    3

    select the most current record

    I have searched the web for the answer to my question and tried many queries, but none have returned the results I need. I am hoping someone can help me out. I have a table with mutiple records for each resno. I need to return just one record per resno and that record needs to be the one with the most current date. Here is a sampling of my actual table:

    date cono resno artyp_type
    2/23/2006 1 814 4
    12/6/1995 1 814 0
    1/2/1900 2 990 3
    1/8/2101 2 990 2
    4/20/1994 2 990 1
    4/20/1994 5 990 0
    8/20/1998 5 990 4
    1/1/2011 5 987 3
    1/3/2011 5 987 2
    5/23/2012 5 987 1
    6/19/2012 8 990 0
    2/2/2012 8 1213 4
    11/18/2011 4 1402 3
    9/26/2011 8 1213 2
    9/25/2010 3 775 1
    4/20/1994 3 778 0
    8/20/1998 1 864 4
    1/1/2011 2 864 3
    1/3/2011 1 1237 2
    5/23/2012 1 1237 1

    The result I need to return would be:

    date cono resno artyp_type
    2/23/2006 1 814 4
    8/20/1998 1 864 4
    5/23/2012 1 1237 1
    1/1/2011 2 864 3
    1/8/2101 2 990 2
    9/25/2010 3 775 1
    4/20/1994 3 778 0
    11/18/2011 4 1402 3
    5/23/2012 5 987 1
    6/19/2012 8 990 0
    2/2/2012 8 1213 4

    I am working with a very large table. Any help is welcome. Thanks!

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Have you tried something like
    Code:
    SELECT resno, MAX([date]), cono, artyp_type 
    FROM tablename
    GROUP BY resno, cono, artyp_type
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  3. #3
    Join Date
    Jul 2012
    Posts
    3

    select the most current record

    Yes I have. It returns more than one record per resno. That seems to be the common issue with all the code I have tried. It filters most of the dates, but leaves more than just the most current date. You know, I just noticed that every one of those columns are keys in this table. Is that the problem? I just didn't notice that before. If so, how would I overcome that? Thanks.

  4. #4
    Join Date
    Jul 2012
    Posts
    3

    still cannot select most current date

    SELECT resno, MAX([date]), cono, artyp_type
    FROM tablename
    GROUP BY resno, cono, artyp_type

    I tried using this code, which I have tried before, but but it did not work. I have also tried the TOP function. Each time it returned a resno for each artyp_type. I only need it to return the most current date for each resno regardless of artyp_type. I believe one of the problems is that all of the columns I need to list are primary keys - cono, resno, date, artyp_type - all of them. Is this the problem? If so, does anyone know how to get around that? Any help is very welcome!!!

Similar Threads

  1. Replies: 3
    Last Post: 04-04-2003, 09:11 AM
  2. NullPointerException when reading text file
    By Andrew McLellan in forum Java
    Replies: 3
    Last Post: 05-09-2001, 05:34 PM
  3. Get 10 months forward with select statment
    By Nirit Touboul in forum Database
    Replies: 7
    Last Post: 02-25-2001, 11:34 AM
  4. Select most recent record
    By Rick in forum Database
    Replies: 4
    Last Post: 01-29-2001, 06:21 PM
  5. Select Record
    By woody in forum VB Classic
    Replies: 5
    Last Post: 04-29-2000, 01:36 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center