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)
_____________

max col in a table subset

hi i have a table as follows:

col1 col2 col3
2 BB 2
1 AB 3
3 CA 3
1 AC 1
2 BA 4
3 CB 2
2 BC 6
1 AA 2
3 CC 5

i want to output the whole row of each unique col1 where its col3 is max, so that i will get the ff result set
1 AB 3
2 BC 6
3 CC 5

i have an idea which is to use a cursor but i don't know the best/fastest way of doing it.
thanks heaps in advance!select col1
, col2
, col3
from daTable as foo
where col3 =
( select max(col3)
from daTable
where col1 = foo.col1 )

Matrix sub-total column width

I have a matrix report which has a row total at the end e.g.
col1 col2 total
80.1 60.4 140.5
Is there a way in which I can make the total column wider than the data cells in the matrix or alternatively can I change the total format to have no decimal places? At the moment its width seems determined by the data cell width and formatting the column with #0 seems to have no effect.
Thanks1. Is there a way to make a matrix total column wider than the associated
data cells.
No. The width of the total column is bound to the width of the data cells.
2. Is there a way to format a total cell independently of the associated
data cells?
Yes. If you set the format code on the Subtotal cell (click on the green
arrow Total cell) it will be ignored. What you have to is use an expression
in the matrix data cell:
=iif(InScope("matrix1_ColumnGroupName"), "C", "C0")
Your expectation about being able to override the data cell format string
using the subtotal format property seems reasonable. I have passed your
observation along to the development team
-- Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"John H" <John H@.discussions.microsoft.com> wrote in message
news:951BAD17-40E0-4E7C-A76F-06323C08DF1B@.microsoft.com...
> I have a matrix report which has a row total at the end e.g.
> col1 col2 total
> 80.1 60.4 140.5
> Is there a way in which I can make the total column wider than the data
cells in the matrix or alternatively can I change the total format to have
no decimal places? At the moment its width seems determined by the data
cell width and formatting the column with #0 seems to have no effect.
> Thanks|||Many thanks Bruce, the formatting suggestion worked well. John
"Bruce Johnson [MSFT]" wrote:
> 1. Is there a way to make a matrix total column wider than the associated
> data cells.
> No. The width of the total column is bound to the width of the data cells.
> 2. Is there a way to format a total cell independently of the associated
> data cells?
> Yes. If you set the format code on the Subtotal cell (click on the green
> arrow Total cell) it will be ignored. What you have to is use an expression
> in the matrix data cell:
> =iif(InScope("matrix1_ColumnGroupName"), "C", "C0")
> Your expectation about being able to override the data cell format string
> using the subtotal format property seems reasonable. I have passed your
> observation along to the development team
>
> -- Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "John H" <John H@.discussions.microsoft.com> wrote in message
> news:951BAD17-40E0-4E7C-A76F-06323C08DF1B@.microsoft.com...
> > I have a matrix report which has a row total at the end e.g.
> >
> > col1 col2 total
> > 80.1 60.4 140.5
> >
> > Is there a way in which I can make the total column wider than the data
> cells in the matrix or alternatively can I change the total format to have
> no decimal places? At the moment its width seems determined by the data
> cell width and formatting the column with #0 seems to have no effect.
> >
> > Thanks
>
>|||Hi,
i have used matrix in one report. in that for ex i have Col1 in Row region,
Col2 in Dynamic column region,Col3 in data region. Is there a way to have
subtotal below Col3 and subtotal for Col2 side by Col2. when i right click on
Col3 it doesnt have Subtotal option.Please help me out. My expected output
should be as below
--|--|
| Col2 | Expected subtotal
--|--|
Col1 |Col3 |
--|--|
Expected
Total
"John H" wrote:
> I have a matrix report which has a row total at the end e.g.
> col1 col2 total
> 80.1 60.4 140.5
> Is there a way in which I can make the total column wider than the data cells in the matrix or alternatively can I change the total format to have no decimal places? At the moment its width seems determined by the data cell width and formatting the column with #0 seems to have no effect.
> Thanks

Matching Columns

All

I have a table which contains 4 columns each of which are NULL or contain a 6 digit code.
Here is a sample of the table content:

COL1 COL2 COL3 COL4
-- -- -- --
452359 NULL NULL 347406
NULL NULL 347406 347406
592319 NULL 347406 347406
592319 150009 347406 347406
592319 150010 347406 347406

Through out the table any number of the columns can be null.

I wish to remove rows from this table where the columns values are contained in another row i.e Row 2 above is contained within row 3. Similarly, row 3 is contained within row 4.
So, the only rows I want from the sample data above are rows 1, 4 and 5.

I hope I have explained my query adequately, and any help would be great appreciated. (Before I go mad...!)

Regards,
KatherineYou'll have to test this carefully, but I'd use something like:DELETE FROM foo
WHERE (Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL OR Col4 IS NULL)
AND EXISTS (SELECT *
FROM foo AS b
WHERE b.Col1 = Coalesce(foo.Col1, b.Col1)
AND b.Col2 = Coalesce(foo.Col2, b.Col2)
AND b.Col3 = Coalesce(foo.Col3, b.Col3)
AND b.Col4 = Coalesce(foo.Col4, b.Col4))-PatP|||pat: sorry: :)
insert...
select 123, null, null, null
union
select null, 123, null, null
union
select 123, 123, null, null

