Display max value rows only

Question

I have the following table (must shorter version than the real one), and I want to all the rows with max _ values for each _ displayed. How should I do this?

Table Now

Table I want to have

thanks a lot in advance!!

Don't use images for sample data and what have you tried? This doesn't look particularly difficult if you use a window function like `RANK()`. — ZLK, Oct 24 '17 at 04:21

score 1 · Answer 1 · answered Oct 24 '17 at 05:07

Using the dense_rank function and a derived table would be appropriate for this (please note I used underscores instead of spaces in the column names):

select group_type
       ,desk_number
       ,comments
from
(select *
       ,dense_rank() over(partition by group_type order by desk_number desc) dr
from mytable) t1
where t1.dr = 1

I made a rextester sample that you can try here

Let me know if you have any questions.

Jayasurya Satheesh · Answer 2 · 2017-10-25T08:05:36.597

0

try this :

WITH CTE
AS
(
  SELECT
    SeqNo = ROW_NUMBER() OVER(ORDER BY CAST(DeskNumber AS INT) DESC PARTITION BY GroupType),
    GroupType,
    DeskNumber,
    [Comment]
    FROM YourTable
)
SELECT
*
FROM CTE WHERE CTE.SeqNo = 1

edited Oct 25 '17 at 08:05

answered Oct 24 '17 at 04:18

Jayasurya Satheesh

7,826
3
22
39

the `JOIN` is useless – Radim Bača Oct 25 '17 at 07:11
You Can use Exists instead. I wrote just one method. – Jayasurya Satheesh Oct 25 '17 at 07:47
No, it does not make a sense to do the self join and also exists is useless. Just remove the `JOIN` and you will have basicaly the same solution as @Simon – Radim Bača Oct 25 '17 at 07:55

score 0 · Answer 3 · edited Oct 25 '17 at 02:10

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

This answers your question quite well but I will convert it for your convenience <3

SELECT *
FROM table
INNER JOIN
    (SELECT comments, MAX([desk number]) AS MaxDesk
    FROM table
    GROUP BY comments) groupedtable
ON table.[desk number]= groupedtable.[desk number]
AND table.comments= groupedtable.MaxDesk

Display max value rows only

3 Answers3