i want only the rows where the MyCol is the maximu

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

General SQL Server Forums

New to SQL Server Programming

i want only the rows where the MyCol is the maximu

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 09:23:10

In my table, I have a column whose value is repeated in several rows. I want only the row containing its highest value.
For ex, my table ll be like this:

Col1 Col2
1 1
1 2
1 3
2 7
2 9
3 1
3 5
3 13

I want my query to return omly the rows having the haighest values of Col2 in each group. So the returned result should be:

Col1 Col2
1 3
2 9
3 13

What should be my query pls
Thanks

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-21 : 09:27:13

select col1, max(col2)
from table
group by col1

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 09:32:57

u re the man thank you

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 09:34:38

and if we want all the columns of the table and not only col1 and col2 without specifying
select max(col2), col1,col3,col43....., coln from ....
how d that be
thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 09:37:10

From your expected result it seems like you want Query1. From your description, it sounds like the 2nd query.

declare @table table
(
	Col1 	int,
	Col2	int
)
insert into @table
select	1, 1	union all
select	1, 2	union all
select	1, 3	union all
select	2, 7	union all
select	2, 9	union all
select	3, 1	union all
select	3, 5	union all
select	3, 13

-- Query 1
select	Col1, max(Col2) as max_Col2
from	@table
group by Col1
order by Col1

/* RESULT

Col1        max_Col2    
----------- ----------- 
1           3
2           9
3           13

*/

-- Query 2
select	Col1, Col2
from	@table t
where	Col2 = (select max(Col2) from @table x where x.Col1 = t.Col1)
order by Col1

/* RESULT

Col1        Col2        
----------- ----------- 
1           3
2           9
3           13

*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 09:38:40

quote:
Originally posted by rtutus

and if we want all the columns of the table and not only col1 and col2 without specifying
select max(col2), col1,col3,col43....., coln from ....
how d that be
thank you

That's the Query 2.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 09:39:45

What if I want all the columns and not only Col1 and Col2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 09:41:54

[code]declare @table table
(
Col1 int,
Col2 int,
Col3 int
)
insert into @table
select 1, 1, 2 union all
select 1, 2, 3 union all
select 1, 3, 4 union all
select 2, 7, 5 union all
select 2, 9, 3 union all
select 3, 1, 2 union all
select 3, 5, 3 union all
select 3, 13,1

select Col1, Col2, Col3
from @table t
where Col2 = (select max(Col2) from @table x where x.Col1 = t.Col1)
order by Col1
/* RESULT

Col1 Col2 Col3
----------- ----------- -----------
1 3 4
2 9 3
3 13 1

*/[/code]

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 10:25:15

I have my table OrdHead already created. The field for which I want the maximum values is No_Ref and the field that s repeated is Sales_Ord. so I do this:

select * from ordhead as t where t.no_ref=(select max(no_ref) from ordhead as x where t.no_ref=x.no_ref group by sales_ord) order by sales_ord

But I get duplicates in my Sales_ord field. That s not what I want.
Thanks

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 10:47:14

or using Col1, Col2... as in above I do this:
select * from MyTable as t where t.col2=(select max(col2) from MyTable as x where t.col2=x.col2 group by col1) order by col1
but it s not giving me the expected result

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 10:51:32

Can you post your table structure, some sample data and result that you want ?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 10:52:30

You've just not copied khtan's example. Try something like this....

select * from ordhead t
where no_ref = (select max(no_ref) from ordhead x where x.sales_ord = t.sales_ord)
order by sales_ord

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

rtutus
Aged Yak Warrior

522 Posts

Posted - 2006-07-21 : 10:58:27

sorry my mistake your query was perfect. But I put Col2 instead of col1 in :
select max(col2) from MyTable as x where t.col2=x.col2
Now I chnged Col2 with Col1 and it s working perfectly
Thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources