MIN/MAX String Troubles

[thelizardking]

I would like to know how to get the first and last entry of a field. I could use MIN/MAX yes but these fields are strings and not numeric. MIN/MAX seem to count how long each string is, not where it is in a field. Any assistant with this will be much appreciated.

[Lars_Berg]

There is no concept of first or last in a relational database.

Can you describe what your data looks like and what result you expect from your query.

[thelizardking]

mysql_query("SELECT COUNT(pid) AS replies, pid, author, title, MIN(UNIX_TIMESTAMP(timestamp)) AS firstpost_timestamp, MAX(UNIX_TIMESTAMP(timestamp)) AS lastpost_timestamp FROM posts GROUP BY pid");

When I run this I return replies, which is the number of posts with the same PID (post ID). The PID itself. Author, Title and I split the timestamp to show the first Post and the Last Post timestamp by using MIN and MAX. I want to do the same with Author. I want to show the First author and the last author without using another query but that may not be possible 🙁

[[deleted]]

This query is not valid, it is not possible to select field values that are not in the GORUP BY clause.

Yes MySQL lets you do it, but mysql sucks.

The GROUP BY creates a group of records, and you cannot select one record from s group without specifying which record you want.

The only thing you can do is select the MIN and MAX value from ONE COLUMN of for each group, and then use a second query to fecth the rest of the data for each of those values.

[thelizardking]

<?php
$results = mysql_query("SELECT COUNT(pid) AS replies, pid, author, title, MIN(UNIX_TIMESTAMP(timestamp)) AS firstpost_timestamp, MAX(UNIX_TIMESTAMP(timestamp)) AS lastpost_timestamp FROM posts GROUP BY pid");
echo mysql_error();
while ($r = mysql_fetch_array($results)) {
?>
  <tr align="center">
    <td align="left"><a href="view_topic.php?topic=<?php echo $r['pid']; ?>"><?php echo $r['title']; ?></a></td>
    <td><?php echo $r['replies'] - 1; ?></td>
    <td><?php echo date("H:i, M d, Y", $r['firstpost_timestamp']); ?><br><a href="profile.php?profile=<?php echo $r['firstpost_author']; ?>"><?php echo $r['firstpost_author']; ?></a></td>
    <td><?php echo date("H:i, M d, Y", $r['lastpost_timestamp']); ?><br><a href="profile.php?profile=<?php echo $r['lastpost_author']; ?>"><?php echo $r['lastpost_author']; ?></a></td>
  </tr>
<?php
}

That works perfectly, if only I could find the first author, name it as firstpost_author and the last author, which i would name lastpost_author. Also, i will not switch from mysql.

[[deleted]]

"That works perfectly,"

Nope it doesn't, the data from that query is complete that just looks like it makes sense. 🙂

The only 'real' data you get is the count, the min and the max.

obviously it is not possible to fetch the author or the title in this query, because you are not group-ing on those. There could be a hundred titles for each pid, sql cannot know.

So the only way to do this is to run this query with only the count, pid, min and max, and then run two seperate queries to fetch the author and title for each set of pid,min and max values.

You can condense all that back to two queries using a temporary table, but then you'd need a thrid queri to get the data out in the right order.