Post reply

Calculate Maximum three values from table in SQL

  • May 18, 2016 at 9:04 pm

    #316570

    Hi all,

    I have a requirement where I have to calculate maximum three values from a table.

    Table1

    ID Column

    1 B12

    2 B13

    3 B14

    4 B15

    5 B16

    6 F12

    7 F13

    8 F14

    9 F15

    10 F16

    So I want take max(column) and max(column)-3 i.e. ID's with column like '%16,15,14%' is what i want

    Output Table

    ID column

    3 B14

    4 B15

    5 B16

    8 F14

    9 F15

    10 F16

    how can I do this?

  • May 18, 2016 at 10:03 pm

    #1880081

    Hope you're okay with ugly...

    SELECT ID

    , col

    , rn

    FROM

    (

    SELECT ID

    , Col

    , Prefix

    , NumPart

    , ROW_NUMBER() OVER (PARTITION BY Prefix ORDER BY NumPart DESC) AS rn

    FROM (

    SELECT ID

    , col

    , LEFT(col,1) AS Prefix

    , CAST(RIGHT(col,2) AS TINYINT) AS NumPart

    FROM #dummy ) x

    ) y

    WHERE y.rn<=3;

  • May 18, 2016 at 10:06 pm

    #1880082

    Not sure about your Id standard, but if you can extract it then you could use DENSE_RANK

    E.g.

    SELECT ID

    FROM

    (

    SELECT *, DENSE_RANK( ) OVER ( ORDER BY CAST( RIGHT( ID, LEN( ID) - CHARINDEX(' ', Table1.ID ) - 1 ) as int ) DESC ) AS Rnk

    FROM

    (

    VALUES

    ('1 B12'),

    ('2 B13'),

    ('3 B14'),

    ('4 B15'),

    ('5 B16'),

    ('6 F12'),

    ('7 F13'),

    ('8 F14'),

    ('9 F15'),

    ('10 F16')

    ) Table1 ( ID )

    ) OutputTable

    WHERE OutputTable.Rnk <= 3

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply