So, bassically, I need to find first message sent by operater and then client messages before next operaters message. If after one operaters message there is no clients answer, I need to insert row like "no clients anwer" or something like that.
And that is large table, so will be good if it is less steps.
I know how to order by multiple columns, I know how to find first operaters message (select min time where id...) but I don't know how to insert those rows where needed.
Hi, sorry for being vague, now I'll start from beginning and try to be more detailed. Here is the deal:
I have table for comunication between clients and operators, which contains phone, id (1=client, 2=operater) date, request, response... and some other (non important) rows.
It looks like this:
PhoneNumber Date Request Response ID
0123456 1.1.2011 some_txt some_other_txt 1
and so on....
I need to get this:
PhoneNumber Request Date Response Date
where last two rows are from clients, and two before them are operators.
So, I need to make it so I have first operators message with request for some number (by date) and response in one row (response is first message from client after that date).
I aimed to get that by left joining table to itself on number, but so if there is no response within wanted criteria, last two rows will be NULL (which I want), but I cant figure out how to join first value by date for every phone number with first response after that date from "right" table.
I tried to select distinct rows where only PhoneNumber is distinct, but that wasn't so successful. And if I somehow manage to do that, how to join it with first row from users messages...
Of course, even if I manage this, probably it isn't the best way.