I'm wondering, how would you use the TOP keyword to find the records that are top 10 in the number of times they appear in the database? I know it is very easy to find the top 10 simply by looking at one of the field values but how would you do it by ranking them with the function COUNT?<br><br>For example, what is wrong with the statement:<br>SELECT TOP 10 *<br>FROM Items<br>WHERE Date BETWEEN StartDate AND EndDate<br>ORDER BY COUNT(*)<br><br>(Assume StartDate and EndDate is defined.)<br>This is kind of frustrating so please help me out here...<br>Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using TOP keyword in a SQL statement. (need help)
- Thread starter SQLReaver
- Start date
- Thread starter
- #2
The code I have provided in the previous post is kind of inadequate. I'm wondering what's wrong with this code now:<br><br>SELECT TOP 10 *<br>FROM Items<br>WHERE Date BETWEEN StartDate AND EndDate<br>GROUP BY Item<br>ORDER BY COUNT(*)<br><br>Basically the database contains a lot of all items being purchased. Therefore I want to retrieve the top ten items by the number of times it is being logged in the table. Any help would be appreciated.
- Thread starter
- #3
Okay, I found out my problem is actually a conflict with group by and order by statements. I'm wondering if any of you could help me with this problem. The code that I now have is (which is wrong):<br><br>SELECT TOP 10 item, COUNT(item) AS cnt<br>FROM Items AS I1<br>WHERE date BETWEEN StartDate AND EndDate<br>GROUP BY item"<br>ORDER BY cnt"<br><br>Now assuming I get rid of the final statement of ORDER BY cnt, this code works. Unfortunately, that will not achieve the effect of getting the top 10 items ( since it obviously cut off the first ten records regardless of whether they have the greatest count or not ). I'm wondering how would I rearrange the code so that I can achieve the effect of getting the top 10 without having this wrong code of GROUP BY and ORDER BY in the same SELECT statement. Thanks once again.
In Oracle is working like this:<br>SELECT item,cnt FROM<br> (SELECT item,COUNT(*) cnt<br> FROM Items WHERE date BETWEEN StartDate AND EndDate<br> GROUP BY item) a<br>WHERE 10><br> (SELECT COUNT(b.*) FROM<br> (SELECT item,COUNT(*) cnt<br> FROM Items WHERE date BETWEEN StartDate AND EndDate<br> GROUP BY item) b<br> WHERE b.cnt>a.cnt)<br>ORDER BY 2,1;<br>I'm not using SQL Server, so I don't use TOP option.<br> <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>
Ed -<br>There's a simpler way to do this in Oracle:<br><br>SELECT item,cnt <br>FROM (SELECT item,COUNT(*) cnt<br> FROM Items <br> WHERE date BETWEEN StartDate AND EndDate<br> GROUP BY item<br> ORDER BY cnt desc)<br>WHERE rownum < 11;<br><br>
That's right Carp. My answer (made in 1 min, including formatting 
) doesn't look optimized (it uses too many subqueries). That's a good place for using rownum pseudocolumn. Once again, Carp was number 1. <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br>
) doesn't look optimized (it uses too many subqueries). That's a good place for using rownum pseudocolumn. Once again, Carp was number 1. <p>Eduard Stoleru<br><a href=mailto:e_stoleru@yahoo.com>e_stoleru@yahoo.com</a><br><a href= > </a><br> - Thread starter
- #7
- Status
Similar threads
