ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести строки с максимальным значением параметра
25 сообщений из 32, страница 1 из 2
  1  все
Вывести строки с максимальным значением параметра
    #39424373
invisiblesereja
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invisiblesereja
Гость
Добрый вечер.
Меня не нужно ругать и закидывать ссаными тряпками.
Я уже на протяжении долгого времени ломаю голову и так и не смог прийти к правильному ответу.
У меня было тестовое задание, на результат которого я уже никак не повлияю, но мне инетересно.

Задача простая:
Есть стандартная таблица Employees (Employee_ID, Department_ID, Salary).
Нужно вывести сотрудников с максимальной в своем отделе зарплатой, избежав подзапросов .
Таким образом, вариант

Код: plsql
1.
2.
3.
4.
select employee_id, department_id, salary from
(select employee_id, department_id, salary, row_number() over(partition by department_id order by salary desc) rn
from employees)
where rn = 1



отпадает, а других у меня и нет.
...
Рейтинг: 0 / 0
21.03.2017, 19:58
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424380
ma1tus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ma1tus
Участник
invisiblesereja,

DENSE_RANK LAST - не?
...
Рейтинг: 0 / 0
21.03.2017, 20:14
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424388
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
ma1tus,

Не. Немного ошибся) - аналитический dense_rank.

Код: plsql
1.
2.
3.
4.
5.
6.
SELECT * FROM (
SELECT e.department_name, e.last_name, e.salary, DENSE_RANK() 
   OVER (PARTITION BY e.department_id ORDER BY e.salary DESC NULLS LAST) AS drank
   FROM employees e
) 
WHERE drank=1
...
Рейтинг: 0 / 0
21.03.2017, 20:29
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424393
ma1tus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ma1tus
Участник
ora601, и точно ведь..)
...
Рейтинг: 0 / 0
21.03.2017, 21:01
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424399
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
ora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).
...
Рейтинг: 0 / 0
21.03.2017, 21:14
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424419
ora601
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora601
Участник
dbms_photoshopora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).

У тебя слишком академическое восприятие) Без инлайн вью это возможно наверное только на model наверное.

Обычно без подзапроса в таких задачах имеется ввиду без

Код: plsql
1.
WHERE (dept_id, salary) IN (SELECT dept_id, max(salary) FROM emp GROUP BY dept_id)



Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.
...
Рейтинг: 0 / 0
21.03.2017, 22:13
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424422
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
invisiblesereja,
какая версія оракля?
через модельку я б сделал так
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
  1  select * from emp e
  2  model
  3  return updated rows
  4  partition by (deptno)
  5  dimension by (empno e,DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) d)
  6  measures (ename, sal,-1 sall)
  7  rules upsert  all
  8  (sall[any,d=1]=sal[cv(),cv()]
  9* )
SQL> /

    DEPTNO          E          D ENAME             SAL       SALL
---------- ---------- ---------- ---------- ---------- ----------
        20       7902          1 FORD             3000       3000
        30       7698          1 BLAKE            2850       2850
        10       7839          1 KING             5000       5000
        20       7788          1 SCOTT            3000       3000



.....
stax
...
Рейтинг: 0 / 0
21.03.2017, 22:30
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424424
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
ora601dbms_photoshopora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).

У тебя слишком академическое восприятие) Без инлайн вью это возможно наверное только на model наверное.

Обычно без подзапроса в таких задачах имеется ввиду без

Код: plsql
1.
WHERE (dept_id, salary) IN (SELECT dept_id, max(salary) FROM emp GROUP BY dept_id)



Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.


нет, не так, у Вас тоже подзапрос (инлине вю)
я так понимаю, автор привел пример с подзапросом

.....
stax
...
Рейтинг: 0 / 0
21.03.2017, 22:35
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424442
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ma1tusora601, и точно ведь..)

А точно ли точно? Не смущaет что aggregate KEEP DENSE_RANK и анaлитический DENSE_RANK не эквивалентны.
Посему TC неплoхo бы определиться что делaть если в oтделе несколько сотрудников с максимальной в своем отделе зарплатой.
Ну и match_recognize:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
select  deptno,
        ename,
        sal
  from  emp
  match_recognize(
                  partition by deptno
                  order by sal desc
                  all rows per match
                  pattern(top+)
                  define
                     top as sal = first(sal) and match_number() = 1
                 )
/

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        20 FORD             3000
        30 BLAKE            2850

SQL>



SY.
...
Рейтинг: 0 / 0
21.03.2017, 23:19
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424445
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
ora601У тебя слишком академическое восприятиеОтнюдь. Я заядлый практик-эникейщик.
ora601Без инлайн вью это возможно наверное только на model наверное.Как я уже сказал решается через model/pattern matching.

