So tired solving this problem, Help please

[vampyr]

This is my table for messages

I'm trying to pull data from only mid no 1 and mid no 4, basically my goal is to get the last message from every sender

First i created a query to pull all the message for a user, in this case receiver id is 1

<cfquery name="messages_tb" datasource="appdb">

    SELECT * FROM messages

    WHERE receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

then i created a list of every sender id

<cfset list.messages_tb_sender_uid = ValueList(messages_tb.sender)>

after that i did this

<cfquery name="messages_tb" datasource="appdb">

    SELECT MAX(mid) as last_id from messages

    WHERE sender IN ( <cfqueryparam value="#list.messages_tb_sender_uid#" cfsqltype="cf_sql_varchar" list="yes">)

    and receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">

</cfquery>

<cfset list.messages_tb_last = ValueList(messages_tb.last_id)>

<cfoutput>

    #list.messages_tb_last#

</cfoutput>

The problem is im getting only 1 row, i dont wanna use loop or output query. Is there any way to just create a list of last mid of every sender ?

[Charlie Arehart · Accepted solution]

If that last valuelist has only 1, then the messages_tb query has only one record.   Do a cfdump of the query  to confirm. So this is not a cfml problem but a sql one.

I would think you could do a simpler single select, whether with group by or subqueries, but a) I'd have to build out a test table to be sure my proposal was right and b) I'm on my phone.

My guess would be that this could work:

SELECT   sender, MAX(mid)

FROM      messages

GROUP BY sender

But if not, it could help if you used querynew and related functions to create a simulated query result, against which folks could then try to do a qofq in cfml, and in cffiddle.org or trycf.com, even from a phone. (Sure, we could "just do it". I'm proposing how you could help us help you.)

It may also help to know what dbms/version you're using, as some problems are best solved using db-specific features--as long as you don't favor cross db compatibility over efficiency. For instance, windowing function if in more recent versions of sql server can do amazing things.

[vampyr]

Thank you soooo much, it works like a charm!

Thanks a ton man, You just made my year This problem was eating me alive ! not anymore

[Charlie Arehart]

Very good to hear, and thanks for marking it as the answer.