(Resolved) Select top10 problems

jrogers · April 12th, 2006, 05:55 AM

I have the following data structure

Date name value
1-jan-2006 abc 200
1-jan-2006 bcd 400
etc
etc
31-mar-2006 abc 700

so I will have lots of data for each day, what I need to do is find the the min and max sum(top 10 value per day) .. It is fairly trivial to get the top 10, but I am struggeling with how to get this data on a per day basis. I am using SQL.

I was thinking that I would need to loop through the data and create a temp table containing the sum of top ten values on a daily basis, then requery this table to find the min and max.

can anyone suggest the best way to do this. can it be done in one select statement?

jrogers · April 12th, 2006, 09:34 AM

Problem Sorted.. I thought I would post my result here

Code:

SELECT 
    MAX(MyVal_Sum) 'Max Top 10', 
    MIN(MyVal_Sum) 'Min Top 10' 

FROM 
    (SELECT SUM(d.MyVal) MyVal_Sum,d.MyDate 
    FROM MyTable d 
    WHERE MyIdent in (SELECT TOP 10 c.MyIdent 
                FROM 
                    MyTable c 
                WHERE c.MyDate= d.MyDate 
                ORDER BYc.MyVal desc) 

    GROUP BY d.MyDate) temptable