August 30, 2010 at 11:59 pm
How do you find the second largest value in a column of table without using any sub queries??:exclamationmark:
August 31, 2010 at 12:16 am
Hi there,
I'm not quite sure if this is a good implementation but atleast it doesn't use sub query.
--//SAMPLE TABLE
DECLARE @tbl TABLE
(
Value DECIMAL(5,2)
)
--//SAMPLE DATA
INSERT INTO @tbl
SELECT 112.34
UNIONSELECT 256.78
UNIONSELECT 390.12
UNIONSELECT 434.56
UNIONSELECT 178.90
UNIONSELECT 212.34
UNIONSELECT 356.78
UNIONSELECT 490.12
UNIONSELECT 134.56
UNIONSELECT 278.90
UNIONSELECT 312.34
UNIONSELECT 456.78
UNIONSELECT 190.12
UNIONSELECT 234.56
UNIONSELECT 378.90
--//SEE TABLE DATA
SELECT * FROM @tbl ORDER BY Value DESC
DECLARE @secondLargest DECIMAL(5,2)
--//IM NOT SURE IF THIS IS A GOOD PRACTICE
SELECT TOP 2 @secondLargest=Value
FROM @tbl
ORDER BY Value DESC
SELECT @secondLargest
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 31, 2010 at 12:43 am
How about this?
declare @rank int;
set @rank = 2;
; with nums (N) as
(
SELECT 3
UNION ALL SELECT 6
UNION ALL SELECT 9
UNION ALL SELECT 12
UNION ALL SELECT 15
UNION ALL SELECT 18
UNION ALL SELECT 21
UNION ALL SELECT 24
UNION ALL SELECT 27
UNION ALL SELECT 30
UNION ALL SELECT 33
UNION ALL SELECT 36
UNION ALL SELECT 39
UNION ALL SELECT 42
UNION ALL SELECT 45
UNION ALL SELECT 48
),
numbered_list (rn, n) as
(
select rn = row_number() over(order by n desc) , n from nums
)
select * from numbered_list where rn = @rank
August 31, 2010 at 12:47 am
Without Sub Queries, probably Quatrie's method will siut.. But tel me, isnt that a interview / homework question? IF u learn the answer from us, when will you learn it yourself ? :ermm:
August 31, 2010 at 12:55 am
ColdCoffee (8/31/2010)
But tel me, isnt that a interview / homework question? IF u learn the answer from us, when will you learn it yourself ? :ermm:
agree
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply