SQL Teradata query

Question

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.?

Classic [tag:greatest-n-per-group] problem. out of time to write an answer, you should check this out: http://stackoverflow.com/a/7745635/570191 — Adriano Carneiro, Nov 02 '12 at 16:36
@Adrian - Actually, it's just a `Sort by an arbitrary order` and `MAX()` question. — MatBailie, Nov 02 '12 at 16:37
@Dems My goodness, you are right. I did say I was out of time of a closer look, right? :) — Adriano Carneiro, Nov 02 '12 at 16:40

score 3 · Answer 1 · answered Nov 02 '12 at 16:35

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

score 0 · Answer 2 · edited May 20 '13 at 08:04

0

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

edited May 20 '13 at 08:04

michaelb958--GoFundMonica

4,617
7
31
35

answered May 20 '13 at 07:26

user2400820

1

A comment to explain your answer? – Shikiryu May 20 '13 at 07:53

SQL Teradata query

2 Answers2