PL/SQL Get Max rownum

If you have such a field, "last_update",  "create_date"  or something like that I can help you write the query you need

You can add a column to the table which would indicate the order of entry. You would also need a trigger and sequence. The trigger can fetch the next value from the sequence and populate it. You might have to do some programming for existing records. In this way there would be no need to update the way records are getting inserted into the DB

After that based on the newly added column you can get the last entered record easily

i would compare something like max(rownum) = count(emp_id) so the max rownum = the total records for the particular id

select * from employee_table where
status = 'ACTIVE' and department IN ('JEWELRY','HOUSEHOLD')
where emp_id = 'a value'
and rownum = (select count(emp_id)
                         from employee_table et2
                         where status = 'ACTIVE' and department IN ('JEWELRY','HOUSEHOLD')
                         and et2.emp_id = emp_id)

abitoun,

rownum is a virtual column,  it is generated at query time.  So, the count(*) won't work unless count = 1

why not?rownum is virtual but the query would work if you put a value saying rownum = 3.

try it with any table


select * from your_table where rownum = 3

you'll get no results returned.



note, even if it did work, there is no sort to force the highest rownum to be the last row entered so the results would not be reliable.

on your last comment I totally agree there is not a way to tell if the last row was the last entered.

Ok, here is the tested one, my apologies for not posting the complete solution before, yes rowum is a virtual value, if you put it as a column in a subquery then you can query on it:
select * from
(select colum1,colum2...,rownum as myrow
from employee_table where
status = 'ACTIVE' and department IN ('JEWELRY','HOUSEHOLD')
where emp_id = 'a value')
where myrow = (select count(emp_id)
                          from employee_table et2
                          where status = 'ACTIVE' and department IN  ('JEWELRY','HOUSEHOLD')
                           and et2.emp_id = emp_id)

If you're going to select an arbitary row as "last" then you can eliminate the extra query and simply sort the data by the arbitrary rownumbers
and take the last one, simple way to do that is sort them descending order and then take the first one.


SELECT   column1, column2
  FROM   (  SELECT   *
              FROM   (SELECT   column1, column2, ROWNUM AS myrow
                        FROM   employee_table
                       WHERE   status = 'ACTIVE'
                           AND department IN ('JEWELRY', 'HOUSEHOLD')
                           AND emp_id = 'a value')
          ORDER BY   myrow DESC)
 WHERE   ROWNUM = 1;



however....  doing the extra ordering by an arbitrary numbering scheme has no real value, nor does querying the rownumber that equals the count(*).
doing either of those is effectively picking a random row.  In which case, it is best to just pick any one row.


SELECT   column1, column2
  FROM   employee_table
WHERE   status = 'ACTIVE'
   AND department IN ('JEWELRY', 'HOUSEHOLD')
   AND emp_id = 'a value'
   AND  ROWNUM = 1

Thank you for  the insight, I am glad you were able to simplify the code i provided.

Yes,  I am using ORACLE.  You are recommending I re-submit the question to the ORACLE database zone 'SQL Syntax'?  So none of the recommened solutions wil work?

however, since we've got your attention.  Can you answer the most important question above?

That is...  is there anything on each row or in another table that links back to each row determining which one row is first and another row is last?
If not, your question cannot be answered.

I am attaching an example of my desired results and what my current query looks like

ordering by rownum like that has no meaning since it's a virtual column.


In your first example..
By what rule is Jewelry is last and Furniture is first?

In your 2nd example...
Why is clothing the last record and shoes the first?

If the assignment_status = 'ASSIGNED' then this is always the employee's current department.  The department with 'ASSIGNED' status is the department that I will always want to return.  For example, if an employee has two records, one with 'ASSIGNED assignment_status and one with 'PREVIOUS' assignment_status, I would like to return the 'ASSIGNED'.

In some cases, some employee have multiple records with assignment_status of 'PREVIOUS' for different departments and no 'ASSIGNED' status.   In this case, I would like to return, the latest entry for the 'PREVIOUS' assignment_status.  

In the attachment, rownum for the last one entered was always the highest rownum.  Since it is a vitural column, I guess it is just a coincident that the entry that is the latest assignment_status was the highest rownum.

yes, it was a coincidence, maybe even repeatable, but still not reliable.

so,  if you have multiple rows with ASSIGNED how do you decide which is the latest?
or if you have multiple rows with PREVIOUS and no ASSIGNED how do you decide which is the latest?

We will never have multiple rows with 'ASSIGNED' but it appears that there are multiple PREVIOUS with no ASSIGNED.  I was hoping rownum would help but I will check to see if there is a date that exists.  I'll get back with you in the A.M.

I am a novice when it come to PL/SQL.
In case there is a date that I could use.  What syntax would I use for the following cases?
If status 'ASSIGNED' exists return department  ( This one take  precedent over all other status').  If this status exists, I don't want to process the other status)

Else if multiple statuses return the one with the latest date.

How would I incorporate into a PL/SQL statement?

note again, this is just sql,  no pl/sql involved at all.