Query for fetching records from SQLite database on Blackberry

Question

I want to fetch data from SQLite database for Blackberry.

There is one table with four columns: ID, FROM, TO, TIME

I want to fetch record on the basis of FROM, and TO values.

Where condition 1:

FROM = [from value] AND TO = [to value]

2nd condition is:

FROM = TO AND TO = FROM

It should return the last 20 records.

Answer 1

It's not very clear what you are after. So, this guess may be wrong:

SELECT *
FROM Messages
WHERE ( fromUserID = @From AND toUserID = @To )
   OR ( fromUserID = @To AND toUserID = @From )
ORDER BY TimeSent DESC
  LIMIT 20 ;

Answer 2

This might work as an example:

CREATE TABLE Messages
(
    MessageID INT
    , fromUserID INT
    , toUserID INT
    , TimeSent DATETIME
);

INSERT INTO Messages VALUES (1, 10, 11, GetDate());
INSERT INTO Messages VALUES (2, 11, 10, GetDate());
INSERT INTO Messages VALUES (3, 11, 12, GetDate());
INSERT INTO Messages VALUES (4, 11, 10, GetDate());
INSERT INTO Messages VALUES (5, 11, 12, GetDate());
INSERT INTO Messages VALUES (6, 11, 10, GetDate());
INSERT INTO Messages VALUES (7, 11, 12, GetDate());
INSERT INTO Messages VALUES (8, 11, 12, GetDate());
INSERT INTO Messages VALUES (9, 11, 10, GetDate());
INSERT INTO Messages VALUES (10, 11, 12, GetDate());
INSERT INTO Messages VALUES (11, 11, 10, GetDate());
INSERT INTO Messages VALUES (12, 11, 12, GetDate());

DECLARE @t TABLE (MessageID INT);
DECLARE @From INT;
DECLARE @To INT;
DECLARE cur CURSOR FOR
SELECT fromUserID, toUserID
FROM Messages
GROUP BY fromUserID, toUserID;
OPEN cur;
FETCH NEXT FROM cur INTO @From, @To;
WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO @t
    SELECT TOP(2) MessageID
    FROM Messages
    WHERE fromUserID = @From AND toUserID = @To
    ORDER BY TimeSent DESC;
    FETCH NEXT FROM cur INTO @From, @To;
END
CLOSE cur;
DEALLOCATE cur;
SELECT * 
FROM Messages 
    INNER JOIN @t tt ON Messages.MessageID = tt.MessageID

This returns the 2 most recent messages for each conversation between fromUserID and toUserID. Change the TOP(2) to TOP(20) to get the 20 most recent conversations.

I'm sure this is not the most optimal way to do this; I believe it can be done in a set-based manner as opposed to the cursor-based, procedural manner shown here.