select top 3 records for each group

2 Attachment(s)

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,

Re: select top 3 records for each group

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?

4 Attachment(s)

Re: select top 3 records for each group

Quote:

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

Yes.

Quote:

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 :D

here it is

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

Quote:

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 :D) not just in a month for each years

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

any insight?

Re: select top 3 records for each group

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.

Code:

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

Code:

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.

Code:

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).

Code:

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!

Re: select top 3 records for each group

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

sorry been busy lately

Re: select top 3 records for each group

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

just curious about the 'SQL syntax'

Re: select top 3 records for each group

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??

Re: select top 3 records for each group

Quote:

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

Re: select top 3 records for each group

Quote:

Originally Posted by 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,

How about this:

SQL Code:

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.

Re: select top 3 records for each group

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?

Re: select top 3 records for each group

Quote:

Originally Posted by 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?

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.

Re: select top 3 records for each group

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:

Code:

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.

Code:

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.