what to add in where clause to get the desired result?

Question

I am trying to get max(date) ,count(id) and *look_name for max(date)* from this table:

id | member_id | look_week | look_name            | is_pinned | date 

1  |   1       |   3       | the improviser       | yes       | 2013-11-19 21:57:04
2  |   1       |   2       | destined for stardom | yes       | 2013-11-19 21:56:00
2  |   1       |   1       | fashinably corporate | no        | 2013-11-19 21:54:00

This is my query:-

$sql="SELECT COUNT(id) as total_pins,MAX(pinned_date) as last_activity_date FROM pin_info WHERE member_id='1' AND is_pinned='yes'";

I am getting this array as out put.

//[total_pins] => 2
//[last_activity_date] => 2013-11-19 21:57:04
//[lookname_for_last_date] => i am stuck at this?

How can I manipulate this query so that i can get look_name for max(date) in this array?

Please consider using MySQLi with parameterised queries rather than string concatenation and escape_string. — Polynomial, Nov 19 '13 at 17:05
sorry but i dint understood what you said...will using that have some performance lag? — HIRA THAKUR, Nov 19 '13 at 17:08
There's no real performance hit, and it provides significant security improvements against SQL injections. Also, `mysql_*` functions are deprecated. See [this question](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1) for a full set of details. — Polynomial, Nov 19 '13 at 17:13
@Onaseriousnote [Here's the manual](http://php.net/manual/en/mysqli.prepare.php) — Kermit, Nov 19 '13 at 17:19
@Onaseriousnote In the question I linked, the second answer has an entire walkthrough of how to use it. — Polynomial, Nov 19 '13 at 17:22

score 1 · Accepted Answer · edited Nov 19 '13 at 20:03

Step 1: First, you have to select the MAX(pinned_date). I assume this is for the one particular member and is_pinned='yes'

Step 2: Then, you have to select the look_name where the pinned_date is equal to the max-date found above. You can do this by making Step 1 and inner query inside Step 2

Step 3: Finally, the query from Step 2 goes as the third column in your main query

    SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,
     (select look_name 
      from pin_info B 
      where A.member_id=B.member_id and A.is_pinned=B.is_pinned
        and pinned_date in (
         select max(pinned_date) 
         from pin_info C 
         where B.member_id=C.member_id and B.is_pinned=C.is_pinned
        ) AS lookname_for_last_date
     ),
       (
         select max(pinned_date) 
         from pin_info C 
         where A.member_id=C.member_id and A.is_pinned=C.is_pinned
        ) AS CHK_LAST_DATE

    FROM pin_info A
    WHERE member_id='1' 
    AND is_pinned='yes'

To check this, or to tweak it, see this SQL Fiddle here.

most of it is similar..This gives me the exact result.

 $sub_query="SELECT MAX(pinned_date) FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";

$sql = "SELECT COUNT(*) as total_pins, MAX(pinned_date) as last_activity_date,(SELECT look_name FROM pin_info WHERE member_id='$member_id' AND pinned_date=($sub_query)) as last_pinned_look FROM pin_info WHERE member_id='$member_id' AND is_pinned='yes'";

Does it give you a blank if you just run the SQL? Or are you trying it directly inside PHP? If the latter, maybe you are trying to pick up the column by name instead of by position? I've added a column name to the sql itself, in case that's the issue. Also, I've added a fourth column to help debug: it is the max date. You can remove it when you have it running right. — Darius X., Nov 19 '13 at 19:49
hey finally did it...its a little diff from yours..diff bieng in sub query..how can i show u the query.. — HIRA THAKUR, Nov 19 '13 at 19:56
The easiest way is to click on the SQL fiddle link in my answer, change the query that comes up. It will generate a new URL, that you can post here. Or, simply edit my answer and put the correct answer in there. — Darius X., Nov 19 '13 at 19:58

KeyNone · Answer 2 · 2013-11-19T17:50:53.810

0

I'd go for a subselect:

SELECT COUNT(id) as total_pins, MAX(pinned_date) as last_activity_date,
(SELECT look_name FROM pin_info WHERE pinned_date = (SELECT MAX(pinned_date) FROM look_info)) as lookNameForMaxDate
FROM pin_info WHERE member_id='%s' AND is_pinned='yes'

edited Nov 19 '13 at 17:50

answered Nov 19 '13 at 17:20

KeyNone

8,745
4
34
51

this gives me an error because we are using alias in where clause..what to do replace with last_activity_date? – HIRA THAKUR Nov 19 '13 at 17:26
@Onaseriousnote see edit. at least it gives me correct results, even though it's kinda ugly... – KeyNone Nov 19 '13 at 17:51
You may have to add your last where clause to the first subselect – KeyNone Nov 19 '13 at 17:52

what to add in where clause to get the desired result?

2 Answers2