March 3, 2010 at 8:21 pm
A brief description:
We have a device/software version tracking table that contains the history of each application a user has run on their device. Each time the user upgrades the application, a new row is added to the table. We also keep track of a date and time of when they last used it.
Sample data:
CREATE TABLE foo_tracking(
id int IDENTITY(1,1) NOT NULL,
device_id varchar(200) NOT NULL,
application_name varchar(100) NOT NULL,
application_version varchar(50) NOT NULL,
last_used datetime NOT NULL,
userID int NOT NULL)
insert into foo_tracking values('bb_21000','AlphaApp','3.0.20','2009-12-10 16:52:49',72522)
insert into foo_tracking values('bb_21000','AlphaApp','3.0.32','2009-12-14 18:57:00',72522)
insert into foo_tracking values('bb_248f2','AlphaApp','3.0.19','2009-11-27 22:20:35',72444)
insert into foo_tracking values('bb_248f2','AlphaApp','3.0.20','2009-12-07 08:10:27',72444)
insert into foo_tracking values('bb_30505','AlphaApp','3.0.20','2009-12-19 00:04:59',72379)
insert into foo_tracking values('bb_39f8a','DeltaApp','1.0.11','2010-01-21 04:11:44',72372)
insert into foo_tracking values('bb_39f8a','AlphaApp','3.0.32','2010-01-25 09:46:21',72372)
Query requirement:
I'd like to write a query that selects all users who are running a specified application and version as their "last used" (or what would be their most recently used) app.
For example, if I specify the application as 'AlphaApp' and version as '3.0.20', then I want the query to return users 72444 and 72379. User 72522 should not be included in the result set because his maximum last used date is greater than the date on the row that he does have for the specified application and version.
Hopefully that makes sense and is enough information. If not, let me know and I will try to provide more. I appreciate any help.
Thanks,
Kevin
March 3, 2010 at 9:43 pm
See if this helps..
DECLARE @strapplication_name VARCHAR(100)
DECLARE @strapplication_version VARCHAR(50)
SET @strapplication_name = 'AlphaApp'
SET @strapplication_version = '3.0.20'
; WITH cteTable AS
(
SELECT ROW_NUMBER() OBER ( PARTITION BY application_name, application_version ORDER BY last_used DESC ) Row, *
FROM foo_tracking
)
SELECT *
FROM cteTable
WHERE Row = 1
AND application_name = @strapplication_name
AND application_version = @strapplication_version
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 3, 2010 at 10:08 pm
Thanks, Kingston, but no, that only gives me one row. The one for userID 72379 (who has the max last_used date for that application version of all users). The result set also needs to include the row from userID 72444.
I'll try messing around with what you have, but any other suggestions are still appreciated.
Thanks,
Kevin
March 3, 2010 at 11:13 pm
Oh, sorry i didn't test it. This will work..
DECLARE @strapplication_name VARCHAR(100)
DECLARE @strapplication_version VARCHAR(50)
SET @strapplication_name = 'AlphaApp'
SET @strapplication_version = '3.0.20'
; WITH cteTable AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY userid, application_name ORDER BY last_used DESC ) Row, *
FROM foo_tracking
)
SELECT *
FROM cteTable
WHERE Row = 1
AND application_name = @strapplication_name
AND application_version = @strapplication_version
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2010 at 10:55 am
Thanks, Kingston! It makes perfect sense when I see it, but I just couldn't think of it on my own.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply