May 18, 2016 at 9:04 pm
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
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
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