Help with sql!!!

[MrNorth]

I am puzzled with this query:

I have three tables

A
--------------
data_id
status_id
etc


A_B
------------
data_id
status_id
status_date


B
-----------
status_id
status_description


Do you understand? In table A I have items, each item can have one or many statuses since Im using a link table A_B. And when I add a new status in A_B I also set a date when this new status was born.

The problem is, how can I write a sql query so I list only those items in A that has the LATEST status. All I have to go on is the status_date

I dont want multiple data_id in my resultset, only unique data_id with the latest status as set in A_B

please help me!! This is oracle9i, but i understand t-sql too...

/Henrik

[szlamany]

Many, many ways to do this - are you in a stored procedure?

Basic concept is:

Select * From A
   Where A.Status_Id=(Select Top 1 Status_Id From A_B
                                      Where A_B.Data_Id=A.Data_Id Order By Status_Date Desc)

You need to check each row in A to see if that row contains the STATUS of the "latest" entry - which in this example is in a sub-query.

Depending on the number of rows - you might want to consider pre-building a "temporary table" with the sub-query results - so you can join to it more cleanly. That is an avenue we would use in a SPROC with T-SQL...

[aconybeare]

I would say something like -

removed my offering as after looking at it, I realised it was junk, sorry!

[MrNorth]

hi and thanks for your answers, howeever I cant make this work in oracle (pl/sql). First of all because rownum and top works differently. Perhaps I need to use pl/sql or a cursor to get what I want. I will show you what all the tables look like, perhaps u can help me... im really lost here...


table ITEM
--------
item_id
item_no


table ITEM_FILE
-------------
item_id
file(blob)

table STATUS
--------------
status_id
status_descr

table ITEM_STATUS
----------------
item_id
status_id
status_date

The setup is like my first example. I have an item, an item has a file, and an item can have or or more statuses depending on the data in the ITEM_STATUS table. The status whtih the latest date is the newest status.

I need a query that selects item_no, file, status_descr for the LATEST item only. That is, the item that has the latest status_date in ITEM_STATUS

can u help me, please
Henrik

[Ecniv]

I think there is a function called last or there is in access. I recall there being one in Oracle, but I think it was tricky to use? Anyway if you have any pdfs of sql function, have a read on that and see if you can get it to work. It should bring back the latest date and if you return an id the latest date for that id.

From there, you'd have to put the rest of the query together.

EDIT: or was that the function first for thefirst date (assuming ordered by date).... ?

[oldbear]

First, you need to get the latest status from the A_B table:

select data_id,
status_id
from A_B ab1
where
ab1.status_date =
(select max(status_date)
from A_B ab2
where ab2.data_id = ab1.data_id)

This is a correlated subquery - this is the only way I know to do this.

Secondly, pop the subquery into a join with table A:

select * from
A
inner join
(select data_id,
status_id
from A_B ab1
where
ab1.status_date =
(select max(status_date)
from A_B ab2
where ab2.data_id = ab1.data_id)) ab
on (a.data_id = ab.data_id and a.status_id = ab.status_id)

Notice that I've used the original query as though it were a table when doing the join. SQL Server allows this, but I don't know if Oracle will do this. If it doesn't, then create the first query as a view. You can then use this view as though it were a table when doing the join.

Let me know if you need more explanation/info.

Hope this helps

Chris Seary