Get Max field value from a table

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 11 Aug 2005 10:25 am

Good morning guys, I'm having a hard time getting a simple information ...
The problem is this: I have a select that should catch a cod (cod_filial) that has the largest inventory available.

I have a qtd_estoque field, I must give a SUM in it to have the sum total and get only the highest value, from this value I need to get the cod that relates to it.
This code is também filter ... but I haven't been able to come up with a way to solve this ...:?: thanks guys, but ...
Rafael.[/code]
Rpizao
Location: RJ

Poston Thu, 11 Aug 2005 10:38 am

Let's see an example with SCOTT: here is the whole table
Code: Select all
SQL> SELECT * FROM EMP;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
         1

15 rows selected.

SQL>
SQL>
Here, shows the highest salary PER Department.
Code: Select all
SQL>
SQL>   select deptno, sum(sal) salario
  2    from emp
  3    group by deptno
  4    order by 2 desc nulls last
  5  /

    DEPTNO    SALARIO
---------- ----------
        20      10875
        30       9400
        10       8750


SQL>
SQL > now take just the first line (which is the one that matters)
Code: Select all
SQL> select *
  2  from
  3  (
  4    select deptno, sum(sal) salario
  5    from emp
  6    group by deptno
  7    order by 2 desc nulls last
  8  )
  9  where rownum=1 
10  /

    DEPTNO    SALARIO
---------- ----------
        20      10875

SQL>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Thu, 11 Aug 2005 2:18 pm

Vlw Gori ...
Only have a few filters that comes out, the other select above.
This will be a subselect .... I am not able to do with them to communicate ...
Vlw man, gave to clariada in ideas ...: idea: Hugs ...
Rafael.
Rpizao
Location: RJ


  • See also
    Replies
    Views
    Last Post