CODEWITHSUNDEEP
sql-servert-sql
sikender
sikender

Reputation: 5921

Set below query into asc or desc order?

SELECT * FROM (SELECT ROW_NUMBER()
over
     (
        ORDER BY
          CASE WHEN @SortExpression ='Country_id' THEN Country_id END,
          CASE WHEN @SortExpression ='Country_name' THEN Country_name END,
          CASE WHEN @SortExpression ='Country_region' THEN Country_region END,
          CASE WHEN @SortExpression ='Country_area' THEN Country_area END,
          CASE WHEN @SortExpression ='Country_Population' THEN Country_Population END,
        CASE WHEN @SortExpression ='Country_gdp' THEN Country_gdp END
     )as num ,* From Country_Profile123 ) as tbl
      WHERE num BETWEEN @column AND @column1

I solved half of the problem (that is the paging and sorting), what I'm trying to do now is the sorting order.

Upvotes: 0

Views: 503

Answers (2)

Guffa
Guffa

Reputation: 700352

Use a single case statement instead of repeating the same case statement for each possible value:

SELECT * FROM (
  SELECT ROW_NUMBER()
  over (
    ORDER BY
      CASE @SortExpression
        WHEN 'Country_id' THEN Country_id
        WHEN 'Country_name' THEN Country_name
        WHEN 'Country_region' THEN Country_region
        WHEN 'Country_area' THEN Country_area
        WHEN 'Country_Population' THEN Country_Population
        WHEN 'Country_gdp' THEN Country_gdp
      END
  ) as num, * From Country_Profile123
) as tbl
WHERE num BETWEEN @column AND @column1

Applying sort order is a bit trickier. You can't apply the direction to the value, so you need a first hand case and a second hand case with different directions:

SELECT * FROM (
  SELECT ROW_NUMBER()
  over (
    ORDER BY
      CASE @SortExpression
        WHEN 'Country_id' THEN Country_id
        WHEN 'Country_name' THEN Country_name
        WHEN 'Country_region' THEN Country_region
        WHEN 'Country_area' THEN Country_area
        WHEN 'Country_Population' THEN Country_Population
        WHEN 'Country_gdp' THEN Country_gdp
        ELSE 0
      END,
      CASE @SortExpression
        WHEN 'Country_id_desc' THEN Country_id
        WHEN 'Country_name_desc' THEN Country_name
        WHEN 'Country_region_desc' THEN Country_region
        WHEN 'Country_area_desc' THEN Country_area
        WHEN 'Country_Population_desc' THEN Country_Population
        WHEN 'Country_gdp_desc' THEN Country_gdp
        ELSE 0
      END DESC
  ) as num, * From Country_Profile123
) as tbl
WHERE num BETWEEN @column AND @column1

Upvotes: 3

Benjamin Cox
Benjamin Cox

Reputation: 6120

Are you looking for something like this?

SELECT * FROM (SELECT ROW_NUMBER()
over
     (
        ORDER BY
          CASE WHEN @SortExpression ='Country_id' THEN Country_id DESC END,
          CASE WHEN @SortExpression ='Country_id_asc' THEN Country_id ASC END,
          CASE WHEN @SortExpression ='Country_name' THEN Country_name DESC END,
          CASE WHEN @SortExpression ='Country_name_asc' THEN Country_name ASC END,
          CASE WHEN @SortExpression ='Country_region' THEN Country_region DESC END,
          CASE WHEN @SortExpression ='Country_region_asc' THEN Country_region ASC END,
          CASE WHEN @SortExpression ='Country_area' THEN Country_area DESC END,
          CASE WHEN @SortExpression ='Country_area_asc' THEN Country_area ASC END,
          CASE WHEN @SortExpression ='Country_Population' THEN Country_Population DESC  END,
          CASE WHEN @SortExpression ='Country_Population_asc' THEN Country_Population ASC END,
        CASE WHEN @SortExpression ='Country_gdp' THEN Country_gdp DESC END
        CASE WHEN @SortExpression ='Country_gdp_asc' THEN Country_gdp ASC END
     )as num ,* From Country_Profile123 ) as tbl
      WHERE num BETWEEN @column AND @column1

Upvotes: 1

Related Questions