Home » SQL & PL/SQL » SQL & PL/SQL » How do I return results based on 2 columns?
How do I return results based on 2 columns? [message #590185] Mon, 15 July 2013 14:28 Go to next message
bertalot17
Messages: 4
Registered: July 2013
Junior Member
I've been having an issue and cannot figure it out for the life. First, here's an example set of the data I'm using so you can see exactly what I'm asking.



Emplid Effdt Effseq

10001 '01-JAN-99' 0

10001 '01-JUL-11' 0

10001 '01-JUL-11' 1

10001 '01-JUL-11' 2

10001 '01-JUL-12' 3



What I need to do here, is obtain 3 rows. The 3 rows I need are rows 1, 4, and 5. I need row 1 because its a completely different date. I need row 5 for the same reason: it's a different date. The issue arises with how I can obtain row 4. The problem is that because rows 2, 3, and 4 all have the same effective date(effdt), SQL Developer just returns one of those rows. Because those 3 rows all have the same effective date(effdt), the tie breaker becomes the effective sequence(effseq) number. When the effective date(effdt) is the same, you need to grab the maximum effective sequence(effseq) number and return that whole row's results such as the emplid, effdt, and effseq. It seems so straight forward and something you can use a subquery for, but its not that simple. Note, that you can specifically use the emplid = 10001 in any specific form because there's many employee id's. Also, the rows will not be in a specific order so you cannot just always grab rows 1, 4, and 5. Some employees may only have a single row in the database, and some may have 50 rows. Everything solely depends on the combination of employee id(emplid), effective date(effdt), and effective sequence(effseq) as the tie breaker. Any help in solving this problem would be greatly appreciated. Thank you in advance.
Re: How do I return results based on 2 columns? [message #590188 is a reply to message #590185] Mon, 15 July 2013 14:35 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems to be a rather simple task - MAX function would do it.
SQL> with
  2  test as
  3    (select 10001 emplid, date '1999-01-01' effdt, 0 effseq from dual union
  4     select 10001, date '2011-07-01', 0 from dual union
  5     select 10001, date '2011-07-01', 1 from dual union
  6     select 10001, date '2011-07-01', 2 from dual union
  7     select 10001, date '2012-07-01', 3 from dual
  8    )
  9  select emplid, effdt, max(effseq) maxeffseq
 10  from test
 11  group by emplid, effdt
 12  order by emplid, effdt;

    EMPLID EFFDT       MAXEFFSEQ
---------- ---------- ----------
     10001 01.01.1999          0
     10001 01.07.2011          2
     10001 01.07.2012          3

SQL>
Re: How do I return results based on 2 columns? [message #590189 is a reply to message #590185] Mon, 15 July 2013 14:35 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: How do I return results based on 2 columns? [message #590190 is a reply to message #590185] Mon, 15 July 2013 14:38 Go to previous messageGo to next message
manubatham20
Messages: 450
Registered: September 2010
Location: Champaign, IL
Senior Member

Try below, and format your post please.

SELECT emplid, 
       effdt, 
       Max(effseq) 
FROM   (SELECT 10001       Emplid, 
               '01-JAN-99' Effdt, 
               0           Effseq 
        FROM   dual 
        UNION ALL 
        SELECT 10001, 
               '01-JUL-11', 
               0 
        FROM   dual 
        UNION ALL 
        SELECT 10001, 
               '01-JUL-11', 
               1 
        FROM   dual 
        UNION ALL 
        SELECT 10001, 
               '01-JUL-11', 
               2 
        FROM   dual 
        UNION ALL 
        SELECT 10001, 
               '01-JUL-12', 
               3 
        FROM   dual) 
GROUP  BY emplid, 
          effdt; 


Manu
Re: How do I return results based on 2 columns? [message #590197 is a reply to message #590190] Mon, 15 July 2013 15:04 Go to previous messageGo to next message
bertalot17
Messages: 4
Registered: July 2013
Junior Member
Thanks for the input. I'm having issues still though. I'm thinking it has something to do with the data set im looking at. Here's what I'm doing:

select j.emplid, j.effdt, max(effseq) maxeffseq from
(
  SELECT j.effseq, e.emplid, j.position_nbr, e.last_name, e.first_name, e.middle_name, e.name_suffix, e.sex, TO_CHAR(e.birthdate, 'MM-DD-YYYY'), 
       trunc(months_between(sysdate,e.birthdate)/12), e.ethnic_group, j.empl_class

  FROM sysadm.ps_job j join sysadm.ps_employees e on j.emplid = e.emplid
  WHERE j.position_nbr not like ' %'
)
group by emplid, effdt;



Now the issue is telling me I have invalid identifiers. I've starred at the identifiers/column names, and still can't figure out what's going on. Do you see some mistake I'm making? I'm trying to incorporate your code into my data set(the inside query where I'm selecting from).
Re: How do I return results based on 2 columns? [message #590199 is a reply to message #590189] Mon, 15 July 2013 15:06 Go to previous messageGo to next message
bertalot17
Messages: 4
Registered: July 2013
Junior Member
BlackSwan wrote on Mon, 15 July 2013 14:35
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Blackswan,

My apologies on that. The information is as follows:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
"CORE 11.1.0.7.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production


OS; windows 7, 64-bit
Re: How do I return results based on 2 columns? [message #590200 is a reply to message #590199] Mon, 15 July 2013 15:08 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The first line shouldn't have the "j." alias because - there's none.
Re: How do I return results based on 2 columns? [message #590271 is a reply to message #590200] Tue, 16 July 2013 09:07 Go to previous message
bertalot17
Messages: 4
Registered: July 2013
Junior Member
After working closely with my team and all of us scratching our heads, we decided this is how we need to do it to get to the core of the data:

select *
from
(
   select emplid, empl_rcd, effdt, effseq, empl_class, deptid, action, action_reason, 
   row_number() over (partition by emplid, empl_rcd order by effdt desc, effseq desc) as selector
   from sysadm.ps_job
)
where selector = '1' 



Thanks for the help. Smile
Previous Topic: TWR--6502: ORA-06502: PL/SQL: numeric or value error: character string buffer
Next Topic: Updating view using instead of trigger
Goto Forum:
  


Current Time: Thu Aug 21 23:40:52 CDT 2014

Total time taken to generate the page: 0.10027 seconds