[RESOLVED] Need help on sqlce select statement

[wiadus]

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

[Jape]

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.

[petevick]

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

Pete

[Jape]

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

[szlamany]

Another way to do that without top would be

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...

[Shaggy Hiker]

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.