How to get top two values from multiplecolum in sqlserver

Question

0.00/5 (No votes)

See more:

hello all

i have three columns in table marksheet suppose

test1 with value 70 ,test2 with value 50 and test3 with value 40

now how can i get top two values from sqlserver which will be 70 and 50

thanks in advance

What I have tried:

hello all

i have three columns in table marksheet suppose

test1 with value 70 ,test2 with value 50 and test3 with value 40

now how can i get top two values from sqlserver which will be 70 and 50




thanks in advance

Posted 17-Apr-17 23:41pm

Shuaib wasif khan

Updated 18-Apr-17 1:46am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

kosmas kafataridis · Answer 1 · 2017-04-17T23:53:00

If i understand you correct

CREATE TABLE #test (
    test1 int,
   test2 int,
   test3 int
);
insert into #test (test1, test2, test3) values (70, 50, 30);
insert into #test (test1, test2, test3) values (30, 70, 50);
insert into #test (test1, test2, test3) values (50, 30, 70);
select 
case when test1 >test2 and test1>test3 then test1 when test2>test1 and test2>test3 then test2 else test3 end firstbigger ,
	case when 
		test1 >test2 and test1>test3  then case when test2>test3 then test2 else test3 end 
		when test2>test1 and test2>test3 then case when test1>test3 then test1 else test3 end  
	else case when test2>test1 then test2 else test1 end  end secondbigger

from #test

drop table #test

CHill60 · Answer 2 · 2017-04-18T01:46:00

An alternative to Solution 1 (which repeats the results for each row in table #test), using the same test setup

SQL

select top 2 * from
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
) q
order by 1 desc

I.e. put all of the values of interest into a single query making sure there are no duplicates (distinct and union do this) then order that result and pick the top 2 entries. Results:

SQL

datum
70
50

You could use a CTE instead of the sub-query if you prefer

SQL

;WITH cte as
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
) 
select top 2 * from cte
order by 1 desc

If you want the results in the same row but in two columns then you should use PIVOT. There is a neat trick using ROW_NUMBER which will get you the TOP 2 without using that key word:

SQL

;WITH cte as
(
	select distinct(test1) as datum
	from #test
	union 
	select distinct(test2)
	from #test
	union  
	select distinct(test3)
	from #test
)
select * from
(
	select ROW_NUMBER() OVER (ORDER BY datum desc) AS rn, datum 
	from cte
) base
PIVOT (MAX(datum) FOR rn in ([1],[2])) pvt