CODEWITHSUNDEEP
sqloracle-databaseselect
silverhawk
silverhawk

Reputation: 609

How to select the last data? Oracle

Have tables Employees и Invoice.

Employees:                              Invoice:
id_emp    name      id_invoice          id_invoice   date_invoice
-------------------------------         ------------------------------
1         Peter     5                   5            01.01.2014 10:56 
2         Alfred    6                   6            02.04.2014 11:21
3         Jack      7                   7            03.09.2014 12:32
2         Alfred    8                   8            10.10.2014 16:43

How can I get all the employees and their only last invoices, ie in the form:

id_emp    name      id_invoice   date_invoice
------------------------------------------------------
1         Peter     5            01.01.2014 10:56 
3         Jack      7            03.09.2014 12:32
2         Alfred    8            10.10.2014 16:43

I tried to do:

SELECT id_emp, name, emp.id_invoice, max(date_invoice) as date_invoice 
   FROM Employees emp, Invoice inv 
     WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name, emp.id_invoice;

But it doesn't work as I want.

Upvotes: 1

Views: 706

Answers (5)

Mureinik
Mureinik

Reputation: 312219

One way to do it is to rank the invoices:

SELECT id_emp, name, id_invoice
FROM   (SELECT id_emp, name, emp.id_invoice, 
               RANK()  (ORDER BY date_invoice PARTITION BY id_emp) AS rk
        FROM   Employees emp
        JOIN   Invoice inv ON emp.id_invoice = inv.id_invoice)
WHERE  rk = 1

Upvotes: 0

Kim Berg Hansen
Kim Berg Hansen

Reputation: 2019

When you do GROUP BY and need to include more columns from "the last" invoice, KEEP syntax can be very handy:

with employees as (
   select 1 id_emp, 'Peter' name, 5 id_invoice from dual
   union all
   select 2, 'Alfred', 6 from dual
   union all
   select 3, 'Jack', 7 from dual
   union all
   select 2, 'Alfred', 8 from dual
), invoice as (
   select 5 id_invoice, to_date('01.01.2014 10:56','DD-MM-YYYY HH24:MI') date_invoice from dual
   union all
   select 6, to_date('02.04.2014 11:21','DD-MM-YYYY HH24:MI')from dual
   union all
   select 7, to_date('03.09.2014 12:32','DD-MM-YYYY HH24:MI')from dual
   union all
   select 8, to_date('10.10.2014 16:43','DD-MM-YYYY HH24:MI')from dual
)
select emp.id_emp
     , max(emp.name) name
     , max(inv.id_invoice) keep (dense_rank last order by inv.date_invoice) last_id_invoice
     , max(inv.date_invoice) last_date_invoice
  from employees emp
  join invoice inv
      on inv.id_invoice = emp.id_invoice
 group by emp.id_emp
 order by emp.id_emp

Group by id_emp. (I assume id_emp is a primary key and emp.name is redundant, therefore I use max(name) - if id_emp is not a key, then include it in the group by.)

The last invoice date we get with an ordinary max() function. Getting the id_invoice for the invoice that has the last date is done with the KEEP syntax - using DENSE_RANK LAST order by inv.date_invoice tells the max(inv.id_invoice) function that it should only take the max() of those rows that have the LAST date_invoice.

An alternative method is using analytic functions like for example:

with employees as (
   select 1 id_emp, 'Peter' name, 5 id_invoice from dual
   union all
   select 2, 'Alfred', 6 from dual
   union all
   select 3, 'Jack', 7 from dual
   union all
   select 2, 'Alfred', 8 from dual
), invoice as (
   select 5 id_invoice, to_date('01.01.2014 10:56','DD-MM-YYYY HH24:MI') date_invoice from dual
   union all
   select 6, to_date('02.04.2014 11:21','DD-MM-YYYY HH24:MI')from dual
   union all
   select 7, to_date('03.09.2014 12:32','DD-MM-YYYY HH24:MI')from dual
   union all
   select 8, to_date('10.10.2014 16:43','DD-MM-YYYY HH24:MI')from dual
)
select id_emp, name, id_invoice, date_invoice
  from (
   select emp.id_emp
        , emp.name
        , inv.id_invoice
        , inv.date_invoice
        , row_number() over (
             partition by emp.id_emp
             order by inv.date_invoice desc
          ) rn
     from employees emp
     join invoice inv
         on inv.id_invoice = emp.id_invoice
  )
 where rn = 1
 order by id_emp

Both methods have the advantage of not accessing tables more than once. The second method using analytic row_number() function is the easiest if you need many columns from "the last" row - in such cases KEEP method requires copying the same KEEP clause in many columns.

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

You need to get the latest invoice number instead of the latest invoice date since there can be two invoices on the same day

Also as per your data you can identify the latest invoice based on its invoice number.Try this:-

SELECT id_emp, name, max(emp.id_invoice), date_invoice
FROM Employees emp, Invoice inv 
WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name, date_invoice;

Upvotes: 0

Ahmad
Ahmad

Reputation: 12737

Here you go

    SELECT id_emp, name, emp.id_invoice, max(date_invoice) as date_invoice 
       FROM Employees emp , Invoice inv 
    WHERE 
emp.id_emp = inv.id_emp 
AND inv_id_invoice = (
      SELECT MAX(id_invoice) from Invoices c where c.id_emp = inv.id_emp
)

Upvotes: 0

Pragnesh Khalas
Pragnesh Khalas

Reputation: 2898

Below Query will help you.You also need to grouping of id_invoice.

SELECT id_emp, name, MAX(emp.id_invoice) AS id_invoice, 
  max(date_invoice) as date_invoice 
FROM Employees emp, Invoice inv 
WHERE emp.id_invoice = inv.id_invoice GROUP BY id_emp, name;

Upvotes: 3

Related Questions