0

I want second largest value. I can get first largest by using max(field name) but how i can get 2nd largest.Is there any custom code. I am new so any help greatly appreciated. Thanks!

I want it in SSRS

maddy
  • 50
  • 1
  • 1
  • 10
  • It depends on where you want to put it (tablix or TextBox), you can select just the second biggest value in your dataset Query then you will have only one value to display – Amine Zaminex Aug 08 '18 at 18:09
  • I want it in SSRS report function expression – maddy Aug 08 '18 at 18:10
  • Can you provide more detail where you want to display this value? Can you order your dataset query by this column or that is not an option? – AB_87 Aug 09 '18 at 02:26

2 Answers2

0

ORDER BY your field DESC, then limit the result set to 1 row, starting at the 2nd row:

SELECT field FROM TABLE ORDER BY field DESC LIMIT 1, 1;

(This is MySQL syntax, your engine may vary.)

Alex Howansky
  • 50,515
  • 8
  • 78
  • 98
0

This is messy but It should work:

SELECT TOP 1 
    [column] 
FROM [table]
WHERE [column] <> 
    (
        Select top 1 
            [column] 
        from [table] 
        order by [column] DESC
    ) 
ORDER BY [column] DESC

If this is in a procedure and you dont care about doing 2 queries this looks a bit neater:

DECLARE @MAX INT
SELECT TOP 1 @MAX = MAX([COLUMN]) from [table] order by [COLUMN] desc
SELECT TOP 1 [COLUMN] FROM [TABLE] WHERE [COLUMN] <> @mAX order by [COLUMN] desc
Dominic Hemken
  • 1
  • 5