[RESOLVED] Need help on sqlce select statement

hi iam having error with this select statement which works with sql server "select top 1 holidaypay from shiftpaid order by paidid desc". The error is the top, so if anyone could help. What I want to do is to select a column from a last row in a table. Thanx

Try select top(1) holidaypay from shiftpaid order by paidid desc. If i remember correctly in sqlce you need to use the () for some reason.

Hi,
pretty sure 'top' is not supported - take a look at 'books online'

Pete

It is since 3.5, so provided u are using the latest version it should work, check this link

Another way to do that without top would be

Code:

---

select holidaypay from shiftpaid
    where paidid=(select max(paidid) from shiftpaid)

---

Although whether CE supports sub-queries in that fashion is a guess on my part...

Now that I look at it, since you only want a single field, another way to do this would be to use ExecuteScalar with the query:

SELECT Holidaypay FROM shiftpaid Order BY paidid desc

This would return the first column in the SELECT statement from the first row in the records returned. With the ORDER clause in there, the first row will be the one you want, and with HolidayPay being the only field, then that will be the data.