query help, select latest status? - Page 2
Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17

Thread: query help, select latest status?

  1. #16
    Senior Member
    Join Date
    Mar 2009
    Posts
    812
    Quote Originally Posted by tjburke79 View Post
    this has just been very frustrating, its obvious that {SELECT StatusID, MemberID, Text FROM Status ORDER BY StatusID DESC LIMIT 1} will give me the latest status.

    there could be 100's of status's per member id.

    i need to select only the latest status from my friend, i want to do this all in one query

    not

    select my friends

    loop {

    select latest status

    }
    - Join the tables
    - Group by member ID
    - Order by status ID
    - Limit 1

    Give it a shot.
    Declare variables, not war.

  2. #17
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,657
    That will however be incorrect SQL in direct violation of the standards and only executable using mysql in "quirks mode" or whatever their non-strict mode is called.

    If you have a group by clause, each and every selected field must either
    - be part of the group by clause
    or
    - be part of an aggregate function.

    Moreover, even if you disregard proper SQL and try to use the group by some stuff only in combination with order by, the above would possibly get statuses from two friends - before the LIMIT 1 clause, which leaves you with 1.

    To get the id of the latest update from one specific friend
    Code:
    SELECT MAX(id) AS id, member_id
    FROM status
    WHERE member_id=@some_member
    GROUP BY member_id;
    To get the corresponding text, use a join
    Code:
    SELECT t.id, status.text, t.member_id
    FROM status
    INNER JOIN
    	(
    		SELECT MAX(id) AS id, member_id
    		FROM status
    		WHERE member_id=@some_member
    		GROUP BY member_id;
    	) t ON t.id = status.id
    And since the inner query uses the max aggregate function, it will be able to pull the highest id from any number of members in conjunction with the group by
    Code:
    SELECT MAX(id) AS id, status.member_id
    FROM status
    INNER JOIN friends ON status.member_id = friends.friend_id
    WHERE friends.member_id = @my_id
    GROUP BY status.member_id;
    which combined with part 2 ought to give you what you need.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •