Applying LIMIT to first instance of each item in a column

[davidcriniti]

Hi, I'm just wondering if there's any way to use the Limit to limit a search to the first instance of multiple items in one column?

 

Eg, in a table called news I might have:

 

subject          content

general          general news1

general          generalnews2

faq                faq1

faq                faq2

general          generalnew3

 

 

When I do

 

 SELECT * FROM news ORDER BY content DESC LIMIT 0,1

(same if I substitute the word subject for content in the line above)

 

...I get just the first item.

Ie:

subject      content

general      generalnews1

 

How would I go about getting the first instance of each subject and its contents to appear?

 

Ie:

subject    content     

general  generalnews1

faq          faq1

 

 

 

Thanks,

 

Dave

[litebearer]

try DISTINCT  http://newsourcemedia.com/blog/select-unique-or-distinct-mysqlphp-queries/

[laanes]

$query = "SELECT * FROM news ORDER BY content";
foreach ($news as mysql_fetch_array($query) {

$noDuplicateValues = array_unique($news);

}

 

 

 

[davidcriniti]

Well I've been bashing away at this and have I guess made some progress. I tried using distinct and noduplicate values and the below is the coding I used when I came closest to success.

 

My actual application is using different values from the example I provided in my initial post, but basically it's the same thing. I'm looking in a table called editablepage for two columns: pagename and content. In the table there are multiple instances of each pagename, but I only want one instance of each pagename and it's corresponding content.

 

Using the below code I have successfully produced a table which has unique pagenames. This is what I want. However, the content column in my table is empty.

 

The code is below. Any tips on where I need to modify would be appreciated.

 

  <?php

	  mysql_connect ("localhost", "MYUSERNAME", "MYPASSWORD");
  mysql_select_db ("MYDATABASE");

// Get all the data from the "example" table

	$result = mysql_query("SELECT DISTINCT pagename FROM editablepage ORDER BY id DESC")
or die(mysql_error());

	echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>";
	echo "<tr bgcolor='#6175BE'> <th>Page Name</th> <th>Content </th></tr>";

while($row = mysql_fetch_array( $result )) {

      $pagename = $row['pagename'];
	    $content = $row['content'];

echo "<tr bgcolor='#CCCCCC'><td>";
echo $row['pagename'];
	echo "</td><td>";
		echo $row['content'];
	echo "</td></tr>";


}
	echo "</table>";

	?>

 

[litebearer]

My error  

 

It appears DISTINCT only returns the 'distincted' (its saturday morning so I can create a new word) column.

 

Try using GROUP BY

[davidcriniti]

Thanks for your response Litebearer,

 

I tried using GROUP By and again seem to have made some progress in the sense that I now have unique values in the pagename column and the corresponding content of each of those values in the content column.

 

As I understand, GROUP BY must be used with an aggregate function, so I've tried a couple - I tried MAX(date) * and also MAX(id) **, but when I update my table the most recent records are not necessarily those which show.  I also tried playing around with MIN instead of MAX, but couldn't seem to find any rhyme or reason. Occasionally with the max dates, values would update but not all the time. I wondered if the alphetetical order of the content may have had something to do with it, and tried inputting data in various ways to see if I could find a pattern, but could not.

 

* date is a field in my table which has a datetime datatype

** id is my primary key in this table, which is an autoincrementing integer.

 

Here is my current code. Any more tips?

 

 

    <?php

	  mysql_connect ("localhost", "MYADMIN", "MYPASSWORD");
  mysql_select_db ("MYDATABASE");

// Get all the data from the "example" table

	$result = mysql_query("SELECT pagename, content,  MAX(id) FROM editablepage GROUP BY pagename")
or die(mysql_error());

	echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>";
	echo "<tr bgcolor='#6175BE'> <th>Page Name</th> <th>Content </th></tr>";

while($row = mysql_fetch_array( $result )) {

      $pagename = $row['pagename'];
	    $content = $row['content'];


echo "<tr bgcolor='#CCCCCC'><td>";
echo $row['pagename'];
	echo "</td><td>";
		echo $row['content'];
	echo "</td></tr>";


}
	echo "</table>";

	?>

[davidcriniti]

Well it was definitely more good luck than good management (and I'm not really sure what some of the code means), but I found a solution. The solution came from this page:

http://stackoverflow.com/questions/2534253/sql-query-group-by-more-than-one-column-but-distinct

 

and this is the script I have which now works. I hope it's of use to someone in the future. Thanks again litebearer and laanes 

 

 

<?php

	  mysql_connect ("localhost", "MYUSERNAME", "MYPASSWORD");
  mysql_select_db ("MYDATABASE");

$result = mysql_query("SELECT b.pagename, b2.Latestdate, b.content  
FROM editablepage b 
    JOIN ( 
        SELECT pagename, MAX(date) AS Latestdate 
        FROM editablepage 
        GROUP BY pagename) b2 ON b.pagename = b2.pagename AND b.date = b2.Latestdate  ORDER BY pagename ASC" )
or die(mysql_error());




	echo "<table border='1' align='center' cellpadding='10' cellspacing='0' bordercolor='#000000'>";
	echo "<tr bgcolor='#6175BE'> <th>Page Name</th> <th>Content </th></tr>";

while($row = mysql_fetch_array( $result )) {

      $pagename = $row['pagename'];
	    $content = $row['content'];


echo "<tr bgcolor='#CCCCCC'><td>";
echo $row['pagename'];
	echo "</td><td>";
		echo $row['content'];
	echo "</td></tr>";


}
	echo "</table>";

	?>