StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The answer is similar to your <a href="https://stackoverflow.com/questions/18707818/sql-server-select-last-result-from-group-by/18707962?noredirect=1#comment27641024_18707962">earlier question</a>. However, now, it must take into account that the <code>@user</code> could be either user in the message.</p> <p>In this case, <code>row_number()</code> is not directly of help.</p> <p>Here are the differences. There is now a subquery to put the two users in "canonical" order. So, all messages between them have the same <code>User1</code> and <code>User2</code> (based on alphabetical order).</p> <p>The <code>partition by</code> clause uses these columns, so all messages are included in the <code>seqnum</code> calculation. The <code>Users</code> table now fetches information about the current user directly.</p> <pre><code>select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId, U.UserFullName, U.UserName, U.UserPhoto, row_number() over (partition by CM.User1, CM.User2 order by CM.[Date] desc) as seqnum FROM (select CM.*, (case when FromUser < ToUser then FromUser else ToUser end) as User1, (case when FromUser < ToUser then ToUser else FromUser end) as User2 from ConversationMessages CM ) CM CROSS JOIN (select * from Users U where @user = u.UserName ) U WHERE @user in (CM.ToUser, CM.FromUser) ) s WHERE seqnum = 1 ORDER BY s.[Date] DESC ; </code></pre> <p>EDIT:</p> <p>The above returns the user information for <code>@user</code>. For the other participant:</p> <pre><code>select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId, U.UserFullName, U.UserName, U.UserPhoto, row_number() over (partition by CM.User1, CM.User2 order by CM.[Date] desc) as seqnum FROM (select CM.*, (case when FromUser < ToUser then FromUser else ToUser end) as User1, (case when FromUser < ToUser then ToUser else FromUser end) as User2 from ConversationMessages CM ) CM JOIN Users U on U.UserName <> @user and U.UserName in (CM.FromUser, CM.ToUser) WHERE @user in (CM.ToUser, CM.FromUser) ) s WHERE seqnum = 1 ORDER BY s.[Date] DESC ; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload