CODEWITHSUNDEEP
sqlsql-order-byteradata
rahul kanakarajan
rahul kanakarajan

Reputation: 21

SQL Teradata query

I have a table abc which have many records with columns col1,col2,col3,

dept         | name |  marks |

science         abc      50
science         cvv      21
science         cvv      22  
maths           def      60
maths           abc      21
maths           def      62
maths           ddd      90

I need to order by dept and name with ranking as ddd- 1, cvv - 2, abc -3, else 4 then need to find out maximum mark of an individual. Expected result is 

dept         | name |  marks |

science         cvv      22
science         abc      50
maths           ddd      90
maths           abc      21
maths           def      62

. How may I do it.?

Upvotes: 2

Views: 3242

Answers (2)

user2400820
user2400820

Reputation: 1

This should work:

SELECT Dept, Name, MAX(marks) AS mark
FROM yourTable
GROUP BY Dept, Name
ORDER BY CASE WHEN Name = 'ddd' THEN 1
              WHEN Name = 'cvv' THEN 2
              WHEN Name = 'ABC' THEN 3
              ELSE 4 END

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

SELECT
  dept,
  name,
  MAX(marks)   AS mark
FROM
  yourTable
GROUP BY
  dept,
  name
ORDER BY
  CASE WHEN name = 'ddd' THEN 1
            name = 'cvv' THEN 2
            name = 'abc' THEN 3
                         ELSE 4 END

Or, preferably, have another table that includes the sorting order.

SELECT
  yourTable.dept,
  yourTable.name,
  MAX(yourTable.marks)   AS mark
FROM
  yourTable
INNER JOIN
  anotherTable
    ON yourTable.name = anotherTable.name
GROUP BY
  yourTable.dept,
  youtTable.name
ORDER BY
  anotherTable.sortingOrder

Upvotes: 3

Related Questions