Retrive the highest from each row with column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Retrive the highest from each row with column

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-01-20 : 07:48:07

ENG IND AMER
35 4 2
3 6 1
3 28 5
47 3 1

My requirement is retrieve the data where “ENG” is high in corresponding rows.
For example:
Take first row, here 35 is highest and it belongs to ENG
Take second row, here 6 is highest and it belongs to IND
Take third row, here 28 is highest and it belongs to IND
Take 4th row, here 47 is highest and it belongs to ENG.

Now I want to retrieve the data as follows
ENG
35
47

G. Satish

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 07:53:57

if data is like
ENG IND AMER
5 3 8

then what you want?

Rahul Shinde

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-01-20 : 07:55:36

Suppose, if u have 50 rows in the table. I want to retrive the rows where ENG is high. Suppose for 10 rows, the ENG is high among 50. I need that 10 rows as result

quote:
Originally posted by ra.shinde

if data is like
ENG IND AMER
5 3 8

then what you want?

Rahul Shinde

G. Satish

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 08:03:16

DECLARE @TABLENAME TABLE(ENG int, IND int, AMER int)

insert into @TABLENAME
SELECT 35, 4, 2
union all
select 3, 6, 1
union all
select 3, 28, 5
union all
select 47, 3, 1

select ENG FROM @TABLENAME
WHERE ENG > IND and ENG > AMER

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 08:09:17

quote:
Originally posted by harsh_athalye

select Eng
from tbl
where Eng > Ind and Ind > Amer

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

if Eng > Amer > Ind then your query will not return it

Rahul Shinde

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-01-20 : 08:22:41

True. Deleted my earlier solution.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources