SQL CE: Limiting rows returned in the query

Question

In SQL Compact Edition 3.5 , note that it is the Compact Edition I am talking about- Is there a way to limit the amount of rows to only 2? Something like using LIMIT or TOP. I really don't want to use anything with a SqlCEDataReader, or SqlCEResultSet. I want to do all the limiting in the query. Is this possible now? I have looked around and it doesn't seem so.

EDIT-

In response to Dave Swersky's request for data and using Min()/Max() on some columns as a means to get the top 2 lines, here is some sample (sterilized) data:

Line     Site          Function     Status
1010     Las Vegas     new          4
1020     DC            send         1
1030     Portland      copy         1
1040     SF            copy         1
1050     Portland      copy         1
1060     DC            send         1

*There are more columns than this but these are the significant ones.

Sorry for the lack of intuitive data (but the actual data is even less intuitive!), but for security i need to change the data.

So- i need to determine: what site the record was at in the preceding line to determine where it needs to be picked up.

The site on any given line (except the first line with function = 'new') corresponds to where the item is going next. So simply grabbing that site off the same line wont tell me where it came from. The status will always be 1 or 4. The 4 corresponds to a where it has been delivered already and so i dont want to include those records in the result. But it might be useful in getting the pickup site.

For this table of data i want the query to return the site corresponding to the line just above the first line with status 1. So- for this it would be Las Vegas.

Answer 1

This will depend on your data... if you have guaranteed-sequential IDs or dates you could use MAX with NOT IN subqueries to return a limited number of rows. Post some of your schema and I'll try to cook up an example.

Answer 2

Depending on what you need to do, you might be able to execute the full query using a DataReader, and only call it's .Read() method twice.

Answer 3

I never found an answer in the query itself. Is this for a mobile app or is this SQL CE on a desktop?

I don't really have a direct answer for you, but will share what I've observed on my own projects... My experience in using SQL CE in desktop apps is that there are situations where it's more performant to use your application code to do the filtering. So, if you find yourself writing a convoluted query, you may want to also look at writing the filters in your app code inside of data reader.Read() loop and compare results. I've found cases where this cut operations that were taking a couple of seconds down to sub-second.

I know it's not what you want to do, but I've found that SQL CE requires you optimize things a little differently from when you're running against server. There's no network latency to worry about, so the normal kinds of worries don't manifest.

Answer 4

Sql CE 3.5 supports the TOP expression: http://technet.microsoft.com/en-us/library/bb686896.aspx