gives:
col1 col2 col3 col4
---- ---- ---- ----
NULL 123 NULL NULL
123 NULL NULL NULL
123 123 NULL NULL

Turns out it's a nice challenge, at least for me...|||I know there *has* to be a better way to do this... but here goes.

create table test (a int null, b int null, c int null, d int null)

insert into test(a,b,c,d)
select 123, null, null, null
union
select null, 123, null, null
union
select 123, 123, null, null

delete from t1
from test t1
inner join test t2
on (t1.a is null or t1.a=t2.a)
and (t1.b is null or t1.b=t2.b)
and (t1.c is null or t1.c=t2.c)
and (t1.d is null or t1.d=t2.d)
where
(
((t1.a!=t2.a and (t1.a is not null and t2.a is not null)) or (t1.a is null and t2.a is not null) or (t1.a is not null and t2.a is null))
or ((t1.b!=t2.b and (t1.b is not null and t2.b is not null)) or (t1.b is null and t2.b is not null) or (t1.b is not null and t2.b is null))
or ((t1.c!=t2.c and (t1.c is not null and t2.c is not null)) or (t1.c is null and t2.c is not null) or (t1.c is not null and t2.c is null))
or ((t1.d!=t2.d and (t1.d is not null and t2.d is not null)) or (t1.d is null and t2.d is not null) or (t1.d is not null and t2.d is null))
)

You'd have to run a dedup after this if you've got duplicate rows.
That ugly WHERE is just killin' me, but dealing with the NULLs is a real PITA.|||I tried it by creating a new table, inserting those with the least nulls then winding up to the most nulls skipping those with matching values. The way I have it now is a real pain and doesn't work properly.. so...|||Like a lot of tricky SQL problems, the issue here is not the question you are asking, but the fact that your schema is not normalized. Fix it, if at all possible, or you are sure to run into more problems in the future.

This method solves your problem but normalizing your data into a temporary table, and then searching the temporary table for record groups that are a subset of other record groups. It presupposes that you have a unique primary key in your original table.

DECLARE @.TEMPNORMAL TABLE
(PKEY INT,
COLVALUE VARCHAR(6))

DECLARE @.PKEYITEMCOUNT TABLE
(PKEY INT,
ITEMCOUNT INT)

INSERT INTO @.TEMPNORMAL
(PKEY,
COLVALUE)
SELECT DISTINCT
PKEY,
COLVALUE
FROM
(Select PKEY, COL1 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL2 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL3 as COLVALUE from YOURTABLE
UNION
Select PKEY, COL4 as COLVALUE from YOURTABLE) NORMALTRANSFORM

INSERT INTO @.PKEYITEMCOUNT
(PKEY,
ITEMCOUNT)
SELECT PKEY,
COUNT(*)
FROM @.TEMPNORMAL
GROUP BY PKEY

DELETE
FROM YOURTABLE
INNER JOIN
(SELECT PKEY
FROM @.PKEYITEMCOUNT PKEYITEMCOUNT1
INNER JOIN
(SELECT PKEY1,
PKEY2,
COUNT(*) MATCHCOUNT
FROM (SELECT DISTINCT
TEMPNORMAL1.PKEY PKEY1,
TEMPNORMAL2.PKEY PKEY2,
TEMPNORMAL1.COLVALUE
FROM @.TEMPNORMAL TEMPNORMAL1
INNER JOIN @.TEMPNORMAL TEMPNORMAL2
ON TEMPNORMAL1.COLVALUE = TEMPNORMAL2.COLVALUE
AND TEMPNORMAL1.PKEY <> TEMPNORMAL2.PKEY) MATCHQUERY
GROUP BY PKEY1,
PKEY2) PKEYMATCHES
ON PKEYITEMCOUNT.PKEY = PKEYMATCHES.PKEY
AND PKEYITEMCOUNT.ITEMCOUNT = PKEYMATCHES.MATCHCOUNT
INNER JOIN @.PKEYITEMCOUNT PKEYITEMCOUNT2
ON PKEYMATCHES.PKEY2 = PKEYITEMCOUNT2.PKEY
AND PKEYITEMCOUNT1.ITEMCOUNT < PKEYITEMCOUNT2.ITEMCOUNT) SUPERFLUOUSRECORDS
ON YOURTABLE.PKEY = SUPERFLUOUSRECORDS.PKEY

blindman|||Oops...insert statement should read like this:

INSERT INTO @.TEMPNORMAL
(PKEY,
COLVALUE)
SELECT DISTINCT
PKEY,
COLVALUE
FROM
(Select PKEY, COL1 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL2 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL3 as COLVALUE from YOURTABLE where COL1 is not null
UNION
Select PKEY, COL4 as COLVALUE from YOURTABLE where COL1 is not null) NORMALTRANSFORM