Trying to pull out last id from a table and group by a specific column

Question

It only gives me one record, i dont know whats the problem, if i just loop the whole query then it works

<cfquery name="messages_tb" datasource="appdb">
    SELECT * FROM messages
    WHERE receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
    and rt = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint">
    group by sender
</cfquery>

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

<cfquery name="messages_tb" datasource="appdb">
    SELECT * FROM messages
    WHERE sender IN ( <cfqueryparam value="#list.messages_tb_sender#" cfsqltype="cf_sql_integer" list="yes">)
    group BY mid
</cfquery>

<cfoutput>
    #messages_tb.mid#
</cfoutput>

and this is how the table looks

mid    sender   receiver  message
======|=======|==========|=============
1        2         1        Hello
2        2         1        Hey
3        3         1        xyz
4        2         1        random text

I want data from only mid no 3 and 4

You have to loop it. What you are doing in that code will take the first element in the result set only. Also do not use select * even if you are receiving all columns. — Paul, Jul 04 '19 at 08:33
As Paul said, your cfoutput is missing the "query" name. However, your sample data looks wrong because the 2nd query wouldn't return ONLY mid=3 or mid=4. — SOS, Jul 04 '19 at 18:51
This could be written as a single query instead of two + looping. — Scott Jibben, Jul 04 '19 at 18:52
1. The "rt" columns is missing from your sample. Please update the sample to include all of the relevant columns 2. Please provide an example of the actual results AND the *expected* results. — SOS, Jul 04 '19 at 22:54

score 1 · Answer 1 · edited Jul 04 '19 at 18:06

If you only need mid no: 3 and 4 data, then the query will be like this...

<cfquery name="messages_tb" datasource="appdb">
  SELECT mid, sender, receiver, message FROM messages
  WHERE receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
  and mid in <cfqueryparam value="3,4" list="yes" cfsqltype="cf_sql_integer">
</cfquery>

If you need to pull out last id from a table and group by a specific column. I assume mid is unique according to the table output, then we may no need group by mid

<cfquery name="messages_tb" datasource="appdb">
  select mid, sender, receiver, message FROM messages
  where mid in (
    select top 1 mid from messages
    and receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
    order by mid desc
    )
</cfquery>

FYI: As is see from table output there is no column named rt

SOS · Answer 2 · 2019-07-04T20:10:59.453

(Too long for comments...)

To answer your question, the reason a single value is displayed is because the cfoutput is missing a "query" name, as Paul pointed out. Without a query name, cfoutput defaults to displaying the first value - in the first row. If you want to display all rows in the query, you must add the "query" attribute.
```
<cfoutput query="messages_tb">
    #messages_tb.mid#
</cfoutput>
```

However, you don't need two separate queries. A simple JOIN would return the same results in a single query.

SELECT DISTINCT m.Mid
FROM  messages m INNER JOIN 
      (
        SELECT DISTINCT sender 
        FROM   messages
        WHERE  receiver = <cfqueryparam value="1" cfsqltype="cf_sql_integer">
        AND    rt = <cfqueryparam value="1" cfsqltype="cf_sql_tinyint">
      ) s ON s.sender = m.sender

Having said all that, neither will return ONLY records MID = 3 and 4. So could you please elaborate?

A. What does the "MID" column represent?
B. Why should the query should only return values 3 and 4?

Based on your sample data, the 1st query would return:
```
sender
======|
2
3
```
Since ALL of the sample records have those sender values, your 2nd query would return all 4 records:
```
mid    sender 
======|=======|
1        2    
2        2    
3        3    
4        2    
```
So either your sample data is wrong or something is missing from your current queries.

Trying to pull out last id from a table and group by a specific column

2 Answers2