Suppose you are asked to write a query to get the student details such as StudID, StudName, and Marks from table STUDENTS_MARKS who have scored higher than 80 marks, and sort the result by the last three characters of student names in ascending order.
If two or more student have same last three characters in name such as (i.e.: Mandeep, Jaydeep both name have same last three characters as ‘eep’ ), then sort the records by StudId in ascending order.
Lets create a sample table named Student_marks and insert some records into table.
CREATE TABLE STUDENT_MARKS ( StudId INT NOT NULL, StudName VARCHAR(30), Marks INT ) INSERT INTO STUDENT_MARKS (StudId, StudName, Marks) VALUES (1, 'Jaydeep',85), (2, 'Mandeep',81), (3, 'Aakash',89), (4, 'Suraj',75), (5, 'Mahesh',80), (6, 'Rajan',90), (7, 'Viaksh',88), (8, 'Sumit',60), (9, 'Vishwajeet',50)
Now we have a table as shown below.
SELECT * FROM STUDENT_MARKS
Lets write the query to get the student who have secured more than 80 marks.
Following T-SQL query, returns the student details who have got higher than 80 marks and sorts the result based on the last three character of names in ascending order, and if two or more student have same last three characters in name then sort the records by StudId in ascending order.
SELECT StudID, StudName ,Marks FROM STUDENT_MARKS
WHERE MARKS >80 ORDER BY RIGHT(StudName,3), StudID ASC ;
You can see, it returns the records of only those students who have scored more than 80 marks.
Also you can see, student Jaydeep and Mandeep both have got marks higher than 80 and the last three character of their name is ending with same characters that is ‘eep’ so the records are sort by StudId in ascending order, that’s why Jaydeep comes before Mandeep in the result set.
Also Read..
