Home » SQL & PL/SQL » SQL & PL/SQL » Query To retrieve latest record
Query To retrieve latest record [message #241850] Wed, 30 May 2007 13:41 Go to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Hi,
I have a table and records like this:
CREATE TABLE MYTABLE(EMPLOYEE NUMBER, PROJECT NUMBER, EFFECTIVE_DATE DATE)

INSERT INTO MYTABLE VALUES(100, 10001, '01-APR-07')

INSERT INTO MYTABLE VALUES(101, 10001, '11-APR-07')

INSERT INTO MYTABLE VALUES(200, 10001, '01-MAY-07')

INSERT INTO MYTABLE VALUES(300, 20001, '01-JAN-07')

INSERT INTO MYTABLE VALUES(200, 40001, '01-FEB-07')

INSERT INTO MYTABLE VALUES(230, 40001, '10-APR-07')



How to get employee who worked last (recent) on a particular project.
e.g for Project 10001, employee 200 is the last one (more recent) to work
similarly employee 230 is the recent one to work on 40001 project

The output should be

EMPLOYEE         PROJECT	 EFFECTIVE_DATE
200		   10001	 01-MAY-07
300		   20001	 01-JAN-07
230		   40001	 10-APR-07 




Regards
Sandi
Re: Query To retrieve latest record [message #241854 is a reply to message #241850] Wed, 30 May 2007 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is your Oracle version?
What if several employees are the last ones?
What about searching for this classical question as for top N one?

Regards
Michel
Re: Query To retrieve latest record [message #241855 is a reply to message #241854] Wed, 30 May 2007 14:10 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks Michel for your reply.

I am using Oracle 9.2.0.6

For a Particular Project only One employee can be the last one (Based on Date)

There cannot be situation where in two employee's effective for a particular project is same.
This situation cannot exist
employee   project    effective_date
100         10001       '01-MAY-07'
200         10001       '01-MAY-07'


However for another project same effective_date is possible

This situation can exist
employee   project    effective_date
100         10001       '01-MAY-07'
200         30001       '01-MAY-07'
100         40001       '01-MAY-07'



Regards
Sandi

[Updated on: Wed, 30 May 2007 14:12]

Report message to a moderator

Re: Query To retrieve latest record [message #241857 is a reply to message #241855] Wed, 30 May 2007 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno, ename, hiredate
  2  from (
  3  select deptno, ename, hiredate, 
  4         row_number() over (partition by deptno order by hiredate desc) rn
  5  from emp
  6  )
  7  where rn = 1
  8  order by deptno
  9  /
    DEPTNO ENAME      HIREDATE
---------- ---------- -------------------
        10 MILLER     23/01/1982 00:00:00
        20 FORD       03/12/1981 00:00:00
        30 JAMES      03/12/1981 00:00:00

3 rows selected.

Regards
Michel
Re: Query To retrieve latest record [message #241864 is a reply to message #241857] Wed, 30 May 2007 14:48 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
And, another method:

SQL> CREATE TABLE MYTABLE(EMPLOYEE NUMBER, PROJECT NUMBER, EFFECTIVE_DATE DATE);

Table created.

SQL> INSERT INTO MYTABLE VALUES(100, 10001, '01-APR-07');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(101, 10001, '11-APR-07');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(200, 10001, '01-MAY-07');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(300, 20001, '01-JAN-07');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(200, 40001, '01-FEB-07');

1 row created.

SQL> INSERT INTO MYTABLE VALUES(230, 40001, '10-APR-07');

1 row created.

SQL>
SQL> SELECT x.employee, x.project, x.effective_date
  2    FROM mytable x,
  3         (SELECT project, MAX(effective_date) effective_date
  4           FROM mytable
  5          GROUP BY project
  6         ) y
  7   WHERE x.project        = y.project
  8     AND x.effective_date = y.effective_date
  9   ORDER BY project
 10  /

  EMPLOYEE    PROJECT EFFECTIVE_DATE
---------- ---------- --------------------
       200      10001 01-MAY-0007 00:00:00
       300      20001 01-JAN-0007 00:00:00
       230      40001 10-APR-0007 00:00:00
Re: Query To retrieve latest record [message #241882 is a reply to message #241864] Wed, 30 May 2007 16:51 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thank You. I was trying something with EXISTS but it did not work. This one is a good approach.

Regards,
Sandi
Re: Query To retrieve latest record [message #262014 is a reply to message #241864] Fri, 24 August 2007 06:15 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Frank or Michel,
In reference to your suggested sql statements:

Please help me flip your select statement into an insert.

SQL> CREATE TABLE NEWTABLE(EMPLOYEE NUMBER, PROJECT NUMBER);

Notice my NEWTABLE does not have the effective_date column.
EMPLOYEE_NUMBER and PROJECT_NUMBER are the primary key columns.

I want to insert only the records from MYTABLE w/ the latest effective date. How would you write the insert statement.

[Updated on: Fri, 24 August 2007 06:16]

Report message to a moderator

Re: Query To retrieve latest record [message #262016 is a reply to message #262014] Fri, 24 August 2007 06:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which latest effective date? Overall? By employee? By project? Both?
What did you already try?
Why does not this fit what you want?

Regards
Michel
Re: Query To retrieve latest record [message #262160 is a reply to message #262016] Fri, 24 August 2007 14:11 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Michel,
I didn't note that you and Frank used seperate examples. My need is the same as is used in this particular title, "Query To retrieve latest record". I will attach my example but am wondering if I should open a new Title. If you suspect I should please let me and I will do so. Thanks, Sandy
  • Attachment: orafaq.doc
    (Size: 38.50KB, Downloaded 220 times)
Re: Query To retrieve latest record [message #262163 is a reply to message #262160] Fri, 24 August 2007 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We don't download .doc as well as any file that may contain a virus.
If you want to attach a file, give a plain text (txt) one.

Regards
Michel
Re: Query To retrieve latest record [message #262165 is a reply to message #262163] Fri, 24 August 2007 14:22 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Response time here is incredible. Attached is the .txt you requested.
  • Attachment: orafaq.txt
    (Size: 3.67KB, Downloaded 154 times)
Re: Query To retrieve latest record [message #262170 is a reply to message #262165] Fri, 24 August 2007 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We are all around the world, there are almost every time someone listening.

There is a missing ',' at the end of line 4 of your query (and the table name is wrong):
SQL> CREATE TABLE COST
  2  (
  3    PART_ID               VARCHAR2(9 BYTE)        NOT NULL,
  4    BEGIN_DATE            DATE,
  5    COGNIZANCE_CODE       VARCHAR2(2 BYTE),
  6    MATERIAL_CNTL_CODE    VARCHAR2(1 BYTE),
  7    UNIT_PRICE_AMT        NUMBER(14,2),
  8    END_DATE              DATE,
  9    REPAIR_NET_PRICE_AMT  NUMBER(10,2),
 10    LAST_ALTER_TS         DATE
 11  )
 12  /

Table created.

SQL> CREATE TABLE PART
  2  (
  3    PART                          CHAR(9 BYTE)    NOT NULL,
  4    COG                           CHAR(2 BYTE),
  5    FSC                           CHAR(4 BYTE),
  6    MCC                           CHAR(1 BYTE),
  7    REPAIR_NET_PRICE              NUMBER(10,2),
  8    ROUTING_ID_CODE               CHAR(3 BYTE),
  9    SECURITY_CLASSIFICATION_CODE  CHAR(1 BYTE),
 10    SMIC                          CHAR(2 BYTE),
 11    UNIT_OF_ISSUE                 CHAR(2 BYTE)    NOT NULL,
 12    UNIT_PRICE                    NUMBER(12,2)    NOT NULL,
 13    ACQUISITION_ADVICE_CODE       CHAR(1 BYTE)    NOT NULL,
 14    QUANTITY_PER_UNIT_PACKAGE     CHAR(1 BYTE)    NOT NULL,
 15    SOURCE_OF_SUPPLY              CHAR(3 BYTE)    NOT NULL,
 16    SLC                           CHAR(1 BYTE)    NOT NULL,
 17    ITEM_NAME                     VARCHAR2(50 BYTE)
 18  );

Table created.

SQL> insert into part_stage
  2     (part, cog, mcc, unit_price, repair_net_price)
  3  select part_id, cognizance_code, 
  4         material_cntl_code, unit_price_amt
  5         repair_net_price_amt
  6  from (
  7      select part_id, begin_date, cognizance_code, 
  8         material_cntl_code, unit_price_amt,
  9         repair_net_price_amt, 
 10         row_number () over (partition by part_id order by begin_date desc) rn
 11        from cost)
 12  where rn = 1;
insert into part_stage
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> insert into part
  2     (part, cog, mcc, unit_price, repair_net_price)
  3  select part_id, cognizance_code, 
  4         material_cntl_code, unit_price_amt
  5         repair_net_price_amt
  6  from (
  7      select part_id, begin_date, cognizance_code, 
  8         material_cntl_code, unit_price_amt,
  9         repair_net_price_amt, 
 10         row_number () over (partition by part_id order by begin_date desc) rn
 11        from cost)
 12  where rn = 1;
insert into part
            *
ERROR at line 1:
ORA-00947: not enough values


SQL> insert into part
  2     (part, cog, mcc, unit_price, repair_net_price)
  3  select part_id, cognizance_code, 
  4         material_cntl_code, unit_price_amt,
  5         repair_net_price_amt
  6  from (
  7      select part_id, begin_date, cognizance_code, 
  8         material_cntl_code, unit_price_amt,
  9         repair_net_price_amt, 
 10         row_number () over (partition by part_id order by begin_date desc) rn
 11        from cost)
 12  where rn = 1;

0 rows created.

Regards
Michel

[Updated on: Fri, 24 August 2007 15:09]

Report message to a moderator

Re: Query To retrieve latest record [message #262175 is a reply to message #262170] Fri, 24 August 2007 15:32 Go to previous messageGo to next message
smithsl
Messages: 9
Registered: August 2007
Junior Member
Yes it was the comma. My apologies for the sloppy table names. I didn't change my table names back after testing. Thank you for your help.
Re: Query To retrieve latest record [message #262395 is a reply to message #241850] Sun, 26 August 2007 23:25 Go to previous messageGo to next message
sqlc
Messages: 6
Registered: August 2007
Junior Member
hey sorry to interrupt.i'm a newbie at this one.

i've written a query that works.

could any of u point out some situation where this query could fail because i've got a hunch it's not complete.


select * from mytable where (project,effective_date) in (select project, max(to_date(effective_date)) from mytable group by project)

i believe it's similar to the query someone posted above

but with queries i dont know if u can be too sure.
Re: Query To retrieve latest record [message #262402 is a reply to message #241850] Sun, 26 August 2007 23:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
could any of u point out some situation where this query could fail because i've got a hunch it's not complete.

One of these two situations happened (not both)
- query failed with error
- query takes a very long time and did not finish yet
and I cannot deduce from your post which of them happened.

I would bet for the second choice as you did not post the error message and it may take long for huge data in mytable.
How many rows are in mytable? And how many distinct project values?

Did you try to rewrite it to use analytics, as shown in previous posts?
Re: Query To retrieve latest record [message #262468 is a reply to message #241850] Mon, 27 August 2007 02:28 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Regarding the original question, why not

SQL> select * from mytable;

  EMPLOYEE    PROJECT EFFECTIVE_D
---------- ---------- -----------
       100      10001 01-APR-2007
       101      10001 11-APR-2007
       200      10001 01-MAY-2007
       300      20001 01-JAN-2007
       200      40001 01-FEB-2007
       230      40001 10-APR-2007

6 rows selected.

SQL> SELECT MAX(employee) KEEP (DENSE_RANK LAST ORDER BY effective_date) last_employee
  2       , project
  3       , MAX(effective_date)    
  4  FROM   mytable
  5  GROUP BY project;

LAST_EMPLOYEE    PROJECT MAX(EFFECTI
------------- ---------- -----------
          200      10001 01-MAY-2007
          300      20001 01-JAN-2007
          230      40001 10-APR-2007

3 rows selected.
Re: Query To retrieve latest record [message #262509 is a reply to message #262468] Mon, 27 August 2007 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why "MAX(employee) I prefer MIN? Wink

Regards
Michel
Re: Query To retrieve latest record [message #262517 is a reply to message #262509] Mon, 27 August 2007 06:00 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Or just for fun, how about AVG? Cool

SQL> SELECT MAX(employee) KEEP (DENSE_RANK LAST ORDER BY effective_date) last_employee
  2       , MIN(employee) KEEP (DENSE_RANK LAST ORDER BY effective_date) last_employee
  3       , AVG(employee) KEEP (DENSE_RANK LAST ORDER BY effective_date) last_employee
  4       , project
  5       , MAX(effective_date) AS last_date
  6  FROM   mytable
  7  GROUP BY project
  8  /

LAST_EMPLOYEE LAST_EMPLOYEE LAST_EMPLOYEE    PROJECT LAST_DATE
------------- ------------- ------------- ---------- -----------
          200           200           200      10001 01-MAY-2007
          300           300           300      20001 01-JAN-2007
          230           230           230      40001 10-APR-2007

3 rows selected.
Re: Query To retrieve latest record [message #262532 is a reply to message #262517] Mon, 27 August 2007 07:27 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
AVG will give you a strange result if you have 2 employees (say 200 and 300) in the same project with the same date. Cool

Regards
Michel
Previous Topic: Get DDL for advanced queus
Next Topic: Grant previlages
Goto Forum:
  


Current Time: Fri Dec 09 08:01:35 CST 2016

Total time taken to generate the page: 0.26569 seconds