Grouping by column to get the oldest entry

[sford999]

Basically I want to group a bunch of rows together to get the oldest entry which is a unix time stamp, so I can compare that to another variable.

 

I would like to know if this query is correct to get the oldest entry in "table.replyUnixTime"?

SELECT table.*, MAX(table.replyUnixTime) AS lastReply FROM table GROUP BY table.replyId ORDER BY lastReply DESC

Thanks

[Jacques1]

This makes no sense, and we can't really help you based on a few column names and a vague description.

 

What do the rows in “table” (I hope that's not really the name) represent? How would an example row look like? What would the expected result look like?

 

Also note that storing Unix timestamps is  bad idea. MySQL has actual data types for dates and timestamps: The DATE, DATETIME and TIMESTAMP types. Use them.

[jazzman1]

I would like to know if this query is correct to get the oldest entry in "table.replyUnixTime"?

 

 

Yes, it's correct! This statement should return the maximum index selectivity of any index defined on a table grouping by "replyId". Don't use the odious star "*" symbol in the selecting list.   

 

EDIT:

 

 

This makes no sense...............

What do you mean?

Edited July 2, 2014 by jazzman1

[Psycho]

Actually, I don't think it will necessarily return what he is wanting. That query will return "one" record for each unique replyID along with the value of the oldest replyUnixTime value. When you do a group by, there is no way to determine from which record in the grouped set is being used for the ungrouped fields. I.e. just because you use a modifier to get the oldest replyUnixTime for the grouped records doesn't mean the rest of the data on that result is from the same record. I believe you have to do a subquery and JOIN the table on itself.

 

http://dev.mysql.com/doc/refman/5.0/en/group-by-extensions.html

 

 

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in theGROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

Edited July 2, 2014 by Psycho

[Psycho]

I think this is what you need (not tested):

 

 

SELECT *
 
FROM table
 
JOIN (
      SELECT table.replyID, MAX(table.replyUnixTime) AS lastReplyTime
      FROM table
      GROUP BY table.replyId
     ) as lastReply
  ON table.replyID = lastReply.replyID
  AND table.replyUnixTime = lastReply.replyUnixTime
 
ORDER BY lastReply DESC

[DavidAM]

I want to group a bunch of rows together

Define "a bunch"

 

and while you're at it, define "group ... rows" (based on what?)

 

to get the oldest entry which is a unix time stamp

MAX(table.replyUnixTime) will get the youngest entry; since Unix time is constantly increasing, the MAX value will be the most recent.

 

 

I don't think anyone can say definitively what they query you presented will return. The GROUP BY will return a single row for each DISTINCT value of replyID in the table. The SELECT * will return all columns in a row. But since there are multiple rows with the same replyID and there is no selection criteria, there is no way to know which row is selected to "return all columns".

 

We really do need to know what the table structure is, and what it is you want from the table in order to provide any assistance at all.