Searlee Searlee - 3 months ago 11
SQL Question

SQL Query ignore subsequent distinct rows

I've got a table,

C_A C_B C_C C_D
mr 1 1000 d
mrs 2 500 d
mz 3 2500 d
mrs 4 1500 d


I'd like to
SELECT
*
FROM
table
ORDER
BY
C_C
DESC
but i'd like to ignore any subsequent (after the ordering) rows in which column A (C_A) is the same as rows that have a larger value in column C.

so it should look like:

C_A C_B C_C C_D
mz 3 2500 d
mrs 4 1500 d
mr 1 1000 d


when output

I've tried lots of different queries and sub queries in using
DISTINCT
and by grouping column C_A but I'm not competent enough in SQL in order to apply those to my example.

Any ideas on how I would be able to do this?

Answer

I'm not quite sure I understood the problem. But this is perhaps what you're looking for:

select t1.*
from tablename t1
  join (select C_A, max(C_C) as maxcc
        from tablename
        group by C_A) t2
  on t1.C_A = t2.C_A and t1.C_C = t2.maxcc

Add ORDER BY t1.C_C desc at the end, if needed.