Home » SQL & PL/SQL » SQL & PL/SQL » Selecting latest date
Selecting latest date [message #564366] Thu, 23 August 2012 03:08 Go to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Hi guys, have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates

PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012

I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)

PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012

I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.

Any thought would be really appreciated

Thanks

Steve
Re: Selecting latest date [message #564368 is a reply to message #564366] Thu, 23 August 2012 03:33 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Use the rank function:

select PART_NO,QUANTITY,APPLIED 
from 
(select PART_NO,QUANTITY,APPLIED, 
RANK() OVER (PARTITION BY PART_NO ORDER BY APPLIED DESC) as rnk from pcuk_BG_alloc_TAB) 
where rnk=1;

[Updated on: Thu, 23 August 2012 13:55] by Moderator

Report message to a moderator

Re: Selecting latest date [message #564369 is a reply to message #564368] Thu, 23 August 2012 03:43 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks that almost works perfectly. I have a few extra records for different months. I think the max just looks at the day (so 25/07/2012 will be bought back rather than say 02/08/2012). Guessing need to convert the date from DD/MM/YYYY to MM/DD/YYYY?
Re: Selecting latest date [message #564370 is a reply to message #564369] Thu, 23 August 2012 03:48 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
I don't think MAX() just looks at the DAY field, It checks the full date.

Regards,
Veeresh
Re: Selecting latest date [message #564371 is a reply to message #564366] Thu, 23 August 2012 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Selecting latest date [message #564373 is a reply to message #564370] Thu, 23 August 2012 03:59 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks Veeresh I can see the problem. The applied column is actually VARCHAR2 rather than DATE. Is this simple to change?

Re: Selecting latest date [message #564374 is a reply to message #564373] Thu, 23 August 2012 04:08 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Oh,
Please post the table structure.

I think you have to use TO_DATE function.

Regards,
Veeresh

Re: Selecting latest date [message #564376 is a reply to message #564374] Thu, 23 August 2012 04:15 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks Veeresh.

PART_NO VARCHAR2(35 BYTE)
QUANTITY NUMBER
APPLIED VARCHAR2(30 BYTE)
Re: Selecting latest date [message #564377 is a reply to message #564376] Thu, 23 August 2012 04:19 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
And I hope data on APPLIED column is stored as only DD/MM/YYYY format.

select PART_NO,QUANTITY,APPLIED 
from 
(select PART_NO,QUANTITY,APPLIED, 
RANK() OVER (PARTITION BY PART_NO ORDER BY TO_DATE(applied,'DD/MM/YYYY') desc) 
as rnk from pcuk_BG_alloc_TAB) where rnk=1; 


Regards,
Veeresh

[Updated on: Thu, 23 August 2012 13:54] by Moderator

Report message to a moderator

Re: Selecting latest date [message #564380 is a reply to message #564377] Thu, 23 August 2012 04:25 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
That is absolutely perfect. Thanks for the help today Smile
Re: Selecting latest date [message #564382 is a reply to message #564380] Thu, 23 August 2012 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remember to follow the guidelines for your next question(s).

Regards
Michel
Re: Selecting latest date [message #564387 is a reply to message #564380] Thu, 23 August 2012 05:21 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
do not worry now ok

[Updated on: Thu, 23 August 2012 05:22]

Report message to a moderator

Re: Selecting latest date [message #564389 is a reply to message #564387] Thu, 23 August 2012 05:34 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Hi Veeresh, sorry further question! I am trying to add a column from another table (contract) joining on part_no

select PART_NO, QUANTITY, APPLIED, CONTRACT
from
(
select A.PART_NO, A.QUANTITY, A.APPLIED, B.CONTRACT, RANK() OVER (PARTITION BY A.PART_NO ORDER BY TO_DATE(a.applied,'dd/mm/yyyyHH24:mi:ss') desc) as rnk from pcuk_BG_alloc_TAB A, INV_PART_STOCK_RESERVATION B
)
where rnk=1
and
a.part_no = b.part_no


I am getting the error "B"."PART_NO": invalid identifier. However part_no definitely does exist in INV_PART_STOCK_RESERVATION. The sql runs witout the a.part_no = b.part_no so think the where clause needs amending
Re: Selecting latest date [message #564391 is a reply to message #564389] Thu, 23 August 2012 05:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 23 August 2012 10:51
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel

Re: Selecting latest date [message #564392 is a reply to message #564389] Thu, 23 August 2012 05:47 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
I am not very sure about the requirement here,

Try,

select PART_NO, QUANTITY, APPLIED, CONTRACT
from
(
select A.PART_NO, A.QUANTITY, A.APPLIED, B.CONTRACT, 
RANK() OVER (PARTITION BY A.PART_NO ORDER BY TO_DATE(a.applied,'dd/mm/yyyyHH24:mi:ss') desc) 
as rnk from pcuk_BG_alloc_TAB A, INV_PART_STOCK_RESERVATION B where a.part_no = b.part_no
)
where rnk=1


Regards,
Veeresh

[Updated on: Thu, 23 August 2012 13:54] by Moderator

Report message to a moderator

Re: Selecting latest date [message #564393 is a reply to message #564392] Thu, 23 August 2012 05:49 Go to previous messageGo to next message
bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore
Member
Steve,

Also, Kindly use the Code Tags As said by Michel.

Regards,
Veeresh
Re: Selecting latest date [message #564394 is a reply to message #564392] Thu, 23 August 2012 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not very sure about the requirement here,


@SteveShephard,
This is one of the reason we ask for a test case.
The other one is that we can test our queries and so not post incorrect ones.

Regards
Michel

[Updated on: Thu, 23 August 2012 06:01]

Report message to a moderator

Re: Selecting latest date [message #564395 is a reply to message #564394] Thu, 23 August 2012 06:12 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Thanks, think I am almost there with the main query.
select BGMC, VALID_FROM,VALID_TO, QUANTITY, CATALOG_NO, SUM(bUY_QTY)Qty_Ordered, SUM(QTY_ASSIGNED) Qty_Reserved, SUM(QTY_ON_ORDER) Qty_On_Order, suM(QTY_PICKED) Qty_Picked, SUM(QTY_SHIPPED)Qty_Shipped, SUM(QTY_INVOICED) Qty_Invoiced, (case when SUM(Buy_Qty) >QUANTITY then 'Y' else 'N' end ) OVERSOLD, APPLIED
from(
select PGA.MARKET_CODE BGMC, PGA.VALID_FROM, PGA.VALID_TO, PGA.QUANTITY, coJ.CATALOG_NO, SUM(coJ.BUY_QTY_DUE)Buy_Qty,
SUM(COJ.QTY_ASSIGNED) Qty_Assigned, SUM(COJ.QTY_ON_ORDER) Qty_On_Order, SUM(COJ.QTY_PICKED) Qty_Picked,
SUM(COJ.QTY_SHIPPED)Qty_Shipped, SUM(COJ.QTY_INVOICED) Qty_Invoiced, PGA.APPLIED,

RANK() OVER (PARTITION BY pga.PART_NO ORDER BY
TO_DATE(pga.applied,'dd/mm/yyyyHH24:mi:ss') desc) as rnk from customer_order_join coj, pcuk_BG_alloc_TAB pga

where pga.market_code = coJ.market_code
and pga.part_no = COJ.catalog_no
and COJ.WANTED_DELIVERY_DATE between PGA.VALID_FROM and PGA.VALID_TO

GROUP BY PGA.MARKET_CODE, VALID_FROM, VALID_TO, QUANTITY, CATALOG_NO, PGA.APPLIED)
where rnk=1

GROUP BY BGMC, VALID_FROM, VALID_TO, QUANTITY, CATALOG_NO, APPLIED

Think there is a problem with the grouping though?

[Updated on: Thu, 23 August 2012 13:53] by Moderator

Report message to a moderator

Re: Selecting latest date [message #564398 is a reply to message #564395] Thu, 23 August 2012 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 59296
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable.
If you don't know how to correctly write a query, use a SQL formatter before posting it.

Regards
Michel

[Updated on: Thu, 23 August 2012 06:44]

Report message to a moderator

Re: Selecting latest date [message #564399 is a reply to message #564395] Thu, 23 August 2012 06:52 Go to previous messageGo to next message
cookiemonster
Messages: 10999
Registered: September 2008
Location: Rainy Manchester
Senior Member
SteveShephard wrote on Thu, 23 August 2012 12:12

Think there is a problem with the grouping though?

We don't have your tables or data.
We can't run the query to see what output you are getting.
We don't know what output you expect to get.
And as Michel points out the query you've posted is unreadable.

Given those facts, how do you expect us to help?

Post a test case as already asked so we can actually replicate what you have.
Re: Selecting latest date [message #564400 is a reply to message #564399] Thu, 23 August 2012 06:58 Go to previous messageGo to next message
SteveShephard
Messages: 34
Registered: August 2012
Member
Sorry 'cookiemonster' I was just trying to get help with the syntax of the query. Am working on it and making progress now Smile
Re: Selecting latest date [message #564401 is a reply to message #564400] Thu, 23 August 2012 07:02 Go to previous message
cookiemonster
Messages: 10999
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's a complicated query and we're not sql compilers.
We can fix syntax issues but only if you give us the tables to run it against.
Previous Topic: issue in union condition
Next Topic: select statement
Goto Forum:
  


Current Time: Thu Oct 02 08:28:42 CDT 2014

Total time taken to generate the page: 0.07634 seconds