SQL Ordering Question - Newest 10 records

[TechBoyJK]

Hi Guys,

Sorry for another post, but I have a different question and I'm struggling to figure out the best way to do it.

I'm writing a messaging app, and when viewing messages, I want users to select how many messages are displayed. When viewing, the messages are displayed from oldest to newest. So as you scroll down, you are scrolling down to newer messages.

This is normal sorting order as older messages are displayed first (because they were entered first).

How would I display the newest 15 records? My thoughts are to Select TOP 15 and Order it descending, so that it grabs the last 15 records. But that leaves the record set in reverse order, displaying the newest message at the top, not the bottom. So I need to run that query, then apply another order by, DESC.. which would flip the record set around, grab the top 15 (newest 15) then flip the record set around again. But I can't get the syntax to work ..

Here's a shortened version of what I have.. what's wrong?

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) ORDER BY SENT_DATE DESC

 

[Ka0t1x]

You're sorting the data the same way twice..? Shouldn't the second one be ASC?

 

[TechBoyJK]

Yes, because I need the order to be reversed in order for the "top" command to grab the last records.. But when the results are displayed, I need those to be in reverse order.

 

[TechBoyJK]

Here's my logic.

Raw order

Date / User / Message

11/14/11 5:50pm / User 1 / How are you?
11/14/11 5:55pm / User 2 / Just fine. You?
11/14/11 5:57pm / User 1 / I'm good. Did you get the package?
11/14/11 5:59pm / User 2 / I did, and I put it on your desk. What is it?
11/14/11 6:14pm / User 1 / It's a new toy. Did you have fun at the party?
11/14/11 6:22pm / User 2 / Yea, it was awesome. Think we'll have another one soon?
11/14/11 6:50pm / User 1 / I sure hope so.



I want to select the last 5 records. (those that are starred)

11/14/11 5:50pm / User 1 / How are you?
11/14/11 5:55pm / User 2 / Just fine. You?
*11/14/11 5:57pm / User 1 / I'm good. Did you get the package?
*11/14/11 5:59pm / User 2 / I did, and I put it on your desk. What is it?
*11/14/11 6:14pm / User 1 / It's a new toy. Did you have fun at the party?
*11/14/11 6:22pm / User 2 / Yea, it was awesome. Think we'll have another one soon?
*11/14/11 6:50pm / User 1 / I sure hope so.


My logic is to reverse the order (desc) and select TOP 5

*11/14/11 6:50pm / User 1 / I sure hope so.
*11/14/11 6:22pm / User 2 / Yea, it was awesome. Think we'll have another one soon?
*11/14/11 6:14pm / User 1 / It's a new toy. Did you have fun at the party?
*11/14/11 5:59pm / User 2 / I did, and I put it on your desk. What is it?
*11/14/11 5:57pm / User 1 / I'm good. Did you get the package?
11/14/11 5:55pm / User 2 / Just fine. You?
11/14/11 5:50pm / User 1 / How are you?


Once I have those selected, I need to reverse the order again, so that when the results are displayed, they are displayed in chrono order, oldest to newest. That would leave me with

*11/14/11 5:57pm / User 1 / I'm good. Did you get the package?
*11/14/11 5:59pm / User 2 / I did, and I put it on your desk. What is it?
*11/14/11 6:14pm / User 1 / It's a new toy. Did you have fun at the party?
*11/14/11 6:22pm / User 2 / Yea, it was awesome. Think we'll have another one soon?
*11/14/11 6:50pm / User 1 / I sure hope so.

 

[KIAman]

You're sorting the data the same way twice..? Shouldn't the second one be ASC?

^^^^^^^^^^^^^^^^^^^^^^

 

[Tweak155]

If you sort DESC twice it will have the same result both times. Sorting the "same direction" doesn't reverse the results.

EDIT:

What happens if you take out both sorts?

 

[TechBoyJK]

^^^^^^^^^^^^^^^^^^^^^^

Are you sure? Either way, how would the sql be written? ASC or descending?

 

[TechBoyJK]

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) ORDER BY SENT_DATE DESC

Right now, this throws an "Incorrect syntax near the keyword 'ORDER'. " whether it's ASC or DESC

 

[TechBoyJK]

If you sort DESC twice it will have the same result both times. Sorting the "same direction" doesn't reverse the results.

EDIT:

What happens if you take out both sorts?

If I take out both, then it takes the first 15 records. I want the last 15 records. That's why I need the Order By Column DESC. The problem here, is that 'Order By Column DESC' works in grabbing the last 15 records, but then when I view that result set, it's in the opposite sort that I want.

Which is why I wanted

Select records
Desc
Grab top 15
Select those top 15 records
Desc

This works and grabs the bottom 15, but the records are display in the opposite order.

SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC

This breaks whether the 2nd Order BY clause is ASC or DESC. I get invalid Syntax at ORDER

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) ORDER BY SENT_DATE DESC

 

[quakefiend420]

not sure why you'd get a syntax error with that...try dropping into a table variable with the first sort, then select from there with the 2nd sort?

 

[KLin]

If I take out both, then it takes the first 15 records. I want the last 15 records. That's why I need the Order By Column DESC. The problem here, is that 'Order By Column DESC' works in grabbing the last 15 records, but then when I view that result set, it's in the opposite sort that I want.

Which is why I wanted

Select records
Desc
Grab top 15
Select those top 15 records
Desc

This works and grabs the bottom 15, but the records are display in the opposite order.

SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC

This breaks whether the 2nd Order BY clause is ASC or DESC. I get invalid Syntax at ORDER

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) SUB ORDER BY SENT_DATE DESC

