| I want a simpler query!!! [message #2831] |
Fri, 16 August 2002 01:41  |
pkmicf
Messages: 18
Registered: August 2002
|
Junior Member |
|
|
I have a table with the following columns
itemno, pono, podate, consigneeNo, rate
such that (1). one item may have many purchase orders(pono)
(2). each PO may have many consignees with different rates.
Now I want to select a row with all columns from this table for a given itemno, latest pono with highest rate.
I have tried with the following query and works fine. But I want the simpler one. Please help.
Query:-
-----
SELECT *
FROM Table1
WHERE itemno=1
and podate in (select max(podate) from table1 where itemno=1)
and rate in (select max(rate) from table1 WHERE itemno=1 and podate in (select max(podate) from table1 where itemno=1))
|
|
|
|
| Re: I want a simpler query!!! [message #2911 is a reply to message #2831] |
Tue, 20 August 2002 22:55  |
Wijnand
Messages: 11
Registered: March 2002
|
Junior Member |
|
|
select * from table1,
(select max((to_number(to_char(podate,'yyyymmdd')) * 1000000000000 + rate)) zz from table1 where itemno = 1) table2
where ((to_number(to_char(podate,'yyyymmdd')) * 1000000000000 + rate)) = zz and itemno = 1
by converting the date to a enormous number and adding the rate to it, you can calculate the latest day and the highest rate on that day in one column.
Unless you have rates higher than 1000000000000 this will work.
table 2 is an inline view that contains the highest zz column value ever for that item.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
