Equivalent of MS Access FIRST() function in SQL Server 2000
-
Hi,
Is there any equivalent of MS Access FIRST() function in SQL Server 2000?
I am looking for a workaround of FIRST function in SQL Server. Please help me out.
Bye,
Kamalanathan T.
-
There is no direct equivalent.
You could try using the TOP predicate to return the TOP 1
-
I've found that MIN() does just about the same job as FIRST().
MS Access:
SELECT customer_id,FIRST(order_date)
FROM customer_table GROUP BY customer_id
SQL SERVER:
SELECT customer_id, MIN(order_date)
FROM customer_table GROUP BY customer_id
Regards,Bob Monahon
-
No, MIN returns the minimum value, FIRST returns the first occurence.
-
Hello,
Yes of course MIN returns minimum,and FIRST returns first occurrence. However, faced with a "workaround" conversion requirement, I can't think of anything better than MIN() or MAX().
If the Access FIRST() function gives reproducible results, then it must use some internal ordering to determine the first row - perhaps arrival sequence. Here's how I'd approach a conversion from Access FIRST() to SQL Server:
1. For the initial conversion, replace all FIRST() functions with MIN() or MAX(). Benefit: All result sets will have the correct number of columns, and all the columns will have the correct data type.
2. Test. Determine the situations where the converted result is not acceptable. In those cases, which I hope will be few, you'll have to determine (by intuition or common sense) just what criteria Access does use to identify the first row.
3. For example, given an order_table like this:
row_iid IDENTITY -- the table key. Access equivalent: Autonumber
customer_id
order_date
order_amt
4. And an Access query like this:
SELECT customer_id, FIRST(order_date), SUM(order_amt)
FROM order_table
GROUP BY customer_id
5. If Access actually returns the order_date from the customer row that has the lowest key (row_iid), then this query will return the "FIRST" row for each customer:
SELECT first_row.* FROM order_table AS first_row
WHERE first_row.row_iid = (
SELECT MIN(scan.row_iid)
FROM order_table AS scan
WHERE first_row.customer_id = scan.customer_id
)
6. Depending on your preference, this "FIRST ROW" result set can be stored in a temp table, defined as a view, or included as a sub-query, like so:
SELECT first.customer_id, first.order_date, SUM(ord.order_amt)
FROM order_table AS ord,
( SELECT first_row.* FROM order_table AS first_row
WHERE first_row.row_iid = (
SELECT MIN(scan.row_iid)
FROM order_table AS scan
WHERE first_row.customer_id = scan.customer_id
)
) AS first
WHERE first.customer_id = ord.customer_id
GROUP BY first.customer_id, first.order_date
7. Personally, I'd use MIN() or MAX() as a workaround unless the results are completely unacceptable.
8. Also -- if you can find any better or less complex solution for a true "FIRST" implementation, it would be greatly appreciated.
Regards,Bob Monahon
-
I couldn't think of a good use for FIRST and LAST even when I was using Access. If I needed such functions then I was almost certainly looping through records using a DAO.recordset in any case.
I agree totally that there needs to be a rowId column and that you are best using MIN(rowId).
I did experiment with a correlated sub-query but these sorts of queries eat your server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply