My firm with employees :)
-- Simple table
CREATE TABLE Emp
(
dept VARCHAR2(20) NOT NULL,
nick VARCHAR2(20) NOT NULL,
pay NUMBER NOT NULL
);
-- All with different pays
INSERT INTO Emp
(dept, nick, pay)VALUES
('Admins' ,'Mike' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Admins' ,'Michael' ,4000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Admins' ,'Wellington' ,3000 );
-- First two with same pay checks
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Bix' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Pierre' ,5000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Ivanhoehoe' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Boldrick' ,2000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('Programers' ,'Payson' ,1000 );
-- 3. and 4. with same checks
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Maush' ,50000);
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Pierre' ,4000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Bob' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Boldrick' ,3000 );
INSERT INTO Emp
(dept ,nick ,pay) VALUES
('CEOs' ,'Vlax' ,1000 );
row_number(), rank(), dense_rank()
Here is the difference in functions:
SELECT dept, nick, pay,
row_number() over (partition by dept ORDER BY pay desc) rnum,
rank() over (partition by dept ORDER BY pay desc) rnk,
dense_rank() over (partition by dept ORDER BY pay desc) densrnk
FROM Empdept nick pay RNUM RNK DENSRNK;
| Admins | Mike | 5,000 | 1 | 1 | 1 |
| Admins | Michael | 4,000 | 2 | 2 | 2 |
| Admins | Wellington | 3,000 | 3 | 3 | 3 |
| CEOs | Maush | 50,000 | 1 | 1 | 1 |
| CEOs | Pierre | 4,000 | 2 | 2 | 2 |
| CEOs | Bob | 3,000 | 3 | 3 | 3 |
| CEOs | Boldrick | 3,000 | 4 | 3 | 3 |
| CEOs | Vlax | 1,000 | 5 | 5 | 4 |
| Programers | Bix | 5,000 | 1 | 1 | 1 |
| Programers | Pierre | 5,000 | 2 | 1 | 1 |
| Programers | Ivanhoe | 3,000 | 3 | 3 | 2 |
| Programers | Boldrick | 2,000 | 4 | 4 | 3 |
| Programers | Payson | 1,000 | 5 | 5 | 4 |
Partition by
Watch what "partition by" can do:
If we need 3 most payed emps by department we surround our query with SELECT * FROM( ourQuery ) where rnum < 4
LEAD/LAG
-- LEAD/LAG
SELECT dept, nick, pay,
LEAD(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(pay, 1, 0) OVER (PARTITION BY dept ORDER BY pay DESC NULLS LAST) PREV_HIGHER_SAL
FROM Emp
ORDER BY dept, pay DESC;dept nick pay NEXT_LOWER_SAL PREV_HIGHER_SAL
Admins Mike 5,000 4,000 0
Admins Michael 4,000 3,000 5,000
Admins Wellington 3,000 0 4,000
CEOs Maush 50,000 4,000 0
CEOs Pierre 4,000 3,000 50,000
CEOs Bob 3,000 3,000 4,000
CEOs Boldrick 3,000 1,000 3,000
CEOs Vlax 1,000 0 3,000
Programers Bix 5,000 5,000 0
Programers Pierre 5,000 3,000 5,000
Programers Ivanhoe 3,000 2,000 5,000
Programers Boldrick 2,000 1,000 3,000
Programers Payson 1,000 0 2,000
FIRST_VALUE and LAST_VALUE
FIRST_VALUE and LAST_VALUE work on first and last record in table
-- FIRST_VALUE/LAST_VALUE
SELECT nick, dept, pay - FIRST_VALUE(pay)
OVER (PARTITION BY dept ORDER BY pay DESC) pay_GAP
FROM Emp
ORDER BY dept, pay_GAP DESCnick dept pay_GAP
Mike Admins 0
Michael Admins -1,000
Wellington Admins -2,000
Maush CEOs 0
Pierre CEOs -46,000
Bob CEOs -47,000
Boldrick CEOs -47,000
Vlax CEOs -49,000
Bix Programers 0
Pierre Programers 0
Ivanhoe Programers -2,000
Boldrick Programers -3,000
Payson Programers -4,000
KEEP
Dunno what this does.
--FIRST/LAST
SELECT dept,
nick,
pay,
MIN(pay) KEEP (DENSE_RANK FIRST ORDER BY pay) OVER (PARTITION BY dept) "Lowest",
MAX(pay) KEEP (DENSE_RANK LAST ORDER BY pay) OVER (PARTITION BY dept) "Highest"
FROM Emp
ORDER BY dept, pay;dept nick pay Lowest Highest
Admins Wellington 3,000 3,000 5,000
Admins Michael 4,000 3,000 5,000
Admins Mike 5,000 3,000 5,000
CEOs Vlax 1,000 1,000 50,000
CEOs Boldrick 3,000 1,000 50,000
CEOs Bob 3,000 1,000 50,000
CEOs Pierre 4,000 1,000 50,000
CEOs Maush 50,000 1,000 50,000
Programers Payson 1,000 1,000 5,000
Programers Boldrick 2,000 1,000 5,000
Programers Ivanhoe 3,000 1,000 5,000
Programers Pierre 5,000 1,000 5,000
Programers Bix 5,000 1,000 5,000
Have Fun!
