StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL: get latest (un)subscribeaction from table
text
Body
copied!<p>I have the following table:</p> <pre><code>ID (int) EMAIL (varchar(50)) CAMPAIGNID (int) isSubscribe (bit) isActionByUser (bit) </code></pre> <p>This table stores all subscribe and unsubscribe actions on campaigns for a user. These actions can be done by the user itself (isActionByUser = true) or by the administration desk (isActionByUser = false).</p> <p>I need to get the last action to determine if a user is subscribed or unsubscribed. But keeping in mind that when a user did an unsubscribe action from a campaign, it will have priority on other subscribe actions by the administration desk.</p> <p>I have found a <a href="https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group">nice solution</a> to get the lastest record grouped by EMAIL and CAMPAIGNID. But I can't figure out how I do incorporate the requirement that an isActionByUser = true, has absolute priority over records with isActionByUser = false. Also: when the administration desk does an unsubscribe action, it will have priority over a record with (isSubscribe = true and isActionByUser).</p> <p>Example data:</p> <pre><code>ID EMAIL CAMPAIGNID ISSUBSCRIBE ISACTIONBYUSER ----------------------------------------------------------- 1 a@aa.com 1 1 0 2 b@bb.com 1 1 0 3 c@cc.com 1 1 0 4 a@aa.com 1 0 1 5 a@aa.com 1 1 0 6 c@cc.com 1 1 1 7 c@cc.com 1 0 0 </code></pre> <p>The expected result would be:</p> <pre><code>ID EMAIL CAMPAIGNID ISSUBSCRIBE ISACTIONBYUSER ----------------------------------------------------------- 2 b@bb.com 1 1 0 4 a@aa.com 1 0 1 7 c@cc.com 1 0 0 </code></pre> <p>With the following query</p> <pre><code>select cs1.* from [TABLE] cs1 left join [TABLE] cs2 on cs1.EM_EMAIL = cs2.EM_EMAIL and cs1.EM_CAMPAIGNID = cs2.EM_CAMPAIGNID and cs1.id < cs2.id where cs2.id is null </code></pre> <p>I' m having the following result:</p> <pre><code>ID EMAIL CAMPAIGNID ISSUBSCRIBE ISACTIONBYUSER ----------------------------------------------------------- 2 b@bb.com 1 1 0 5 a@aa.com 1 1 0 7 c@cc.com 1 0 0 </code></pre> <p>Another approach:</p> <pre><code>SELECT * FROM [TABLE] cs WHERE id in ( SELECT top 1 id FROM [TABLE] ss WHERE cs.EMAIL = ss.EMAIL and cs.CAMPAIGNID = ss.CAMPAIGNID and ISSUBSCRIBE = ( select top 1 min(convert(int, ISSUBSCRIBE)) FROM [TABLE] sss WHERE cs.EMAIL = sss.EMAIL and cs.CAMPAIGNID = sss.CAMPAIGNID ) and ISACTIONBYUSER= ( select top 1 max(convert(int, ISACTIONBYUSER)) FROM [TABLE] ssss WHERE cs.EMAIL = ssss.EMAIL and cs.CAMPAIGNID = ssss.CAMPAIGNID ) ) </code></pre> <p>This will produce the following result:</p> <pre><code>ID EMAIL CAMPAIGNID ISSUBSCRIBE ISACTIONBYUSER ----------------------------------------------------------- 2 b@bb.com 1 1 0 4 a@aa.com 1 0 1 6 c@cc.com 1 1 1 </code></pre> <p>Which is also not correct. And I'm afraid performance will be a big rpoblem with this approach.</p> <p>So any ideas how I can solve this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload