Get Records having the 2nd highest value

SQL to fetch all records having the 2^nd highest value in a given column. This is for Oracle(pl-sql) dialect

SELECT *

FROM some_table

WHERE val=

(SELECT MAX(val)

FROM some_table

WHERE val <>

(SELECT MAX(val) FROM some_table

)

);

If you need only the 2nd largest value, then you can use

SELECT MAX(val)

FROM some_table

WHERE val <>

(SELECT MAX(val) FROM some_table

)

Similar statement holds good for 2nd smallest as well. Use MIN instead of MAX.
.

Latest transaction for all users

Here is SQL to fetch the last transaction records for all users. Note that this is for Oracle (pl/sql)

SELECT *

FROM some_table t

INNER JOIN

(SELECT user_id,

MAX(record_id ) AS record_id

FROM some_table

GROUP BY user_id

) v

ON p. record_id = v. record_id;

Get OIM table field key

In the OIM API code, we need to use the field key rather than the actual database table field name. (example : "Users.First Name" or "Organizations.Created By")

All these are stored in the LKU table of OIM. Execute the below SQL to extract the complete list.

SELECT LKU_FIELD, LKU_TYPE_STRING_KEY FROM LKU WHERE LKU_TYPE='f' ORDER BY LKU_FIELD;

The part before the underscore in the LKU_FIELD indicates the table name.