[Resolved] Max distinct

[mystrymaster]

Alrright so I have 4 fields in a table

One of the is called territory

How can I select the last entry with different territories listed?

If this doesn't make sense here would be a sample

| ID | updater | updaterTime | terotory |
| 1 | username | 7:31 am | som |
| 2 | username | 9:31 pm | hun |
| 3 | username | 7:31 am | mer |
| 4 | username | 11:54 pm | som |
| 5 | username | 8:01am | mer |
| 6 | username | 6:31pm | hun |

So if this was my data how would I select records number 2,4,5?

[kevinsequeira]

try this
select max(id), territory
from tablename
group by territory

reg
kevin

[mystrymaster]

perfect that worked - now is there a way I could get the lates 3 from each teritory?

Sorry I know creaping functionality but it's something I just thought of 🙂

[bad76]

😕
i think the easiest way is to use two query:

The first one get distinct territory.
The second, for each territory, get three row, sort by data desc.

may be?

[mystrymaster]

allright interesting thing is happening - it is returning the max ID but the first set of data so with the sample data above I am getting

ID's 2,4,5 but the data for rows 1,2,3

any ideas?

[bad76]

umh...

$first="select distinct territory"

while ($row=mysql_fetch_row("$first"))
{      

        $second="select * where terriitory=".$row[0]." sort by data";
$row=mysql_fetch_row("$second");
echo $row;
$row=mysql_fetch_row("$second");
echo $row;
$row=mysql_fetch_row("$second");
echo $row;
}

is it?

[mystrymaster]

well that didn't work but it pointed me in the right direction thank you very much

here is the solution for anyone else

$county = mysql_query("select distinct teritory FROM signoff ORDER BY teritory");
While ($row = mysql_fetch_array($county)) {
				$getid = mysql_query("SELECT max(ID) FROM signoff WHERE teritory = '$row[0]'");
				$outID = mysql_fetch_array($getid);
					$getdata = mysql_query("SELECT signoff.updater, signoff.updaterTime, signoff.teritory, users.username FROM signoff, users WHERE users.ID = signoff.updater AND signoff.ID = $outID[0]");
					WHILE ($data = mysql_fetch_array($getdata)) {
					 	if ($i == 0) {
          		echo "<tr bgcolor='#ffffff'>";
          	} else {
          		echo "<tr bgcolor='#eeeeee'>";
          	}
          	echo "<td valign='top' class='text10'>$data[teritory]</td>\n
          			 <td valign='top' class='text10'>$data[updaterTime]</td>\n
          			 <td valign='top' class='text10'>$data[username]</td>\n
          			</tr>";

      	if ($i == 0) {
      		$i = 1;
      	} else {
      		$i = 0;
      	}
      } 
}