Select top n records by categories (group by)

Shivsa,

First of all, t-sql does not offer 'limit' in order by clause. Secondly, there are columns not in group by such as task_type and caller_id.

Thanks for your quick response.

GT

What is the unique key for this table?

Is task_id unique or is it by group_id?

That is, is task_id unique or can the the same task_id appear for each group_id?

At any rate, try something like this:


SELECT *
FROM yourTable yt1
WHERE action_dt_tm IN (
      SELECT TOP 4 action_dt_tm
      FROM yourTable yt2
      WHERE yt1.[user_name] = yt2.[user_name]
      AND yt2.group_id = yt2.group_id
      ORDER BY action_dt_tm DESC )
ORDER BY group_id, user_name, action_dt_tm

will this work.
-----------
Select * from all_action
group by group_id, user_name
order by group_id, user_name, action_dt_tm  HAVING COUNT(*) = n

geotiger,

Given the table T1,

the query will look like this:

select t1.*
from t1 join
  (select a.group_id, a.user_name,  a.action_dt_tm from
    (select group_id, user_name, action_dt_tm from t1) a
    join
    (select group_id, user_name, action_dt_tm from t1) b
    on a.group_id=b.group_id and a.user_name=b.user_name and a.action_dt_tm <= b.action_dt_tm
    group by a.group_id, a.user_name,  a.action_dt_tm having count(*) <= 1)  x
    on x.group_id=t1.group_id and x.user_name=t1.user_name and x.action_dt_tm=t1.action_dt_tm
order by t1.group_id, t1.user_name, t1.action_dt_tm desc


Rgds.

VC

geotiger,

Sorry pressed 'Send'  too fast...

The parameter for the top records should be specified in the 'having  count(*)'  clause.

E.g.  having count(*) <= 4/8/etc.  for the first four and eight records.   The big subquery determines a combination of (user_name,  group_id and action_dt_tm) for the first N records and the result is joined with the original T1 table getting you all the columns from T1.  N is the parameter for 'having count(*)<= N.


Rgds.

VC

geotiger,

An alternative is:

select * from (
  select x.*,
       (select count(*) from t1 where group_id=x.group_id and user_name=x.user_name and action_dt_tm <= x.action_dt_tm) cnt
  from t1 x) y
where cnt <=  N,  where N is 2/4/6/..etc


But it may be slower than my first version.

VC

ScottPletcher,

It will work;  it will return the first most recent record.  I explained how it works in my second message.

VC

geotiger,

Sorry ,  I reversed the comparison in my secnd version.  It should be like this:

select * from (
  select x.*,
       (select count(*) from t1 where group_id=x.group_id and user_name=x.user_name and action_dt_tm >= x.action_dt_tm) cnt
      
  from t1 x)
where cnt <= 1


@ScottPletcher,

Your query is also fine... It will work.

VC

ScottFetcher,

Your query as well as my second version will be less performant on big data volumes then the 'ugly' join version as they run a correlated subquery for every row -- yours in the where part and mine in the select clause.

That's why I prefer the join version.

Rgds.

VC

But your subquery is doing essentially the same comparison, so I don't think there'd be a noticeable performance difference.  In fact, I'm almost certain that a method with a derived table also will be slower than my method.

I actually checked ;)

The merge join (my first query with a derived table)  is faster than the correlated subquery(mine  and yours) for about 1000 rows.  I analyzed the table.

VC

Thanks, VC and SP!

I will test on my system to see which one works better.

GT