select top 3 records for each group

[erickwidya]

Let's say i have records like at table.png

i want to select top 3 point for each respective Group

the result will be like result.png

can this be done via query?

thanks,

[szlamany]

I cannot see an easy way of doing that based on just the three columns you have posted.

I'm guessing you want three records from each group - even if the POINT values are exactly the same for all three - right?

[erickwidya]

I'm guessing you want three records from each group - even if the POINT values are exactly the same for all three - right?

Yes.

I cannot see an easy way of doing that based on just the three columns you have posted.

hmm..it suppose to make it easier than the real data

here it is

this a normal Calendar -> normal.png
this is work day at the office (assume there's alot holiday ) -> coming day.png
this is the day where the Employee is coming to the office -> working day A.png, working day B.png

Group
Accounting
- Employee A: 8 days not coming
- Employee B: 5 days not coming
- Employee C: 5 days not coming
- …

Marketing
- Employee D: 5 days not coming
- Employee E: 5 days not coming
- …

so each Employee will have its own "Working Card" like above and i would like to know
for each group the top 3 number of days for "NOT COMING", the largest last day one of them is ever come

for the example the result would be
Accounting: 8, 5, 5, 29 Nov 2007
Marketing: 5, 5, 28 Nov 2007 (not show here)

the calculation boundary is for all records in the respective table
(now i have from January 2005 to November 2007 ) not just in a month for each years

so i got 4 table for "working day", "coming day" ,"employee", "employee's group"

any insight?

[szlamany]

Ok - that's complex!!

I'm leading towards creating some temp tables or table variables to accomplish this. The reason for this is to get a "sequence #" into the table that is incremented automatically.

Create Table #Work (SeqNo int identity, EmpGroup varchar(10), NotComing int, Employee varchar(25))
Insert into #Work Select ... From ... Order by EmpGroup, NotComing

The important part of filling this table isthe ORDER BY - you want the "most" NOT COMING entries for each group to appear sorted towards the end of that group.

So you end up with this in your work table

SeqNo  EmpGroup     NotComing  Employee
  1    Accounting      1           M
  2    Accounting      2           L
  3    Accounting      5           C
  4    Accounting      5           B
  5    Accounting      8           A
  6    Marketing       1           W
  7    Marketing       1           X
  8    Marketing       1           Y
  9    Marketing       2           Z
 10    Marketing       5           D
 11    Marketing       5           E

So Accounting has 5 entries - with 3, 4 and 5 being the ones you want.

Marketing has 6 entries - 9, 10 and 11 the ones you want.

This query will give you the "max" and "min" seq for a group.

Select EmpGroup,Max(SeqNo),Min(SeqNo) From #Work Group by EmpGroup

Max(SeqNo)-2 is the "start of your range" that you want. Of course you need to make sure that Max(SeqNo)-2 is not less than Min(SeqNo) in case a group doesn't even have 3 values in this table.

This might work (I don't have SQL here to play with).

Select EmpGroup,Max(SeqNo)
   ,Case When Min(SeqNo)>Max(SeqNo)-2 Then Min(SeqNo) Else Max(SeqNo)-2 End...

I think you can CASE around MAX/MIN functions - if not you need to derive these values into another temp table.

At any rate once you get the MAX and MIN values you put them into a WHERE clause - or JOIN back to these.

Hope this helps!

[erickwidya]

thx sz let me try to understand it first ..and will give the result for it..

sorry been busy lately

[erickwidya]

hm..i wonder wether if it can do via Query only?

just curious about the 'SQL syntax'

[szlamany]

I don't believe so - as the value itself doesn't determine if the row gets selected. It's simply the top 3 - and that implied a sequence # is required.

The only step that is taken is to build that temp table. If you can use a stored procedure it can all happen in a single SPROC.

Once you have the temp table you JOIN or WHERE back to it.

What is your question about the SQL syntax??

[erickwidya]

What is your question about the SQL syntax??

what i mean is the syntax if it can be done using only SQL, it seem complicated enough so i can learn from it..but as u said, The only step that is taken is to build that temp table then i guess i end up try to using temptable

but if i found something, i'll post it..

thanks

[Mark Gambo]

Let's say i have records like at table.png

i want to select top 3 point for each respective Group

the result will be like result.png

can this be done via query?

thanks,

How about this:

SELECT XX.GroupName, XX.GDate, XX.Points
FROM GroupPoints AS XX
WHERE (((XX.Points) In (SELECT TOP 3  YY.POINTS
                          FROM GroupPoints YY
                          WHERE XX.GroupName = YY.GroupName AND 
                              XX.GDate = YY.GDate
                          ORDER BY  YY.GroupName, YY.Points )));

I would recommend that you rename the column called "Date" since it is a reserved word.

[szlamany]

Mark - seems like you are headed in a direction where a sub-query can handle this - but I'm not sure this is it yet.

Have you tested it?

[Mark Gambo]

Mark - seems like you are headed in a direction where a sub-query can handle this - but I'm not sure this is it yet.

Have you tested it?

I created a table and I added groups up to F and it seemed to work with the exception of the A group. With the A Group it was returning the lowest three. I use a similiar query in a SPROC with one on my apps and it works like a charm, but for some reason I couldn't get the top 3 of the A Group, hmmmm . . . . I figure the op can try and figure it out.

[szlamany]

If you go into BOOKS ONLINE and look at the EXISTS help I believe it suggests why there is an issue here.

I am not a 100% sure - but I believe that IN (sub-query) is executed once - and the WHERE clause compares ROW values to that (sub-query) result.

I also believe that EXISTS IN (sub-query) will actually test on a row-by-row basis that the WHERE condition is TRUE.

So with all that said - and again I'm not 100% sure of this - I think that EXISTS IN (sub-query to test if we are one of the top 3 values for the group) will always work.

But keep in mind that EXISTS IN (sub-query) will execute that sub-query for each and every row in the FROM tables. That is certainly not efficient on a large scale.

Although I think we have a bigger issue of data where the top 3 scores are the same in certain situations.

I always first look for the SET-of-data to supply the values for WHERE. In this situation the top-3-for-a-group is not in the SET-of-data of the original table. When I see this my first reaction is to derive this SET-of-data somehow - usually with a TEMP TABLE since I use SPROCS so often.

I just created a test table with these values:

EmpGroup   NotComing   Employee
---------- ----------- --------
Accounting 1           M
Accounting 2           L
Accounting 5           C
Accounting 5           B
Accounting 8           A
Marketing  1           W
Marketing  1           X
Marketing  1           Y
Marketing  2           Z
Marketing  5           E

Now look at this query -which orders the "most NOT COMING" for each empgroup and shows the count of how many employees fit that group.

select empgroup,notcoming,sum(1) "Number not coming" 
	from empgroup 
	group by empgroup,notcoming 
	order by empgroup,notcoming desc

returns...

empgroup   notcoming   Number not coming
---------- ----------- -----------------
Accounting 8           1  <- Want this row
Accounting 5           2  <- Want these rows
Accounting 2           1
Accounting 1           1
Marketing  5           1  <- Want this row
Marketing  2           1  <- Want this row
Marketing  1           3  <- Want just one of these rows!!

Note that there is "1" employee in accounting with an "8" - you want them. There are "2" employees with a "5" - you want both of them. That satisfies the 3 from that group.

Now marketing points out what I see to be the big problem with all this. The "5" employee (there is just 1) - you want them. You also want the "2" employee (there is just 1). But now you have the next score - a "1" - but there are three of them. You want just one of these rows.

Sorry for being so long winded here - but this issue of wanting just 1 of the 3 in that group immediately makes me think of how do we make them unique in some way so that we grab just one.

Thus my suggestion of loading a temp table with a sequence #. That sequence # makes this all work - since I can take the "first highest seq for a group" and assume I want that person (since I loaded-via-ORDER BY). I obviously want "first seq" - 1 and "first seq" - 2 for each group as well.