Getting Top 10 results per month/year
-
Hi there. I'm having some trouble trying to do the following: I have some data in my DB: ID (PK) type creation_date What I want to do is to get the top x results by month/year of creation_date. I already have a query to count the number of ID's in some month/year, grouped by type, with a 'group by'. This would return the number of ID's with type T in month mm of year yy, for all months and years that appear on creation_date. Now I need to return the top X results in month 01/2007, top X results in month 02/2007 and so on. I already make this happen, with a stored procedure, but I'm using another stored procedure to get all the data to a XML file, and I'm not being able to call the stored procedure with the top from the stored procedure that will generate the XML... Any ideia, anyone? :) Joao
-
Hi there. I'm having some trouble trying to do the following: I have some data in my DB: ID (PK) type creation_date What I want to do is to get the top x results by month/year of creation_date. I already have a query to count the number of ID's in some month/year, grouped by type, with a 'group by'. This would return the number of ID's with type T in month mm of year yy, for all months and years that appear on creation_date. Now I need to return the top X results in month 01/2007, top X results in month 02/2007 and so on. I already make this happen, with a stored procedure, but I'm using another stored procedure to get all the data to a XML file, and I'm not being able to call the stored procedure with the top from the stored procedure that will generate the XML... Any ideia, anyone? :) Joao
-
That is total rubbish!!
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture) http://www.frankkerrigan.com/[^]
-
That is total rubbish!!
Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture) http://www.frankkerrigan.com/[^]
I concur.
Upcoming FREE developer events: * Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ... My website
-
How does this solve the problem? Did you even read his post?
Deja View - the feeling that you've seen this post before.
-
How does this solve the problem? Did you even read his post?
Deja View - the feeling that you've seen this post before.
Well, that will not solve my problem, because I do not know exactly how many months/years there could be in the first place. I do have something like this: select count(ID), type, year(creation_date), month(creation_date) from table1 group by year(creation_date), month(creation_date), type which returns the count per type/month/year... After that I just want to show top 5 types per month/year... As I said, I could do something like what I want with a stored procedure, with a loop foreach month/year, but I do not know how to run a stored procedure from inside another stored procedure... I've tried 'exec sp_getdata' from inside the other stored procedure, but that didn't work... Joao
-
Well, that will not solve my problem, because I do not know exactly how many months/years there could be in the first place. I do have something like this: select count(ID), type, year(creation_date), month(creation_date) from table1 group by year(creation_date), month(creation_date), type which returns the count per type/month/year... After that I just want to show top 5 types per month/year... As I said, I could do something like what I want with a stored procedure, with a loop foreach month/year, but I do not know how to run a stored procedure from inside another stored procedure... I've tried 'exec sp_getdata' from inside the other stored procedure, but that didn't work... Joao
-
Well, that will not solve my problem, because I do not know exactly how many months/years there could be in the first place. I do have something like this: select count(ID), type, year(creation_date), month(creation_date) from table1 group by year(creation_date), month(creation_date), type which returns the count per type/month/year... After that I just want to show top 5 types per month/year... As I said, I could do something like what I want with a stored procedure, with a loop foreach month/year, but I do not know how to run a stored procedure from inside another stored procedure... I've tried 'exec sp_getdata' from inside the other stored procedure, but that didn't work... Joao
Sql-Server 2005 introduced a bunch of new functions for ranking and windowing data. Try Using Ranking and Windowing Functions in SQL Server 2005[^]
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".