Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are you trying to say you have a time series, with each row linked by from and to, so the first row would have a from equal to the to of the 2nd row?? –  Phil Sep 5 '12 at 21:25
    
More detail in your question would help us determine what you are attempting to do, and will allow us to provide the best possible answer. –  Max Vernon Sep 5 '12 at 21:46
    
i have chat database where from is fromUserId and to is toUserId and time is on which the message sent now i want to retrieve last 20 messages on the basis of from and to values. –  Omer Sep 6 '12 at 0:37

2 Answers 2

up vote 1 down vote accepted

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 ;
share|improve this answer
    
you are very close to what i want i have done this way SELECT * FROM Table1 WHERE fromUserId=@from OR fromUserId=@to let me know if it is correct way to do it –  Omer Sep 6 '12 at 11:33

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.