9 Replies Latest reply: Jun 5, 2012 6:54 AM by Oracle_Walker RSS

    How to get all minimum values for a table of unique records?

    Oracle_Walker
      I need to get the list of minimum value records for a table which has the below structure and data
      create table emp (name varchar2(50),org varchar2(50),desig varchar2(50),salary number(10),year number(10));
      
      insert into emp (name,org,desig,salary,year) values ('emp1','org1','mgr',3000,2005);
      insert into emp (name,org,desig,salary,year) values ('emp1','org1','mgr',4000,2007);
      insert into emp (name,org,desig,salary,year) values ('emp1','org1','mgr',7000,2007);
      insert into emp (name,org,desig,salary,year) values ('emp1','org1','mgr',7000,2008);
      insert into emp (name,org,desig,salary,year) values ('emp1','org1','mgr',7000,2010);
      commit;
      
      
      SELECT e.name,e.org,e.desig,min(e.year) FROM emp e,(
      SELECT e1.name,e1.org,e1.desig,e1.salary FROM emp e1
      GROUP BY (e1.name,e1.org,e1.desig,e1.salary)
      HAVING COUNT(*) >1) min_query
      WHERE min_query.name = e.name AND min_query.org = e.org AND min_query.desig =e.desig
      AND min_query.salary = e.salary
      group by (e.name,e.org,e.desig);
      With the above query i can get the least value year where the emp has maximum salary. It will return only one record. But i want to all the records which are minimum compare to the max year value

      Required output
      emp1     org1     mgr     7000     2008
      emp1     org1     mgr     7000     2007
      Please help me with this..
        • 1. Re: How to get all minimum values for a table of unique records?
          Frank Kulash
          Hi,

          Aggregate functions reduce the result set to one row per group.
          Almost all of the aggregate functions have analytic counterparts that can give the same results without reducing the result set. Since you want the output to contain multiple rows per group, then the analytic functions are probably better for you.

          Here's one way:
          WITH      got_analytics     AS
          (
               SELECT     name, org, desig, salary, year
               ,     MAX (salary)  OVER ( PARTITION BY  name, org, desig)          AS max_salARY
               ,     ROW_NUMBER () OVER ( PARTITION BY  name, org, desig, salary
                                          ORDER BY        year  DESC
                                 )                              AS year_num
               FROM    emp
          )
          SELECT     name, org, desig, salary, year
          FROM     got_analytics
          WHERE     salary          = max_salary
          AND     year_num     > 1
          ;
          • 2. Re: How to get all minimum values for a table of unique records?
            SamFisher
            Wow, I never knew that we can use 2 analytic functions in a single query.
            Thank You Frank. I'm gonna treasure it.
            • 3. Re: How to get all minimum values for a table of unique records?
              Frank Kulash
              Hi,
              SamFisher wrote:
              Wow, I never knew that we can use 2 analytic functions in a single query.
              Sure, you can compute any number of analytic functions in the same query, but they all have to be independent. You can NOT use one analytic function as the argument of another, for example, nor can you use one analytic function in the analytic clause of another. To do things like that, you need to compute the inner function in a sub-query.
              • 4. Re: How to get all minimum values for a table of unique records?
                SamFisher
                Frank,
                Can I write the query like this in case of duplicates?
                Definitely there would have been a better way than the query I've written.
                WITH      got_analytics     AS
                (
                     SELECT     name, org, desig, salary, year
                     ,     MAX (SALARY)  OVER ( PARTITION BY  NAME, ORG, DESIG)          AS MAX_SALARY
                     ,     ROW_NUMBER () OVER ( PARTITION BY  NAME, ORG, DESIG, SALARY 
                                                ORDER BY        year  DESC
                                       )                              AS YEAR_NUM
                       FROM    (SELECT 'emp1' AS NAME, 'org1' AS ORG, 'mgr' AS DESIG, 3000 AS SALARY, 2005 AS YEAR FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',4000,2007 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',4000,2008 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL 
                SELECT 'emp1','org1','mgr',7000,2008 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL)
                )
                SELECT     name, org, desig, salary, year
                FROM     got_analytics
                WHERE     salary          = max_salary
                AND     YEAR_NUM     > 1
                
                Result:
                emp1     org1     mgr     7000     2010
                emp1     org1     mgr     7000     2008
                emp1     org1     mgr     7000     2007
                emp1     org1     mgr     7000     2007
                
                
                
                WITH      got_analytics     AS
                (
                     SELECT     name, org, desig, salary, year
                     ,     MAX (SALARY)  OVER ( PARTITION BY  NAME, ORG, DESIG)          AS MAX_SALARY
                     ,     ROW_NUMBER () OVER ( PARTITION BY  NAME, ORG, DESIG, SALARY 
                                                ORDER BY        year  DESC
                                       )                              AS YEAR_NUM
                  ,     ROW_NUMBER () OVER ( PARTITION BY  NAME, ORG, DESIG, SALARY, Year 
                                                ORDER BY        YEAR  DESC
                                       )                              AS year_num2
                     FROM    (SELECT 'emp1' AS NAME, 'org1' AS ORG, 'mgr' AS DESIG, 3000 AS SALARY, 2005 AS YEAR FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',4000,2007 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',4000,2008 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL 
                SELECT 'emp1','org1','mgr',7000,2008 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL UNION ALL
                SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL)
                )
                SELECT     name, org, desig, salary, year
                FROM     got_analytics
                WHERE     salary          = max_salary
                AND     YEAR_NUM     > 1
                AND YEAR_NUM2 < 2
                
                Result:
                emp1     org1     mgr     7000     2008
                emp1     org1     mgr     7000     2007
                • 5. Re: How to get all minimum values for a table of unique records?
                  Frank Kulash
                  Hi,

                  It looks like this is getting away from OP's problem. I think you should start your own thread.
                  SamFisher wrote:
                  Frank,
                  Can I write the query like this in case of duplicates?
                  Definitely there would have been a better way than the query I've written.
                  What are you trying to do? Always explain how you get the results you want from the given sample data.
                  ,     ROW_NUMBER () OVER ( PARTITION BY  NAME, ORG, DESIG, SALARY, Year 
                                                  ORDER BY        YEAR  DESC
                                         )                              AS year_num2
                  It never makes sense to use the same column in both the PARTITION BY clause and the ORDER BY clause of the same function. If you're PARTITIONing BY year, then you'll only be comparing 2005s with other 2005s. If you really want arbitrary ordering, then ORDER BY a constant, such as 0.
                  • 6. Re: How to get all minimum values for a table of unique records?
                    SamFisher
                    I thought of posting a new thread but backed off as I thought that it was relavant to the OP.
                    I apologize for all the confusion.
                    WITH      got_analytics     AS
                    (
                         SELECT     name, org, desig, salary, year
                         ,     MAX (SALARY)  OVER ( PARTITION BY  NAME, ORG, DESIG)          AS MAX_SALARY
                         ,     ROW_NUMBER () OVER ( PARTITION BY  NAME, ORG, DESIG, SALARY 
                                                    ORDER BY        year  DESC
                                           )                              AS YEAR_NUM
                           FROM    (SELECT 'emp1' AS NAME, 'org1' AS ORG, 'mgr' AS DESIG, 3000 AS SALARY, 2005 AS YEAR FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',4000,2007 FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',4000,2008 FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',7000,2007 FROM DUAL UNION ALL 
                    SELECT 'emp1','org1','mgr',7000,2008 FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL UNION ALL
                    SELECT 'emp1','org1','mgr',7000,2010 FROM DUAL)
                    )
                    SELECT     name, org, desig, salary, year
                    FROM     got_analytics
                    WHERE     salary          = max_salary
                    AND     YEAR_NUM     > 1
                     
                    Result:
                    emp1     org1     mgr     7000     2010
                    emp1     org1     mgr     7000     2008
                    emp1     org1     mgr     7000     2007
                    emp1     org1     mgr     7000     2007
                    As per the requirement of Op, he shouldn't get the the max year(i.e, 2010).
                    But we are getting 2010 from the query. (Coz, we have duplicate set of data for the year 2010).
                    That is the reason why I have used year in both the partition clause and order by clause.
                    Please correct me if I'm wrong.
                    • 7. Re: How to get all minimum values for a table of unique records?
                      ShankarViji
                      Hi,

                      I tried and came with this friend, If it does not satisfy I apologizes..
                      SELECT   NAME, ORG, DESIG, SALARY, YEAR
                          FROM (SELECT DISTINCT NAME, ORG, DESIG, SALARY, YEAR,
                                                DENSE_RANK () OVER (PARTITION BY YEAR ORDER BY YEAR,
                                                 SALARY DESC) RN
                                           FROM TEMP_V)
                         WHERE RN = 1
                      ORDER BY YEAR DESC;
                      Output :
                      NAME     ORG     DESIG     SALARY     YEAR
                      emp1     org1     mgr     7000     2010
                      emp1     org1     mgr     7000     2008
                      emp1     org1     mgr     7000     2007
                      emp1     org1     mgr     3000     2005
                      Thanks,
                      Shankar
                      • 8. Re: How to get all minimum values for a table of unique records?
                        Oracle_Walker
                        Thank you Frank for the perfect solution and your explanation.. It helps a lot..

                        Edited by: Balaji on Jun 5, 2012 5:24 PM
                        • 9. Re: How to get all minimum values for a table of unique records?
                          Oracle_Walker
                          Frank's solution is the correct one. Thank you trying to help me.