You need to assign the subquery an alias. I just added SUB to it.

 

[Mark R]

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) ORDER BY SENT_DATE DESC

You need to give the "outer" SELECT statement the name of a "table". You can't just give it a select statement. You need to give the inner SELECT a name.

SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		WHERE recipient_id = "#session.myid#" and sender_id = "#member_b_id#"					

		ORDER BY SENT_DATE DESC) [b]subquery[/b] ORDER BY [b]subquery.[/b]SENT_DATE DESC

Notice the addition of the name (in this example, I've simply called it "subquery"), so that the SQL knows exactly what to sort with the second "ORDER BY". If you don't put it in, then it may get confused, and think it's got 2 "ORDER BY" commands for the same thing.


---

Gah. Beaten to it.

 

[KLin]

TOO SLOW, JOE! I mean, MARK R

 

[TechBoyJK]

Ok, thanks guys. Well, I guess I need to dump the full SQL. I posted an abbreviated version hoping to simplify things and I'm running into a different error.

	SELECT * FROM (SELECT TOP 15 *		

		FROM tbl_member_messages
		
		INNER JOIN tbl_member_messages_content on tbl_member_messages.content_id = tbl_member_messages_content.content_id 
		
		INNER JOIN members.dbo.tbl_member_profile_thumbnail on tbl_member_messages.sender_id = members.dbo.tbl_member_profile_thumbnail.member_id

		INNER JOIN members.dbo.tbl_member_core_contact on tbl_member_messages.sender_id = members.dbo.tbl_member_core_contact.member_id

		WHERE (
				(recipient_id = <cfqueryparam value="#session.myid#"> and sender_id = <cfqueryparam value="#member_b_id#"> 
				and recipient_archived <> 1 and recipient_deleted <> 1 <cfif show_spammed eq 1>and recipient_spammed = 1 <cfelse>and recipient_spammed <> 1  </cfif>) 
				or 
				(recipient_id = <cfqueryparam value="#member_b_id#"> and sender_id = <cfqueryparam value="#session.myid#"> 
				and sender_archived <> 1 and sender_deleted <> 1 <cfif show_spammed eq 1>and sender_spammed = 1 <cfelse>and sender_spammed <> 1  </cfif>)
				)

		ORDER BY SENT_DATE DESC) SUB ORDER BY SENT_DATE

error is "The column 'content_id' was specified multiple times for 'SUB'. "

Am I going to need to declare each selected column in the primary query? I'd rather not since I'm going to select everything anyway.

 

[KLin]

You've got a field called content_id in multiple tables in your sub query. You won't be able to do a select * in the subquery. You need to specify the columns, and give the content_id fields an alias.

 

[beginner99]

Just show the newest first. Makes a lot more sense in a messaging app anyway, IMHO. I also sort my emails that way. newest on top.

 

[TechBoyJK]

Just show the newest first. Makes a lot more sense in a messaging app anyway, IMHO. I also sort my emails that way. newest on top.

Thanks for the input. I actually agree with you. However, this isn't the inbox. The inbox is laid out the way you are suggesting.

This is the pageview for the message chain between two users. It's just like gmail's. If you click an email from someone, it takes you to the message chain. The oldest message is the first displayed, and as you scroll down, you get to the newer messages. The newest message is the last displayed, and below that is the reply text box.

Ok.. I got it to work! WOOO HOOOOOOO

imaginary drinks on me! :beer:

SELECT 	message_id, 
		sender_id, 
		recipient_id,
		new, 
		replied, 
		flagged, 
		sent_date, 
		viewed_date, 
		category, 
		body, 
		first_name, 
		middle_name, 
		last_name,
		name_display,
		thumbnail_url,
		thumbnail_file 

FROM 
                (SELECT #TOP# #maxrows# 
	
	        tmm.message_id,
		tmm.content_id as content_id_a,
		tmmc.content_id as content_id_b,
		tmm.sender_id, 
		tmm.recipient_id, 
		tmm.new, 
		tmm.replied, 
		tmm.flagged, 
		tmm.sent_date, 
		tmm.viewed_date, 
		tmmc.category, 
		tmmc.body, 
		tmcc.first_name, 
		tmcc.middle_name, 
		tmcc.last_name,
		tmcc.name_display,
		tmpt.thumbnail_url,
		tmpt.thumbnail_file
		
		FROM tbl_member_messages tmm
		INNER JOIN tbl_member_messages_content tmmc on tmm.content_id = tmmc.content_id
		INNER JOIN details_at_members.dbo.tbl_member_core_contact tmcc on tmm.sender_id = tmcc.member_id
		INNER JOIN details_at_members.dbo.tbl_member_profile_thumbnail tmpt on tmm.sender_id = tmpt.member_id

		WHERE (
				(recipient_id = <cfqueryparam value="#session.myid#"> and sender_id = <cfqueryparam value="#member_b_id#"> 
				and recipient_archived <> 1 and recipient_deleted <> 1 <cfif show_spammed eq 1>and recipient_spammed = 1 <cfelse>and recipient_spammed <> 1  </cfif>) 
				or 
				(recipient_id = <cfqueryparam value="#member_b_id#"> and sender_id = <cfqueryparam value="#session.myid#"> 
				and sender_archived <> 1 and sender_deleted <> 1 <cfif show_spammed eq 1>and sender_spammed = 1 <cfelse>and sender_spammed <> 1  </cfif>)
				)

		ORDER BY tmm.SENT_DATE DESC) SUB ORDER BY SENT_DATE