MySQL & PHP - Select 6 rows, each with a different 'WHERE' clause

I'm pretty good with MySQL, but this is something I have never done. What I want to do is make an SQL code to select 6 rows, each with their own WHERE clause.

What I am trying to do is get 6 rows, and each will be the most recent "video" that was posted. there are 6 categories, so that's why I have 6 rows. I want it to pull the most recent by it 'id' number.

I'd do it with 6 different SQL queries, but I assume that would be slower (unless this is the only way to do this?)

From that small snippet, I would like to end up with is this:

2 --> 21

6 --> 16

8 --> 14 (Picks 14 since it's largest.)

Final Working Code

$sql="SELECT video_category, MAX(video_id) AS video_id FROM videos GROUP BY video_category";
$result=mysql_query($sql);
w开发者_如何学JAVAhile($rows=mysql_fetch_array($result)) {
echo $rows['video_category'] . " --> " . $rows['video_id'] . "<br>";
}

something like

select distinct category, video_id from table_name order by id DESC

If you have 6 categories in the db, you would get 6 rows, all having highest id in their category

Please share your table structure. Nevertheless, i think the following query should do the trick:

SELECT category_id, MAX(movie_id) most_recent_movie_for_category FROM movies GROUP BY category_id

Thanks for posting the table structure. This is just a simple GROUP BY with a MAX aggregate on video_id.

SELECT video_category, MAX(video_id) AS video_id FROM videos GROUP BY video_category;

You have two options:

1. Determine common WHERE clause that will result in what you need.
2. (probably preferred one) Make some query involving UNION (SELECT ... FROM ... WHERE ... UNION SELECT ... FROM ... WHERE ... etc.)

Let me know if you have any questions. I believe without your database structure it would be hard to help you more.