The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 21, 2019, 01:45:33 AM

Login with username, password and session length


Pages: [1]
  Print  
Author Topic: Fun query that should be easy but isn't (MSSQL)  (Read 7880 times)
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« on: June 09, 2010, 11:25:23 AM »

Hi Everybody!

I have a query i'm working on and it should be simple but it's proving to be a pain in the ass.

What i'm trying to do:

I have a registration table that keeps a log of any time a user subscribed or unsubscribed to or from a list.  It's basically a big log table.  An action of 1 means subscribed and an action of 2 means unsubscribed.

Each record is inserted with the date/time of when the action occurred.

What I'm trying to get back is a list of the last action for each user.  I want one record per user and i want the action along with the date/time it occurred. 

I'll be building some other criteria in but that is the base of the query.

Anyone have a good solution for this?  I have tried joining a sub-query that picks out the last action of the user by it only returns 1 row rather than 1 row for each user.  I had thought the join would join each of the IDs from the outer query but because im limiting the sub query to 1 record returned it only returns the last action for a single user (row)

help?

Smiley



Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: June 09, 2010, 12:07:36 PM »

select user_id, user_name, subscribed_state from thelist order by action_date DESC limit 1
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #2 on: June 09, 2010, 12:10:16 PM »

that works for a single action from a single user but i need the last action from many users.  Give me a little credit here man Tongue

i actually JUST solved it with a correlated subquery grouping by the useridentity

works just like i want but i suspect it's going to be slow once the table gets all full of records.  we'll see.
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #3 on: June 09, 2010, 02:16:40 PM »

If speed is of the essence, then you might consider a stored procedure and extra field.

Consider if you have a single char field called "last_action" that is 0 or 1. Have a script run on append/updated that does

set last_action='0' where user_id = NEW.user_id; (the stored procedure syntax will be different based on the database engine)
set NEW.last_action='1';

then index on last_action and you've got it, be blazing fast.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #4 on: June 09, 2010, 03:07:43 PM »

so you're saying add a column to the table and when a particular user generates a new status update the col on every record for that particular user?  That way i can just select a distinct list of users and i can check that field.

clever idea, very clever.  i'll have to ponder that one.

Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #5 on: June 09, 2010, 03:32:15 PM »

Yep, close: essentially using the event vector of an "add" to the table to set a field to true ONLY for the most recent record. All previous would still be there, but all earlier actions would be false and the index could actually be last_action + last_name or something.

Actually you don't even need distinct, you could simply say

select * from mytable where last_action='1' order by lname

... because the trigger would be updating records for each person. In other words, the trigger would make sure that if I had 17 records and add another, then the 18th would be true and all 17 previous would be false. It'd be ridiculously fast because all the work is done when something is added, not at seek time.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #6 on: June 09, 2010, 03:40:18 PM »

oh ho! now i see what you are saying.  the last action is a yes/no as to whether or not it is the last action by that user, duh.

i like that much more better than the way i was thinking about it.

the other idea i had was simply a second table that had 1 record per user and their current status was updated each time a registration action occurred in the system.

that way there were no mass updates to the table and no queries sorting anything out at runtime.  just query that table and you know everyone's status.  zingo zango.

Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #7 on: June 09, 2010, 03:44:21 PM »

Zactly. And you could even have your indexed based on last_action + subscribed + last_name and then

select * from subscribed_table where last_action = 1 and subscribed = 1 order by last_name

and you'd get just the people you want to spam inform of your excellent new deals in no time and barely any processor.

Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #8 on: June 09, 2010, 03:47:52 PM »

I'll have you know this is in no way spam related, thank you very much.  I don't know WHERE you get these crazy ideas anyhow Mr. Perks.

Come to think of it, this would be the exact opposite of spam since its part of the logic i use to block sms messages to mobile phones that have opted out of a particular program.

Tongue
Logged

We can't stop here, this is bat country.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #9 on: June 09, 2010, 03:49:15 PM »

 ROFLMAO ROFLMAO ROFLMAO
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
KaptainKrayola
Keeper of Pie
Global Moderator
Lifer
*****
Offline Offline

Posts: 994



View Profile WWW
« Reply #10 on: June 09, 2010, 03:56:45 PM »

i have often wondered why nobody is utilizing SMS for spamming yet.  Phones are getting to where they have full on browsers and are like little PCs in your pocket.

From what i've seen working in the world of sms is that you can get away with pretty much anything if you know which providers to use so you could be sending out spam text messages in the millions before anyone even noticed. 

id say there must be no money in it but the fact that people still click links in spam emails suggests otherwise.
Logged

We can't stop here, this is bat country.
nop_90
Global Moderator
Lifer
*****
Offline Offline

Posts: 2203


View Profile
« Reply #11 on: June 09, 2010, 03:58:32 PM »

i have often wondered why nobody is utilizing SMS for spamming yet.  Phones are getting to where they have full on browsers and are like little PCs in your pocket.
They are
Logged
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #12 on: June 09, 2010, 04:00:40 PM »

 
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #13 on: June 09, 2010, 05:58:14 PM »

The risk is that SMS spamming can be equated to specific damages. Since many people pay per SMS message each spam message can be equated to a personal cost. Unlike email which is an implied cost.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jacktimo
n00b
*
Offline Offline

Posts: 6


View Profile
« Reply #14 on: June 13, 2013, 03:12:32 AM »

JUST solved it with a correlated subquery grouping by the useridentity




______________________________________________________________
banned
« Last Edit: June 13, 2013, 03:27:37 AM by Bompa » Logged
Pages: [1]
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!