Wednesday, March 28, 2012

max elements from table

Hi,
my table has COL1, COl2 with values below:

COL1 COL2
joy 10
joy 11
nik 10
nik 11
nik 12
ale 11
ale 12

How can I select rows with max value for COL2?
that is I want

COL1 COL2
joy 11
nik 12
ale 12

Thanks in advance.
Sunday.SAMPLE Script : run it against some test DB in Query Analyzer .
--=======================================

declare @.tbl table (COL1 int ,COL2 varchar(30) )

insert into @.tbl (COL1,COL2) values (1,'JOY')
insert into @.tbl (COL1,COL2) values (2,'JOY')
insert into @.tbl (COL1,COL2) values (3,'JOY')

insert into @.tbl (COL1,COL2) values (1,'NIK')
insert into @.tbl (COL1,COL2) values (2,'NIK')
insert into @.tbl (COL1,COL2) values (3,'NIK')

insert into @.tbl (COL1,COL2) values (1,'ALE')
insert into @.tbl (COL1,COL2) values (2,'ALE')
insert into @.tbl (COL1,COL2) values (3,'ALE')
insert into @.tbl (COL1,COL2) values (4,'ALE')
insert into @.tbl (COL1,COL2) values (5,'ALE')

insert into @.tbl (COL1,COL2) values (2,'ROY')
insert into @.tbl (COL1,COL2) values (3,'ROY')

insert into @.tbl (COL1,COL2) values (1,'STU')
insert into @.tbl (COL1,COL2) values (2,'STU')
insert into @.tbl (COL1,COL2) values (3,'STU')
insert into @.tbl (COL1,COL2) values (4,'STU')
insert into @.tbl (COL1,COL2) values (5,'STU')

-- pure GROUP BY Clause
SELECT Max(COL1) AS 'COL1',COL2 FROM @.tbl GROUP BY COL2

-- GROUP By Clause with additional ORDER BY
SELECT Max(COL1) AS 'COL1',COL2 FROM @.tbl GROUP BY COL2 ORDER BY COL1 DESC

-- REFER to Books On Line for more information on the TOPIC
--=============================================

regards.
srdjan|||Hi,
What do you think about that one:

I named my table 'tb', put alias 'a' and 'b' on it:

_____________
select a.* from tb a where col2 = (select MAX(col2) from tb b where b.col1 = a.col1)
_____________

No comments:

Post a Comment