0

I have a table that has the following information:

mesID is my identity seed

mesID | ToUser | FromUser | DateTime ------------------------------------- 91 | jason | krissy | 1/18/2013 12:10:23 PM 92 | jason | krissy | 1/18/2013 12:20:38 PM 93 | jason | krissy | 1/18/2013 12:35:14 PM 94 | jason | krissy | 1/18/2013 12:20:38 PM 95 | jason | krissy | 1/18/2013 12:35:14 PM 98 | jason | krissy | 1/18/2013 12:10:23 PM 101 | jason | krissy | 1/18/2013 12:20:38 PM 104 | jason | krissy | 1/18/2013 12:35:14 PM 109 | jason | krissy | 1/18/2013 12:54:11 PM

now keep in mind i have over 60 more messages that are lower mesID then 98. What I want to do is get only the last 5 messages, but keep them in the original order from oldest to newest message returned (mesID = 1 obviously would be the oldest).

logically I would think I would just get the top 5 returns and order it by mesID by DESC. Then after the 5 have been returned then re-order them by mesID AESC, but I have no clue how to do this.

So the end result i would get something like...

95 | jason | krissy | 1/18/2013 12:35:14 PM 98 | jason | krissy | 1/18/2013 12:10:23 PM 101 | jason | krissy | 1/18/2013 12:20:38 PM 104 | jason | krissy | 1/18/2013 12:35:14 PM 109 | jason | krissy | 1/18/2013 12:54:11 PM
Improve this question

3 Answers 3

Reset to default
3

perform a subquery:

SELECT * FROM TABLE WHERE mesid IN (SELECT TOP 5 mesid FROM TABLE WHERE ToUser like '%krissy%' or FromUser like '%krissy%' ORDER BY mesid DESC) ORDER BY mesid ASC
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

i guess i shoulda put my full question because I thought the following would work..
@eqiz like that above? you did say OR (not AND?)
3 
SELECT * FROM ( SELECT TOP 5 * FROM TABLE ORDER BY mesID DESC ) t ORDER BY t.mesID
Improve this answer

2 Comments

brykneval this only displays the top 5 unfortunately ordered the opposite way.
This is the simplest solution
1

This will get latest 5, assuming there are many records with toUSer and FromUser, for every user.

WITH recordList AS ( SELECT mesID, ToUSer, FromUser, DateTime, ROW_NUMBER() OVER (PARTITION BY ToUSer, FromUser ORDER BY mesID DESC) rn FROM tableName ) SELECT mesID, ToUSer, FromUser, DateTime FROM recordList WHERE rn <= 5 ORDER BY mesID
Improve this answer

4 Comments

this works, but I needed it in a standard sql method without using "with" but I still gave you a plus +1 thanks for feedback in showing a different method
@eqiz ok, i'm using CTE because it is supported in SQL Server as you tagged it along with your question :D
this is just too complex for the task
@Bulat yes, because I was overthinking. The above query works if you want to get Top 5 rows for each group of ToUser and FromUser. I'm pretty sure the table is not only for jason and krissy, there's more than them.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.