CODEWITHSUNDEEP
sql-servert-sqlsortingcursor
Renaud is Not Bill Gates
Renaud is Not Bill Gates

Reputation: 2084

Sort data in a table using a cursor

we had a chalenge question in a test we did at school,that we to create a t-sql script which sort data in a table by the employees salary using a cursor without using another table or using the clause ORDER BY .

No one did it because as I said it was a challenge question, I'm trying to do it at home but unfortunately I didn't do much progress so any help will be more that appreciated.

Many thanks in advance!

Upvotes: 1

Views: 351

Answers (1)

Nico
Nico

Reputation: 1197

i go in line that it is absolutely the wrong way to order data this way - anyway, this approach does what OP asked for. And yes - the cursor is definetly superfluous, because you can run the inner query without the last WHERE clause to have an ordering without using ORDER BY. Only added it to go along with original question.

--CREATE TABLE salary( emplId int, salary int );
--INSERT INTO salary VALUES( 4, 150), (2, 100), (3, 200), (1, 10);

DECLARE @emplId INT;
DECLARE @runs INT = 0;

DECLARE sal CURSOR
FOR SELECT EmplId FROM "salary" 
OPEN sal

FETCH NEXT FROM sal INTO @emplId
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT 
        S."emplId", S."salary"
    FROM 
        "salary" AS S 
        CROSS APPLY ( SELECT COUNT( "salary" ) AS "successors" 
                  FROM "salary" 
                  WHERE "salary" < S."salary" ) AS Data 
    WHERE 
        Data."successors" = @runs 

    SET @runs +=1

    FETCH NEXT FROM sal INTO @emplId
END

CLOSE sal
DEALLOCATE sal;

Upvotes: 1

Related Questions