CODEWITHSUNDEEP
sqlsql-serverstored-procedures
NomadTraveler
NomadTraveler

Reputation: 1084

SQL Stored procedure combining data from two rows

I am just after the best method to implement the following functionality.

Basically, I am querying a table and I want to fetch data from two different rows.

To explain a bit better :

Let's say Table 'myTable' has 3 columns 'StudentID', 'StudentName', 'StudentPercentage'.

I pass student ID as a parameter to a stored procedure which should return back the student's rank, along with the name and student id of the student whose rank is first.

To calculate rank, I use -

ROW_NUMBER() OVER (ORDER BY StudentPercentage DESC) AS Rank

So, if the parameter(Student ID) passed to the stored procedure is '1', the data is something like this (First Rank's student details + details of the student whose student ID was passed as an argument) :

StudentID  StudentName  StudentPercentage Rank
   5           A             96            1
   1           C             84            6

The data that I want to return back is :

A (First rank's student name), 96 (first rank's student percentage), 6 (rank of the student whose ID was passed as an argument).

I am unable to figure out a way to get the desired results.

Any help would be great!

Thanks.

P.S. - The code that I am writing is in C# connecting to SQL database. Though here, all I want to see is how this data can be returned by the stored procedure. In short, focussing on the SQL query.

Upvotes: 0

Views: 747

Answers (2)

LoztInSpace
LoztInSpace

Reputation: 11

Select the first student then get the passed in student with a correlated sub query.

Something like:

select
x.StudentName, x.StudentPercentage,
(select ROW_NUMBER() OVER (ORDER BY StudentPercentage DESC) AS Rank where studentID=@myStudentID)
from
(
select StudentName, StudentPercentage,ROW_NUMBER() OVER (ORDER BY StudentPercentage DESC) AS Rank
)x 
where x.Rank=1

You should probably use RANK() not ROW_NUMBER so you handle ties.

Upvotes: 1

GCaiazzo
GCaiazzo

Reputation: 131

I think you are looking for something along the lines of:

Select Top(1) StudentName,
  StudentPercentage,
  (YOUR RANKING SELECTION CODE HERE AS A SUBQUERY Where StudentId = @StudentId) as Rank
From myTable
Order By StudentPercentage Desc

Just use a subquery.

Upvotes: 0

Related Questions