CODEWITHSUNDEEP
sqlsql-server
Bill
Bill

Reputation: 761

Using Top in SQL Server 2012

I have a table Contacts, parent to table Activity. I would like to select the latest activity for each contact, but getting more than one row.

This is my query:

select top 30 
     * 
from 
     Contacts o, Activity d  
where 
     o.ID = d.contact 
     and d.ID > 401061 
     and Last_Action is null 
order by 
     d.activity_date desc

I think I need Top? but not sure how to implement here. Any help would be appreciated.

Upvotes: 1

Views: 70

Answers (6)

JamieD77
JamieD77

Reputation: 13949

Using a cte

WITH cte AS 
(
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY contact ORDER BY activity_date DESC) rn
    FROM   Activity
    WHERE  d.ID > 401061  AND Last_Action IS NULL 
)
SELECT TOP 30 * 
FROM Contacts o 
     JOIN cte d  ON o.Id = d.contact 
WHERE d.rn = 1
ORDER BY cte.activity_date DESC

Upvotes: 0

underscore_d
underscore_d

Reputation: 6794

Assuming you want the top 30 actions per contact, this is the exact kind of thing CROSS APPLY was invented for.

Something like the following - uncertainties are because I can't see an example of your data.

select
    *
from
    contacts
    cross apply (
        select top 30
            *
        from
            activity
        where
                contacts.id = activity.contact
                and 401061 < activity.id
    ) as _ca
where
    last_action is null -- Perhaps you could move this into the CA - but we don't know which table it's from
order by
    activity.activity_date desc;

edit

select top 30
    *
from
    contacts
    cross apply (
        select top 1
            *
        from
            activity
        where
            contacts.id = activity.contact
            and 401061 < activity.id
        order by
            activity.activity_date desc
    ) as _ca
where
    last_action is null -- assuming this is in table contacts
order by
    _ca.activity_date desc;

Upvotes: 0

Paurian
Paurian

Reputation: 1402

I think you want to use a subquery:

SELECT TOP 30 *
FROM
  Contacts AS o,
  ( SELECT
      contact,
      MAX( activity_date ) AS activity_date
    FROM
      Activity
    WHERE
      contact > 401061 AND
      Last_Action IS NULL
    GROUP BY
      contact
  ) AS d
WHERE
  o.ID = d.contact
ORDER BY
  d.activity_date

Upvotes: 0

Mat Richardson
Mat Richardson

Reputation: 3606

This is slight guesswork as I can't see what your tables look like in terms of columns, but perhaps this (or something like it) would work?

select top 30 * from Contacts o, 
(SELECT contact, max(activity_date) FROM Activity GROUP BY contact) d  
    where o.ID = d.contact And d.ID > 401061 
    and Last_Action is null order by d.activity_date desc

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32402

Here's a way using not exists that will work on most dbs. You're basically selecting each activity per contact where a newer activity does not exist (therefore it's the latest activity).

select top 30 * from activity a
join contact c on c.id = a.contact
where not exists (
    select 1 from activity b
    where b.contact = a.contact
    and b.activity_date > a.activity_date
) and last_action is null and a.id > 401061
order by a.activity_date desc

Upvotes: 1

Andomar
Andomar

Reputation: 238296

You can use row_number() to number each contact's activities. In an outer query, you can filter down to only the latest activity per contact:

select  top 30 * 
from    (
        select  row_number() over (
                    partition by o.ID
                    order by d.activity_date desc) as rn
        ,       *
        from    Contacts o
        join    Activity d
        on      o.ID = d.contact 
        where   d.ID > 401061 
                and Last_Action is null 
        ) as SubQueryAlias
where   rn = 1 -- Only last activity per contact
order by 
         activity_date desc

Upvotes: 2

Related Questions