search cancel

FIND and Reserve ORDER BY Clause

book

Article ID: 237607

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Does the Find and Reserve feature have an Order By Capability? 
If not, is there a way to accomplish returning data based on the DESC order of a field in the table etc... ?

Use Case:
Find the most recent Encounter in the Encounter table.
Encounter.encounterID is a sequential number (the largest value is the most recent encounter).
The user queries the encounter table and is only interested in the most recent matching encounter.

Cause

Current product limitation

Environment

TDM 4.9.x 4.10.x

Test Data Manager Portal

Resolution

Currently the find a reserve future in TDM Portal does not have an order by capability.

To return the latest record in a table you can create a view to return the top 1 and do a sort on the column 

example

Below is a view with an order by  as an example:
CREATE VIEW [dbo].[Order_by]
AS
SELECT        TOP (1) ORDER_ID, ORDER_DATE, ORDER_SHIPPED_DATE, ORDER_STATUS_CODE, ORDER_TOTAL, CUSTOMER_ID, SHIP_TO_NAME, SHIP_TO_ADDRESS_ID, SHIP_TO_PHONE_NUMERIC, 
                         SHIPPING_OPTION_ID, PAYMENT_OPTION_ID, DISCOUNT_ID, COUPON_ID, FREE_SHIPPING_FLAG, CUSTOMER_COLLECT_FLAG, COLLECTION_WAREHOUSE_ID, GIFTWRAP_FLAG, GIFTWRAP_MESSAGE, 
                         CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, OBJECT_VERSION_ID, object_notes, ORDER_ID AS Expr1
FROM            dbo.ORDERS
ORDER BY Expr1 DESC

This returns the most current order in the Orders table based on the ORDER_ID column.

Another option is using the MAX function but since it's an aggregate function, I'm afraid that it'll require a GROUP BY clause.

Alternatively, it can be used also in subquery - similar to 
SELECT * from Encounter WHERE encounterID = (SELECT MAX(encounterId) FROM Encounter GROUP BY encounterId) 

- such query will return only the most recent record assuming that encounterId is unique.