Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
 Posted - 2010-02-10 : 17:02:39
|
WOW! I feel like I lost my SQL touch! I can't figure out a seemingly simple query.Here's the data.Score Table:scoreid deviceid username score date1 1 john 2098 1/1/2010 01:30:382 1 john 2098 1/1/2010 01:30:403 1 john 2098 1/1/2010 01:30:414 2 alan 2008 1/2/2010 16:45:255 2 alan 2008 1/2/2010 16:45:286 3 beth 6783 1/7/2010 13:10:567 1 alex 983 1/8/2010 08:00:068 1 john 3098 1/8/2010 10:50:42 <-- These johns are not the same9 4 john 5098 1/8/2010 10:50:42 <-- The result I need ordered by score desc, date asc, but elimiting duplicate entries and only the top score for each deviceid and username (this is because the deviceid is not related the username, instead to the computer/device used and may have another username. ie: in the data above john and alex played on the same computer/device). Duplicate entries will be considered any entries with the same deviceid, the same username and the same score within 1 minutescoreid deviceid username score date6 3 beth 6783 1/7/2010 13:10:569 4 john 5098 1/8/2010 10:50:428 1 john 3098 1/8/2010 10:50:424 2 alan 2008 1/2/2010 16:45:257 1 alex 983 1/8/2010 08:00:06 The columns I need are just username, score and date. I placed the other columns there for visual explaination of the proper location for each row.If someone can provide a query that actually removes any entries that have the same deviceid, same username, same score and within 1 minutes, that would be better.- RoLY roLLshttp://www.buysellmotors.comhttp://www.tikiloveroom.comhttp://www.rolyrolls.comhttp://iphone.rolyrolls.com |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-11 : 00:41:44
|
something likeSELECT columns.. FROM (SELECT ROW_NUMBER() OVER (PARTITION BY deviceid,username ORDER BY score desc, date asc) AS Seq,other columns... FROM Table)tWHERE Seq=1 ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2010-02-11 : 01:52:25
|
| WOW!!!!!!!!!!!!!You are a genius!Worked like a charm! (As far as I've tested all the possibilites that come to mind)I was actually using the select row_number() on the outside and putting a subquery in that. Guess I was doing it backwards!I'm going to look it up in the SQL Books, but in plain English terms can you tell me just a little about PARTITION BY, so i can compare my interpretation versus yours?BTW: Game was fixed to not include duplicates now, but I needed a way to work with data I currently have. You saved me.Thanks again!!!- RoLY roLLshttp://www.buysellmotors.comhttp://www.tikiloveroom.comhttp://www.rolyrolls.comhttp://iphone.rolyrolls.com |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-02-11 : 02:14:00
|
In my simple words:PARTITION BY controls when the row_number() has to start over again with value 1.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-11 : 02:31:15
|
quote:
Originally posted by RoLYroLLs
WOW!!!!!!!!!!!!!You are a genius!Worked like a charm! (As far as I've tested all the possibilites that come to mind)I was actually using the select row_number() on the outside and putting a subquery in that. Guess I was doing it backwards!I'm going to look it up in the SQL Books, but in plain English terms can you tell me just a little about PARTITION BY, so i can compare my interpretation versus yours?BTW: Game was fixed to not include duplicates now, but I needed a way to work with data I currently have. You saved me.Thanks again!!!- RoLY roLLshttp://www.buysellmotors.comhttp://www.tikiloveroom.comhttp://www.rolyrolls.comhttp://iphone.rolyrolls.com
welcome  ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Group By clause