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!
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.
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!!!
