CODEWITHSUNDEEP
phpmysql
S.M_Emamian
S.M_Emamian

Reputation: 17383

SQL Order By id and Count star not working

I would like to get number of all records and get last record : 

   $sql_count_sms = "SELECT count(*) as total,content,id FROM android_users_sms WHERE user_id=$id ORDER BY id DESC";
    $result_count_sms = mysql_query($sql_count_sms);
    $row_num_sms = mysql_fetch_assoc($result_count_sms);
    $num_sms     = $row_num_sms['total'];
    $last_my_sms = $row_num_sms['content'];

I can get number of records but I can't get last content record . It returns first record !

Where is my wrong ?

Below codes works fine, but I think count(*) is faster than mysql_num_rows .

    $sql_count_sms = "SELECT content,id FROM android_users_sms WHERE user_id=$id ORDER BY id DESC";
    $result_count_sms = mysql_query($sql_count_sms);
    $row_num_sms = mysql_fetch_assoc($result_count_sms);
    $num_sms     = mysql_num_rows($result_count_sms);
    $last_my_sms = $row_num_sms['content'];

Any solution?

Upvotes: 2

Views: 85

Answers (1)

ficuscr
ficuscr

Reputation: 7053

The grain of the two results you want is not the same. Without using a sub-query you can't combine an aggregate and a single row into the same result.

Think of the grain as the base unit of the result. The use of GROUP BY and aggregate functions can influence that "grain"... one result row per row on table, or is it grouped by user_id etc... Think of an aggregate function as a form of grouping.

You could break it out into two separate statements:

SELECT count(*) as total FROM android_users_sms WHERE user_id = :id;

SELECT * FROM android_users_sms WHERE user_id = :id ORDER BY id DESC LIMIT 1;

Also, specific to your question, you probably want a LIMIT 1 in combination with the ORDER BY to get just the last row.

Now, counter intuitively perhaps, this should also work:

SELECT count(*), content, id 
FROM android_users_sms 
WHERE user_id = :id 
GROUP BY id, content 
ORDER BY id 
LIMIT 1;`

This is because we've changed the "grain" with the GROUP BY. This is the real nuance and I feel like this could probably be explained better than I am doing now.

You could also do this with a sub query like so:

SELECT aus.*, 
(SELECT count(*) as total FROM android_users_sms WHERE user_id = :id) AS s1 
FROM android_users_sms AS aus 
WHERE user_id = :id ORDER BY id DESC LIMIT 1;

Upvotes: 1

Related Questions