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 |
|
mxwebb00
Starting Member
17 Posts |
 Posted - 2015-02-12 : 18:07:07
|
| Ok doing a test need some help on one Question.Hard when you don't see the data and data Types.Not sure what to sum count and well group by is all the other not in the Aggregate.3. The MEMBER_STATISTICS table contains one row per post. For this purpose, each row contains the post's: Division - a way of grouping posts by their member size Department - the state in which the post is located PostNumber - unique post identifier Reinstated - count of members whose annual subscription had lapsed for at least two years but who have now subscribed for the current year Write a T-SQL statement to determine the top ten posts in each division based on the number of reinstated members, with a minimum of 50 reinstated members. Rank them by highest to lowest reinstated count. Return their Division, Rank, Department, PostNumber, ReinstatedANSWER******************Select MS.Division, Rank, MS.Department, MS.PostNumber, MS.Reinstated FROM MEMBER_STATISTICS MSWhere (MS.Reinstated > '50') |
|
|
mxwebb00
Starting Member
17 Posts |
Posted - 2015-02-12 : 18:34:17
|
| I am getting close any suggestion?Select MS.Division, Rank = ROW_NUMBER() OVER (PARTITION BY MS.Division,MS.Reinstated ORDER BY MS.Reinstated DESC), MS.Department, MS.PostNumber, MS.Reinstated FROM MEMBER_STATISTICS MSWhere (MS.Reinstated > '50') |
 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-02-13 : 08:47:32
|
| close, yes, Looks like you need an ORDER BY clause and a TOP() clause |
|
|
|
|
|
|
|
|
Job Question test!