CODEWITHSUNDEEP
sqlsql-serverdatabase
BlackCat
BlackCat

Reputation: 2044

Query with order by

Table : Students

  ID    Name    Marks
    1   Ashley   81
    2   Samantha 75
    4   Julia    76
    3   Belvet   84

A query to return the name where marks is greater than 75, output will be sorted by last three characters of each name,if ties,then sort with ascending ID.

My Query:- 

  Select Name 
    From Students 
   Where Marks > 75
Order By (Select Substring(Name,1,2))

I tried this so far,I tried to figure out order by someway,but this is wrong . How could I accomplish it?

Upvotes: 1

Views: 126

Answers (5)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

SELECT Name
FROM Students 
WHERE Marks > 75
ORDER BY RIGHT(Name, 3),
         ID

You could also use:

ORDER BY SUBSTRING(Name, LEN(Name)-2, 3)

as an alternative.

Upvotes: 4

René Vogt
René Vogt

Reputation: 43946

You don't need SELECT in the ORDER BY clause. And to order by the last three characters instead of the first use this:

SELECT Name
FROM Students 
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID

To order ties by ID just add ID as second ORDER argument.

Upvotes: 2

Amit Singh Chauhan
Amit Singh Chauhan

Reputation: 1

Make this function in sql:

Alter function dbo.getlastthreechar (@s varchar(256)) returns varchar(3)
   with schemabinding<
begin
   if @s is null
      return null
   declare @s2 varchar(256)
   set @s2 = ''
   set @s2 = RIGHT(@s,3)
   if len(@s2) = 0
      return null
   return @s2
   end

Then you would query like:

Select Name from Students where Marks > 75 order by dbo.getlastthreechar(Name )

Upvotes: -2

Doruk
Doruk

Reputation: 912

Below is the part you need. Order by it, then id.

select substring('THENAME', LENGTH('THENAME')-2, 3)

Whole code:

Select Name from Students 
where Marks > 75
order by (Substring(Name,LENGTH(Name)-2,3)), Id

Upvotes: 0

Wake
Wake

Reputation: 1696

SELECT Name 
FROM Students 
WHERE Marks > 75
ORDER BY substring(Name, LEN(Name)-2, 3), ID

Upvotes: 1

Related Questions