Set below query into asc or desc order in mysql?

Question

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 HAVE DONE HALF OF IT. MEANS. DID SORTING REMAINING IS SORTING ORDER.

Answer 1

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

Answer 2

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