Один из вариантов модели уже написал наш коллега из Львова.

Через pattern matching
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
SQL> with emp (deptno, empno, sal) as
  2  (
  3  select 1, 1, 1 from dual
  4  union all select 1, 2, 100 from dual
  5  union all select 1, 3, 100 from dual
  6  union all select 1, 4, 10 from dual
  7  union all select 2, 1, 1 from dual
  8  union all select 2, 2, 10 from dual
  9  )
 10  select *
 11  from emp
 12  match_recognize
 13  (
 14    partition by deptno
 15    order by sal desc
 16    all rows per match
 17    pattern ((max_sal|{-dummy-})+)
 18    define max_sal as sal = first(sal)
 19  ) mr
 20  order by deptno, empno;
 
    DEPTNO        SAL      EMPNO
---------- ---------- ----------
         1        100          2
         1        100          3
         2         10          2


ora601Обычно без подзапроса в таких задачах имеется ввиду
...
Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.Обычно предполагается, что уникальный один или испытуемый задаст вопрос про уникальность.
Если их таки не один, то keep не проканает, да.
...
Рейтинг: 0 / 0
21.03.2017, 23:21
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424448
12це
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
12це
Гость
invisiblesereja,

fetch first ... with ties
...
Рейтинг: 0 / 0
21.03.2017, 23:23
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424450
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Ну а еcли нужно только одного (например с бoльшим стажем):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select  deptno,
        ename,
        sal
  from  emp
  match_recognize(
                  partition by deptno
                  order by sal desc,hiredate desc
                  measures first(ename) as ename,
                           first(sal) as sal
                  one row per match
                  pattern(dummy+)
                  define
                    dummy as 1 = 1
                 )
/

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        30 BLAKE            2850

SQL>



SY.
...
Рейтинг: 0 / 0
21.03.2017, 23:25
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424453
кросс-банальность
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кросс-банальность
Гость
А банальный having = max уже не катит?
...
Рейтинг: 0 / 0
21.03.2017, 23:33
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424455
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
invisibleserejaНужно вывести сотрудников с максимальной в своем отделе зарплатой, избежав подзапросов .Все условия соблюдены.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SQL> with emp (deptno, empno, sal) as
  2  (
  3  select 1, 1, 1 from dual
  4  union all select 1, 2, 100 from dual
  5  union all select 1, 3, 100 from dual
  6  union all select 1, 4, 10 from dual
  7  union all select 2, 1, 1 from dual
  8  union all select 2, 2, 10 from dual
  9  )
 10  select deptno,
 11         max(sal) sal,
 12         regexp_count(listagg(sal, ',') within group(order by sal desc) || ',', max(sal) || ',') max_emp_cnt,
 13         rtrim(regexp_substr(listagg(empno, ',') within group(order by sal desc, empno), '(\d+,){' ||
 14                             regexp_count(listagg(sal, ',') within group(order by sal desc) || ',', max(sal) || ',') || '}'),
 15               ',') max_emp_list
 16    from emp
 17   group by deptno
 18   order by deptno;
 
    DEPTNO        SAL MAX_EMP_CNT MAX_EMP_LIST
---------- ---------- ----------- --------------------------------------------------------------------------------
         1        100           2 2,3
         2         10           1 2
...
Рейтинг: 0 / 0
21.03.2017, 23:36
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424456
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
кросс-банальностьА банальный having = max уже не катит?Ну покажи мастерство группировки.
...
Рейтинг: 0 / 0
21.03.2017, 23:38
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424457
Б.анальный
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Б.анальный
Гость
dbms_photoshopкросс-банальностьА банальный having = max уже не катит?Ну покажи мастерство группировки.
Код: plsql
1.
2.
3.
4.
5.
select e1.deptno, e1.empno, e1.sal
from emp e1
join emp e2 on e2.deptno=e1.deptno
group by e1.deptno, e1.empno, e1.sal  
having e1.sal = max(e2.sal)
...
Рейтинг: 0 / 0
21.03.2017, 23:39
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424459
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Б.анальный,

Таки да, как-то я забыл, что есть джойны.
...
Рейтинг: 0 / 0
21.03.2017, 23:42
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424460
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
12цеinvisiblesereja,

fetch first ... with ties

