Solved

Select rows from table sql

Posted on 2014-02-24
10
639 Views
Last Modified: 2014-02-28
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
0
Comment
Question by:newtoperlpgm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
10 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 400 total points
ID: 39882838
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
 

Author Comment

by:newtoperlpgm
ID: 39882995
This appeared to have worked, it yielded both rows of data.
0
 

Author Comment

by:newtoperlpgm
ID: 39883003
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39883008
>> 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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 39883534
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39883550
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
 
LVL 32

Expert Comment

by:awking00
ID: 39883593
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
 

Author Comment

by:newtoperlpgm
ID: 39884209
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
 
LVL 32

Expert Comment

by:awking00
ID: 39885563
The query I proposed should do that.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question