[SOLVED] Getting 1st item from many

[phpscott]

I (100% newbie and creator of ugly code while learning) am trying to output data to a web page that will show me the latest (date) activity for a specific field:

 

The source info would be as follows:

id, date, username, notes 1, 1/1/07, bill, broken screen 2, 1/1/07, mary, format hd 3, 1/2/07, bill, returned 4, 1/3/07, jane, lost document 5, 1/5/07, mary, returned 6. 1/6/07, mary, lost document

 

I want to get the latest activity for each username. I would like the output to be:

3, 1/2/07, bill, returned 4, 1/3/07, jane, lost document 6. 1/6/07, mary, lost document

 

I have seen the distinct option but every example I have found shows it working on just 1 field.

SO, I have tried making an array that would store the usernames as I process them and compare them to the previous ones used, skipping over the output loop if one is found.  The code that I made does not seem to make this happen:

//restrict data     echo '<hr align="center" width="50%" size="1" noshade><table cellpadding=2 border=1>'; echo " <tr>     <td>date </td>     <td>asset</td>     <td>location type</td>     <td>techname</td>     <td>userid</td>   </tr>"; $check[] = "startdata";      while($rows = mysql_fetch_row($result2)) { $donotuse="use"; foreach ($check as $value){ if($value == $rows[1]){ $donotuse="donotuse" ; // echo "$value is $donotuse <br>"; } // end if } // end foreach if($donotuse="use") {         echo "<tr>";         echo "<td>" . $rows[0]."</td>";         echo "<td>" . $rows[1]."</td>";         echo "<td>" . $rows[2]."</td>";         echo "<td>" . $rows[3]."</td>";         echo "<td>" . $rows[4]."</td>";         echo "</tr>"; $check[] = $rows[1]; } //ending if $check[] = $rows[1]; // overkill     } // ending while     echo "</table>";

 

[revraz]

DISTINCT should work fine, because only one field needs to be DISTINCT.  See if you can combine DISTINCT with a sort by date DESC and only display 1.

 

[PFMaBiSmAd]

DISTINCT operates on the ROWS in the result set and looks at what was SELECTED. It basically does a GROUP BY whatever was selected.

 

Your date values 1/1/07 will be almost impossible to order correctly or make comparisons. You need to use a mysql DATE data type.

[helraizer]

The DISTINCT method as mentioned change your date formats to an actual date format and use TOP.

 

So it'll be "SELECT TOP 1 * FROM `table` ORDER BY `date` DESC"

 

Sam

[phpscott]

The source info would be as follows:

id, date, username, notes

1, 1/1/07, bill, broken screen

2, 1/1/07, mary, format hd

3, 1/2/07, bill, returned

4, 1/3/07, jane, lost document

5, 1/5/07, mary, returned

6. 1/6/07, mary, lost document

 

I want to get the latest activity for each username. I would like the OUTPUT to be:

3, 1/2/07, bill, returned

4, 1/3/07, jane, lost document

6. 1/6/07, mary, lost document

What I feel like I am missing is that select DISTINCT Username only returns 1 column when I am wanting all 3:

--> Date, Username, Notes. 

 

I need to look up TOP to understand it but it seems that would also give me 1 result when in my example I am expecting 3 results.

--> the last ticket for each username

[helraizer]

The source info would be as follows:

id, date, username, notes

1, 1/1/07, bill, broken screen

2, 1/1/07, mary, format hd

3, 1/2/07, bill, returned

4, 1/3/07, jane, lost document

5, 1/5/07, mary, returned

6. 1/6/07, mary, lost document

 

I want to get the latest activity for each username. I would like the OUTPUT to be:

3, 1/2/07, bill, returned

4, 1/3/07, jane, lost document

6. 1/6/07, mary, lost document

What I feel like I am missing is that select DISTINCT Username only returns 1 column when I am wanting all 3:

--> Date, Username, Notes. 

 

I need to look up TOP to understand it but it seems that would also give me 1 result when in my example I am expecting 3 results.

--> the last ticket for each username

 

What is it that you want to order by what, in your database? Explain fully and I will try and help.