А "в своем отделе" куда воткнуть? Да и за фасадом подзапросов нeмepянo:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
SQL> variable c clob
SQL> begin
  2      dbms_utility.expand_sql_text('select  deptno,
  3          ename,
  4          sal
  5    from  emp
  6    order by sal desc
  7    fetch first 1 row
  8    with ties',:c);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."DEPTNO" "DEPTNO",
        "A1"."ENAME" "ENAME",
        "A1"."SAL" "SAL"
  FROM  (
         SELECT  "A2"."DEPTNO" "DEPTNO",
                 "A2"."ENAME" "ENAME",
                 "A2"."SAL" "SAL",
                 "A2"."SAL" "rowlimit_$_0",
                 RANK() OVER ( ORDER BY "A2"."SAL" DESC ) "rowlimit_$$_rank"
           FROM  (
                  SELECT  "A3"."EMPNO" "EMPNO",
                          "A3"."ENAME" "ENAME",
                          "A3"."JOB" "JOB",
                          "A3"."MGR" "MGR",
                          "A3"."HIREDATE" "HIREDATE",
                          CASE
                            WHEN USER <>'U1' THEN "A3"."SAL"
                            ELSE NULL
                          END  "SAL",
                          "A3"."COMM" "COMM",
                          "A3"."DEPTNO" "DEPTNO"
                    FROM  "SCOTT"."EMP" "A3"
                 ) "A2"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rank"<=1
  ORDER BY "A1"."rowlimit_$_0" DESC

SQL>



SY.
...
Рейтинг: 0 / 0
21.03.2017, 23:43
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424464
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Насчет:

Код: plsql
1.
2.
3.
4.
                          CASE
                            WHEN USER <>'U1' THEN "A3"."SAL"
                            ELSE NULL
                          END  "SAL",



это у меня висит DBMS_RLS policy запрещающая U1 глядеть на SAL.

SY.
...
Рейтинг: 0 / 0
21.03.2017, 23:48
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424467
Ещё на.фан.тазировал
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ещё на.фан.тазировал
Гость
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select distinct
   deptno,
   case when sal = first_value(sal) over(partition by deptno order by sal desc, empno) 
     then  empno 
     else first_value( empno) over(partition by deptno order by sal desc, empno) end empno,
   first_value(sal) over(partition by deptno order by sal desc, empno) sal
from emp;
...
Рейтинг: 0 / 0
22.03.2017, 00:01
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424528
ma1tus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ma1tus
Участник
SYma1tusora601, и точно ведь..)А точно ли точно? Не смущaет что aggregate KEEP DENSE_RANK и анaлитический DENSE_RANK не эквивалентны.
SY.Ну, решение в отсутствиe group by, мне приглянулось больше, чем
 
предположенное мною)
Код: plsql
1.
2.
3.
4.
5.
6.
select * from (
  select employee_id, department_id, salary, 
         max(salary) keep (dense_rank last order by salary) over (partition by department_id) as maxsalary
    from employees
   group by employee_id, department_id, salary 
) where salary = maxsalary

...
Рейтинг: 0 / 0
22.03.2017, 07:46
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39424548
ma1tus
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ma1tus
Участник
ma1tusпредположенное мноюСорри, group by-то зачем взгромоздил...
 
Код: plsql
1.
2.
3.
4.
5.
select * from (
  select employee_id, department_id, salary, 
         max(salary) keep (dense_rank last order by salary) over (partition by department_id) as maxsalary
    from employees
) where salary = maxsalary

...
Рейтинг: 0 / 0
22.03.2017, 08:46
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39425034
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
Б.анальныйdbms_photoshopпропущено...
Ну покажи мастерство группировки.
Код: plsql
1.
2.
3.
4.
5.
select e1.deptno, e1.empno, e1.sal
from emp e1
join emp e2 on e2.deptno=e1.deptno
group by e1.deptno, e1.empno, e1.sal  
having e1.sal = max(e2.sal)


понравилолсь решение, к сожеленью, не со Львова :)

......
stax
...
Рейтинг: 0 / 0
22.03.2017, 16:13
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39425073
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
invisiblesereja,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
with employees as (
select 1 employee_id, 1 department_id, 1 salary from dual union all
select 4, 1, 2 from dual union all
select 2, 2, 5 from dual union all
select 5, 2, 4 from dual union all
select 3, 3, 6 from dual union all
select 6, 3, 3 from dual
)
select max(employee_id) keep (dense_rank last order by salary) as employee_id,
department_id,
max(salary) as salary
from employees
group by department_id
...
Рейтинг: 0 / 0
22.03.2017, 16:49
| Ответить | Цитировать | Написать  
Вывести строки с максимальным значением параметра
    #39425082
Фотография --Eugene--
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
--Eugene--
Участник
будем надеяться, что уровень дохода уникально распределен среди сотрудников )))
...
Рейтинг: 0 / 0
22.03.2017, 16:53
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести строки с максимальным значением параметра
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&startmsg=39424373&tid=1886241]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 165ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 2ms
others: 200ms
total:  462ms
созд. / загр.: 462ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]