Select rows from table sql

I have a table that I select data from using sql, and I try to select the most current record from that table for the primary key, MYCOL1.  In the case of the data shown below from my table, I need to select both rows, because MYCOL8 is distinct,.  What can I add to my select statement so that I can select both rows below, not just Row1, which is what my current query is yielding.  Thank you!

SELECT * FROM MYTABLE A
WHERE
AND MYCOL10 = (SELECT MAX(MYCOL10)
            FROM MYTABLE B
            WHERE A.MYCOL1 = B.MYCOL1
            AND A.MYCOL2 = B.MYCOL2);
                       
         
MYCOL1      MYCOL2      MYCOL3      MYCOL6      MYCOL7      MYCOL8      MYCOL9      MYCOL10
123456      1            abc            1.82a      TRUE      PDE-23      9.23B      10/01/2011
123456      1            abc            4.35a      TRUE      ABE-33      7.53C      10/01/2011
newtoperlpgmAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Try something like this:
select col1, col2, col3
from (
select col1,col2,col3, row_number() over(partition by mycol1, mycol8 order by mycol1,mycol8) myrn
)
where myrn=1;


Add columns to the row_number partition and order clause for your specific requirements.

If you cannot get it to work, please provide table structures, sample raw data and expected results.
0
 
newtoperlpgmAuthor Commented:
This appeared to have worked, it yielded both rows of data.
0
 
newtoperlpgmAuthor Commented:
I am going to double check the data to ensure it yields the other rows of data I need, that is, that it didn't affect the other rows of data I need to yield with exact date since I am trying to yield the most current distinct row of data from my table.  Thanks.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
>> need to yield with exact date since I am trying to yield the most current distinct row of data from my table.  Thanks.

Add the date to the ORDER BY statement DESCENDING.  Newest date will be at the top.
0
 
awking00Connect With a Mentor Commented:
slightwv, I think your query would also retrieve another row if there were a mycol1 value of 123456 and a mycol10 value earlier than 10/01/2011 with a different value in mycol8. If so, we can combine the row_number with a dense_rank -

select mycol1, mycol2, mycol3,  mycol6,  mycol7,  mycol8,  mycol9,  mycol10 from
(select t.*, row_number() over (partition by mycol1, mycol8 order by mycol10 desc) rn,
 dense_rank() over (partition by mycol1 order by mycol10 desc) rnk
 from tablea t)
where rn = 1 and rnk = 1;
0
 
slightwv (䄆 Netminder) Commented:
I didn't add all the 'necessary' columns.  Just paid particular attention to the ones referenced in the question.

Per the original post:  "that table for the primary key, MYCOL1", well the data shown shows a duplicate value for MYCOL1 so there is some interpretation that is necessary here.

If we had actual sample data to go along with the expected results I think you can get the results with a single ROW_NUMBER call without the need of another window function.

Then again, I could be wrong....
0
 
awking00Commented:
I wasn't really referring to all of the columns but the case where a record like the following could also exist:
MYCOL1      MYCOL2      MYCOL3      MYCOL6      MYCOL7      MYCOL8      MYCOL9      MYCOL10
123456      1            abc            2.46a      TRUE      CDE-33      8.35B      09/01/2011

I also agree that sample data and expected results might clarify the requirements.
0
 
newtoperlpgmAuthor Commented:
I did provide sample data, however I am providing more so that you can see the exact rows I want to retrieve.  I want to obtain row 1 because in MYCOL9 the date is the most current date.  I don't want to retrieve Row 3.  So my query would select both row 1 and row 2.  

MYCOL1      MYCOL2      MYCOL3      MYCOL6      MYCOL7      MYCOL8      MYCOL9      MYCOL10
123456      1            abc            1.82a      TRUE      PDE-23      9.23B      10/01/2011
123456      1            abc            4.35a      TRUE      ABE-33      7.53C      10/01/2011
123456      1            abc            2.46a      TRUE      PDE- 33     8.35B       09/01/2011
0
 
awking00Commented:
The query I proposed should do that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.