This content has been marked as final. Show 6 replies
No matter what x is, the answer to "How do I use ROWNUM to do +x+ ?" is usually "Don't use ROWNUM; use the analytic ROW_NUMBER function instead." On the rare occassions when that's not the right answer, the right answer is to the use the analytic RANK or DENSE_RANK function instead.
In this case, you want something like:
If you'd care to post CREATE TABLE and INSERT statements for the sample data, and the results you want from that sample data, then I could test it.
WITH got_r_num AS ( SELECT ticket_id , last_modified_by , modified_dt , cur_status , ipm_group , ROW_NUMBER () OVER ( PARTITION BY ticket_id ORDER BY modified_date DESC ) AS r_num FROM ipmprod WHERE ticket_id IN ( 2357981, 2357980 ) ) SELECT r.ticket_id , r.last_modified_by , r.modified_dt , a.created_by , r.cur_status , r.ipm_group FROM ipmtkte2e a , got_r_num r WHERE a.ticket_id = r.ticket_id AND r.r_num = 1 ;
Edited by: Frank Kulash on Sep 17, 2012 12:17 PM
madmac wrote:I'm not sure I understand.
Is there not a way to do this in one sql SELECT statement?
The query I posted IS one SELECT statement.
Do you mean "Is there a way to do this without a sub-query?". No, I don't think so. The query you posted needed 2 sub-queries, this needs only 1 (not to mention it does what you requested). Isn't that good enough? What's wrong with sub-queries? There are other ways to get these results using EXISTS or IN, but those ways still require sub-queries.
What Frank posted is a single SQL statement. He just replace your in-line view using rownum with a Common Table Expression (the with bit). The in-line view version, similar to your original query would be:
SELECT sql2.ticket_id, sql2.last_modified_by, sql2.modified_dt, created_by, sql2.cur_status, sql2.ipm_group FROM ipmtkte2e a, (SELECT ticket_id, last_modified_by, modified_dt, cur_status, ipm_group FROM (SELECT ticket_id, last_modified_by, modified_dt, cur_status, ipm_group, Row_Number() Over(Partition By ticket_id Order By modified_dt DESC) rn FROM ipmprod WHERE ticket_id in ( 2357981, 2357980 ) WHERE rn = 1) sql2 WHERE a.ticket_id = sql2.ticket_id