Returning First and Last Rows from Columns Containing Different Data Types

I want to return the first and last rows from a table. First and last assume a logical sort by datetime_col, a column expressed by the datetime data type, and key_col, which collects data in the integer data type and is the table's primary key. How do I return those rows?

Let's use the Orders table in the Northwind database for the solutions' examples because it's very similar to the table in the scenario you present. The data type for the OrderDate column is datetime, and the data type for the OrderID column is integer. OrderID is also the primary key. Consider a query that's supposed to return the order with the minimum order date (and within that date, the minimum order ID) as well as the row with the maximum order date (and within that date, the maximum order ID).

Solution 1: If you don't mind incorporating a T-SQL­specific feature, using the TOP option to retrieve the first and last rows is fairly straightforward, as the queries at callout A and callout B in Listing 1 show. The next obvious step is to union the two queries. Note that a UNION operation is more appropriate than a UNION ALL operation in this case because UNION removes duplicates and UNION ALL doesn't. You probably wouldn't want to return the same row twice, in the case where the first row is the last row (i.e., the table holds only one row). However, a statement that tries to union both queries, as Listing 1 shows, returns the following error message:

Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'UNION'

Listing 1 returns an error because T-SQL permits only one ORDER BY clause in a UNION query, and that ORDER BY clause must appear at the end of the query. Such an ORDER BY clause operates on the result of the UNION, not on each individual input query. You can easily circumvent this problem by using derived tables, as Listing 2 shows. Listing 2's query should return orders 10248 and 11077, which are the first and last orders according to the sort specifications of the problem.

Solution 2: If you need to deliver an ANSI-compliant solution, you can't use the TOP clause. But you can follow the TOP clause approach—in which one query obtains the first row and another query obtains the last—and union the two queries. In this approach, you perform the union only with ANSI-compliant elements. For example, to return the first row, you can write a query that uses two nested subqueries, as follows:

SELECT *FROM OrdersWHERE OrderID = (SELECT MIN(OrderID)    FROM Orders    WHERE OrderDate = (SELECT MIN(OrderDate) FROM Orders))

The above query returns the row in which the order ID is equal to the minimum order ID and the order date is equal to the minimum order date in the Orders table. Replacing the MIN() function with the MAX() function in both subqueries returns the last row. Listing 3 shows the full query.

Solution 3: To return the first and last rows, you can also use a totally different approach that incorporates joins and derived tables. One derived table called M contains one row with the minimum and maximum order dates:

(SELECT MIN(OrderDate), MAX(OrderDate)   FROM Orders) AS M(Min_Date, Max_Date)

Another derived table called DM contains the minimum and maximum order IDs for each order date:

(SELECT Orderdate, MIN(OrderID), MAX(OrderID)   FROM Orders   GROUP BY OrderDate) AS DM(OrderDate, Min_OID, MAX_OID)

Now, join Orders and M to return only the rows from Orders that have either the minimum order date or the maximum order date. Then, join the result to DM to return only the rows that have either the minimum order date and minimum order ID or the maximum order date and maximum order ID. Listing 4 shows the complete code.