Selecting latest incidences

[RobbieBHA]

I have a database of treatments provided and I need to select just the latest 5 incidences of a certain treatment code for each practitioner. I have the date of each treatment and wonder if there is anyway I can do this automatically in BO?

Thanks in advance,

Rob.

 

[blom0344]

Hello Rob,

Just with BO this will have to be done by using the ranking function with top 5 to give you the appropriate records.
With SQL you can ( in some RDBMS's) do a sort on the data-set by an order by statement followed with a special statement like:

FETCH FIRST 5 ROWS ONLY;

(this is syntax for DB2)

Always state the RDBMS you are working with, this will give you more exact responses.

You can manually alter the SQL that BO generates by activating the "Do not generate SQL" setting if you would want to..... T. Blom
Information analyst
tbl@shimano-eu.com

 

[RobbieBHA]

Thanks for the prompt reply and the hints.

We are using Oracle 8i. Would this change the syntax at all?

Rob.

 

[pabloj]

The syntax for Oracle is &quot; ... WHERE ROWNUM < 6&quot; Stick to your guns

 

[RobbieBHA]

Quote &quot;The syntax for Oracle is &quot; ... WHERE ROWNUM < 6&quot; &quot;

This is close but only returns the first 5 rows in the report and not the first 5 rows for each practitioner. Each practitioner is identified by a 6 digit personal number called PERSNUM.

Can anyone help?

Thanks in advance again.

Rob.

 

[blom0344]

Rob,

What you should do is dive into the analytical functions that ORACLE has to offer. With the RANK function you can assign a ranking that resets itself for each practioner. If you apply this and create a new object to hold the ranking you can be done with just a where-clause like:

WHERE <NEW-RANK-OBJECT> < 6 T. Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Let me add an example with DB2 syntax (pure SQL):

RANK() OVER (PARTITION BY PERSNUM
ORDER BY DATE DESC) AS NEW-RANK-OBJECT T. Blom
Information analyst
tbl@shimano-eu.com