Creating a Top One Link (Magic xpa 4.x)

Objective

How can we fetch only the top 1 record of a table using a Link Query?

Creating a Direct SQL task that fetches the top 1 record is easy, however, not always applicable.

For example: SELECT TOP 1 Employee_ID, Employee_Name FROM Employee ORDER BY Employee_ID DESC

Solution

The following steps are required:

1. Change the Change Tables in Studio database property to No.
   
   Manipulations to the table definition on the Magic xpa side are required; these changes should not be reflected in the real database structure.

2. Get the definition of the table, for example an Employees table.

3. Duplicate the column you need to do the Top 1 statement on, for example the Employee_ID column.

4. Place one of the column entries as column #1 in the Magic xpa structure.

5. Change the column's DB Column name property from Employee_ID to: TOP 1 Employee_ID AS "MyID"

6. Change the field number on all indexes using the Employee_ID field. Instead of using the first column, use the second instance of the column (the copied one).

7. Use this table entry in a Link Query operation – it will fetch the first / last record (according to the index direction).

Note: This table entry can only be used to read data; no DMLs can be performed on it.