Q:

SQL Server CE: Insert row to the top of the table

Is this posible? The reason for this is that I'm using a table like a queue, poping the TOP(1) elements sequentially for processing. And I would like to insert some rows to be processed immediately. It's ce 3.5 by the way, thanks.

A:

You need a column to maintain order, either identity or a timestamp. Then you can do "select top(1) order by x desc" to get the most recent.

A:

Create an priority column on you database, then sort on it (along with your identity):
CREATE TABLE myqueue(priority INT NOT NULL, id INT IDENTITY NOT NULL PRIMARY KEY, payload VARCHAR(200) NOT NULL)

CREATE INDEX ix_myqueue(priority, id)

INSERT
INTO myqueue (priority, payload)
VALUES (0, 'Regular message')

INSERT
INTO myqueue (priority, payload)
VALUES (1, 'Urgent message')

SELECT id, payload
FROM myqueue
ORDER BY priority DESC, id DESC
TOP 1

id | payload
2  | Urgent message

DELETE
FROM mytable
WHERE id = 2

SELECT payload
FROM myqueue
ORDER BY priority DESC, id DESC
TOP 1

id | payload
1  | Regular message

DELETE
FROM mytable
WHERE id = 1

