81171

SQL Server 2008 Using RANK to Dynamically Number Rows

Question:

I have a stored procedure that pulls data for a SS Report. I need to include a Row Number column but I have never used RANK before and I'm gettig this error: Column 'WL.SSN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.. Can anyone help me with the syntax? Here is what I have so far.

SELECT WL.SSN, WL.LastName, WL.FirstName, WL.AddressLine1, WL.AddressLine2, WL.AddressLine3, WL.City, WL.State, WL.ZipCode, WL.Plus4, S.SeminarID, S.SeminarType, RANK = Count(*) FROM @tblWaitList WL INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid WHERE S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType AND RowNumber = COUNT(*) ORDER BY RANK

I have tried almost every approach to this from <a href="http://support.microsoft.com/kb/186133" rel="nofollow">http://support.microsoft.com/kb/186133</a> but I'm missing something.

Here is an example of what I'm trying to acheive...

Rank Au_Lname Au_Fname ---- -------------- ----------- 1 Bennet Abraham 2 Blotchet-Halls Reginald 3 Carson Cheryl 4 DeFrance Michel 5 del Castillo Innes 6 Dull Ann 7 Greene Morningstar 8 Green Marjorie

Answer1:

This should do:

SELECT rank() OVER (ORDER BY WL.LastName, WL.FirstName) as rank, WL.LastName, WL.FirstName FROM @tblWaitList WL INNER JOIN @tblSeminar S ON WL.SeminarGuid=S.SeminarGuid WHERE S.SeminarID = @SeminarID AND S.SeminarType = @SeminarType ORDER BY rank ;

Recommend

  • Android: playing audio files in /res/raw by file name
  • Writing Unittest for generic classes… best approach?
  • FluentMigrator Failed Migrations Don't Rollback?
  • FlexJSON Orders Alphabetically by Default
  • Microsoft Chart Controls for Microsoft .NET Framework 4.0
  • LINQ to Entities does not recognize the method 'System.Collections.Generic.Dictionary`2[System.
  • command line of process by name
  • Sending Content-Type application/x-www-form-urlencoded WSO2 ESB
  • SQL Server re-calculate or not?
  • Does the MySQL IN clause execute the subquery multiple times?
  • Get current user from inside the model in Sails
  • Strong vs Weak entities MYSQL
  • Counting problem C#
  • ZipList with Scalaz
  • Alamofire and Reachability.swift not working on xCode8-beta5
  • Which open source license has no forking [closed]
  • Question about instantiating object
  • Redux Form - Not able to type anything in input
  • Email verification using google app script and google forms
  • Bad request using file_get_contents for PUT request in PHP
  • Disable Enter in editText android
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Dynamically accessing properties of knockoutjs observable array
  • Read text file and split every line in MSBuild
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How do you join a server to an Active Directory (domain)?
  • embed rChart in Markdown
  • How does Linux kernel interrupt the application?
  • python draw pie shapes with colour filled
  • Are Kotlin's Float, Int etc optimised to built-in types in the JVM? [duplicate]
  • Running Map reduces the dimensions of the matrices
  • Reading document lines to the user (python)
  • Binding checkboxes to object values in AngularJs
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?