creating stored procedure for 'th' highest marks

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL-Server

CTE

, +

I've a table

SQL

create table marks(stdName nvarchar(30), marks float)

SQL

insert into marks
select 'std1', 98
union all select 'std2', 96
union all select 'std3', 95
union all select 'std4', 97
union all select 'std5', 93

I want to create stored procedure to select the row with 'th' highest marks as given input parameter.suppose if the input parameter is 3, it should display 'std2', 96 or if input parameter is 5, it should display 'std5', 93.

Posted 17-May-12 19:20pm

nischalinn

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2012-05-17T19:34:00

The procedure itself is quite straightforward, modify the statement as per requirements and then include inside a procedure, see CREATE PROCEDURE [^]

About the query, I would advice to separate the first column to two separate columns if the data describes different things. Concatenated columns should never be stored inside the database.

If the columns would be separate, then your query to fetch the row could be something like:

SQL

INSERT INTO Marks (strName, anotherfield, marks)
SELECT stdName, anotherField, marks
FROM  (
             select 'std' as stdName, 1 as AnotherField, 98 AS Marks
   union all select 'std' as stdName, 2 as AnotherField, 96 AS Marks
   union all select 'std' as stdName, 3 as AnotherField, 95 AS Marks
   union all select 'std' as stdName, 4 as AnotherField, 97 AS Marks
   union all select 'std' as stdName, 5 as AnotherField, 93 AS Marks) AS sub
WHERE sub.AnotherField = @procedureParameter

VJ Reddy · Answer 2 · 2012-05-17T19:41:00

Solution 2

If the requirement is to retrieve nth row from the Table then, the ROW_NUMBER function of Sql Server can be used for this purpose as shown below

SQL

CREATE PROCEDURE GetnthRow
    @nthRow nvarchar(30)
AS
WITH OrderedMarks AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY stdName) as RowNo, stdName, marks
    FROM marks
)
SELECT stdName, marks
FROM OrderedMarks
WHERE RowNo=@nthRow
GO

Posted 17-May-12 19:41pm

VJ Reddy

Updated 17-May-12 19:43pm

v2

Comments

Wendelius 18-May-12 1:47am

Good example!

VJ Reddy 18-May-12 1:54am

Thank you, Mika :)

creating stored procedure for 'th' highest marks

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

creating stored procedure for 'th' highest marks

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us