How to present large dataset from a SQL Server query?

Help with SQL Server Query, Show Most Duplicate Value in a Column...?

• Hi! I have created a database based on movies. I want to create a query to show me the most popular genre, and the results will be based on the amount of titles in that genre that have a rating of more than 3. I already have created this query to show me the movies that have a rating more than 3: SELECT TitleName, GenreName, Rating FROM Movies WHERE (Rating >= 3) I want to alter this query to only show the genre with the highest ratings by counting up the most ratings over 3 or equals 3 and only displaying them.

• Answer:

  Well, before you can write a query that finds the most popular genre, you first need to be able to count how popular each genre is, right? You do that with GROUP BY and COUNT: SELECT GenreName, COUNT(*) as count FROM Movies GROUP BY GenreName; That will list all Genre's, and how many times they appear: Horror 7 Comedy 12 Romantic 8 XRated 187 But wait, I left out your WHERE clause, gotta have that: SELECT GenreName, COUNT(*) as count FROM Movies WHERE rating >= 3 GROUP BY GenreName; You'll get the same results, but the numbers will change: Horror 3 Comedy 9 Romantic 7 XRated 2 Getting closer, but how do you pick Comedy out of that list? There is more than one way to do that, but one way is to sort the list: SELECT GenreName, COUNT(*) as count FROM Movies WHERE rating >= 3 GROUP BY GenreName; ORDER BY COUNT(*) desc; Now you have: Comedy 9 Romantic 7 Horror 3 XRated 2 You could just stop there, and in your code, just take the first item in the result set. But it's bad practice in SQL to return a whole bunch of rows if you know you are going to discard most of them. So how do we get just the one row we want? That depends a bit on what flavor of SQL you are using. I'll use MySQL as an example, but you can do the same sort of thing in most any version of SQL. In MySQL, you just add a limit clause: SELECT GenreName, COUNT(*) as count FROM Movies WHERE rating >= 3 GROUP BY GenreName; ORDER BY COUNT(*) desc LIMIT 1; Now you have: Comedy 9 You probably don't care about the actual count, so drop it from the SELECT clause: SELECT GenreName FROM Movies WHERE rating >= 3 GROUP BY GenreName; ORDER BY COUNT(*) desc LIMIT 1; Yeah, I know, I could have just given you the final answer. But in the real world (I write SQL for a living), I can almost never write exactly what I need on the first cut for non-trivial queries. I always start with what I do know how to do, then figure out how to get closer to what I really need to do. It's a strategy that works for me.