group by

[jonniejoejonson]

mId dSent origId 

 

1 jan14 4

2 jan4 4

3 jan21 7

4 jan11 7

5 jan8 7

 

I want the mId’s of the smallest dSent grouped by originalId

 

However the following query:

$query = "SELECT mId, origId, MIN(dSent) FROM products GROUP BY origId";

 

Should produce:

mId 2

mId 5

 

But is producing

mId 1

mId 3

 

It is simply selecting the mId of the first row of the grouped origId…

 

Any ideason how to get it to do what I want… thanks to any responders.

[cooldude832]

That is because you didn't add an ORDER BY

 

Also the dSENT isn't an integer  so how it treats it is as a string (http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_min)  see the link in there

 

SELECT mID as mID, origId as OrigID, MIN(dSent) as The_Min from `products` GROUP BY origID ORDER BY The_Min

 

[jonniejoejonson]

Thanks cooldude, but that is still selecting the wrong results, but ordering them differently...

 

In the example above dSent is a datetime stamp not a string... i just wrote as a string to make easier to show... but your right it is wrong.

However it is still selecting the wrong reslusts. your continued support is much appreciated.

 

[cooldude832]

what are the results?

 

If they are backwards you can add a ASEC or DESC to the ORDER BY

 

 

[jonniejoejonson]

Table: messsages.

messageId

dateSent

originalMessageId

userId

1

jan14

4

3

 

2

jan4

4

3

 

3

jan21

7

3

 

4

jan11

7

3

 

5

jan8

7

3

 

 

(dateSent is a dateTimeStamp in mysql) just written it out to make it easier to see.

 

I want to select the messageId ’s of the most recent originalMessageId messages for userId’3’.

 

Therefore the table above should produce two messageId’s

3

1

 

Do you understand what I am trying to do? becuase it is quite hard to explain. thanks J

[cooldude832]

SELECT messageId from `messages` Where userId = '3' ORDER BY dateSent DESC LIMIT 0,2

[Barand]

SELECT p.mId

FROM products p

INNER JOIN (SELECT origId, MIN(dSent) as dSent FROM product GROUP BY origId) as x

    USING (origId, dSent)

[jonniejoejonson]

thanks again cool dude, that works if the table stays like that, but doensn't do what i need...

this following sql statement seems to work:

 

$sql = "select messageId,originalMessageId,dateSent

FROM messages

WHERE userID='3' AND dateSent = (select max(dateSent) from messages as messages2 where messages2.originalMessageId = messages.originalMessageId)";

 

not sure how quick it is thugh... anyway if you hve a better idea let us know... thanks J.

 

 

[jonniejoejonson]

thanks Barand...

is your code better than the code i managed to muster or are they about the same speed wise?

 

 

[Barand]

SELECT messageId from `messages` Where userId = '3' ORDER BY dateSent DESC LIMIT 0,2

 

Yes, that pulls the records 1 and 3 (although we really want 2 and 5), but wouldn't it be better to have a query that also worked for other sets of data, such as

[pre]

mId  dSent  origId

 

1    jan6      4

2    jan4      4

3    jan21    7

4    jan11    7

5    jan8      7[/pre]

[Barand]

d

thanks Barand...

is your code better than the code i managed to muster or are they about the same speed wise?

 

 

 

do you mean better = faster, or better = works?

[jonniejoejonson]

better faster, as my code above works aswell, however I am a bit of a newbie, and is quicker to use your code with a join or the way i have done it... thanks for your help. J.

[Barand]

I hadn't seen your working one, the last one you posted before I posted mine was

 

$query = "SELECT mId, origId, MIN(dSent) FROM products GROUP BY origId";

 

Anyway, without actually timing them I wouldn't really know - and I dont have your database to do that.