Max in select clause

I have the following code......
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM
account_t a
LEFT OUTER JOIN price_t p
ON a.ser_id = p.ser_id
GROUP BY
a.acct_nbr ,p.pric_uval_amt
Its purpose is to return an account number from one table, and from the
corresponding price table the latest unit price date and unit price.
Basically the price table
keeps a list of all products associated with an account. Each account can
have multiple products. The price table keeps regularly updated prices for
these products and whatever the date that theprice was updated. What I want
to do is to be able to get the latest (max) unit price date and the
corresponding unit price value.
The code above gives me the latest unit price date (if I take out the
,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
the prices at all previous dates.
What I tend to get is this......
acct_nbr unit price
unit_price_date
----
0001 90
05/05/2005
0001 98
05/06/2005
0001 91
05/07/2005
0002 43
05/05/2005
0002 45
05/06/2005
When what I want is this.........
acct_nbr unit price
unit_price_date
----
0001 91
05/07/2005
0002 45
05/06/2005
Any idea's ?Hi
Is this the query that you are looking for:
SELECT account_t.acct_nbr, account_t.[unit price], derived.Unit_Price_Date
FROM account_t
INNER JOIN (
SELECT a.acct_nbr,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
FROM account_t a
LEFT OUTER JOIN price_t p ON a.ser_id = p.ser_id
GROUP BY a.acct_nbr
) derived
ON derived.Unit_Price_Date = account_t.Unit_Price_Date AND
derived.acct_nbr = account_t.acct_nbr
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Try,
Try,
SELECT
a..acct_nbr
,p.pric_uval_amt AS 'Unit Price'
,p.pric_asof_dt AS 'Unit_Price_Date'
FROM
account_t as a
LEFT OUTER JOIN
price_t as p
ON a.ser_id = p.ser_id
where
p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
p1.ser_id = a.ser_id)
AMB
"quiglepops" wrote:

> I have the following code......
>
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,MAX(pp.pric_asof_dt) AS 'Unit_Price_Date'
> FROM
> account_t a
> LEFT OUTER JOIN price_t p
> ON a.ser_id = p.ser_id
> GROUP BY
> a.acct_nbr ,p.pric_uval_amt
> Its purpose is to return an account number from one table, and from the
> corresponding price table the latest unit price date and unit price.
> Basically the price table
> keeps a list of all products associated with an account. Each account can
> have multiple products. The price table keeps regularly updated prices for
> these products and whatever the date that theprice was updated. What I wan
t
> to do is to be able to get the latest (max) unit price date and the
> corresponding unit price value.
> The code above gives me the latest unit price date (if I take out the
> ,p.pric_uval_amt AS 'Unit Price' line). If I leave that line in I also get
> the prices at all previous dates.
> What I tend to get is this......
> acct_nbr unit price
> unit_price_date
> ----
> 0001 90
> 05/05/2005
> 0001 98
> 05/06/2005
> 0001 91
> 05/07/2005
> 0002 43
> 05/05/2005
> 0002 45
> 05/06/2005
>
> When what I want is this.........
> acct_nbr unit price
> unit_price_date
> ----
> 0001 91
> 05/07/2005
> 0002 45
> 05/06/2005
>
> Any idea's ?
>
>|||Thanks everyone for helping.
Alejandro, used your solution worked well. Thanks a lot.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B65F1A34-7B16-46BB-B371-6143E989ACC5@.microsoft.com...
> Try,
> Try,
> SELECT
> a..acct_nbr
> ,p.pric_uval_amt AS 'Unit Price'
> ,p.pric_asof_dt AS 'Unit_Price_Date'
> FROM
> account_t as a
> LEFT OUTER JOIN
> price_t as p
> ON a.ser_id = p.ser_id
> where
> p.pric_asof_dt = (select max(p1.pric_asof_dt) from price_t as p1 where
> p1.ser_id = a.ser_id)
>
> AMB
>
> "quiglepops" wrote:
>
can
for
want
get

MAX

I have the following code that I created today:
select month(DataPesquisa) as Mes, count(FConceito) as Bom,
isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
Regular,
isnull(Ruim.Conceito,0) as Ruim
from satisfacao x
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '2005'
group by referencia) Excelente
on x.referencia = Excelente.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '2005'
group by referencia) Regular
on x.referencia = Regular.referencia
left outer join (select referencia, count(Fconceito) as Conceito from
satisfacao
where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '2005'
group by referencia) Ruim
on x.referencia = Ruim.referencia
where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
'2005'
group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
Regular.Conceito, Ruim.Conceito
Your result is:
Excellent Godd Regulate Bad
-- -- -- -- --
1 3 2 1 0
My question: I want to select the LARGEST value of these results. Does give
there for using the function MAX in that procedure of top or will I have to
do this for the application (programming even)'?I think you can hadle it in the client application
Madhivanan|||Frank Dulk wrote:

> select month(DataPesquisa) as Mes, count(FConceito) as Bom,
> isnull(Excelente.Conceito,0) as Excelente,isnull(Regular.Conceito,0) as
> Regular,
> isnull(Ruim.Conceito,0) as Ruim
> from satisfacao x
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '4' and referencia = '200
5'
> group by referencia) Excelente
> on x.referencia = Excelente.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '2' and referencia = '200
5'
> group by referencia) Regular
> on x.referencia = Regular.referencia
> left outer join (select referencia, count(Fconceito) as Conceito from
> satisfacao
> where month(Datapesquisa) = '01' and FConceito = '1' and referencia = '200
5'
> group by referencia) Ruim
> on x.referencia = Ruim.referencia
> where month(Datapesquisa) = '01' and FConceito = '3'and x.referencia =
> '2005'
> group by x.referencia, month(x.DataPesquisa), Excelente.Conceito,
> Regular.Conceito, Ruim.Conceito
Instead of several self joins you can rewrite that query to a single
aggregation:
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)

> Excellent Godd Regulate Bad
> -- -- -- -- --
> 1 3 2 1 0
>
> My question: I want to select the LARGEST value of these results. Does giv
e
> there for using the function MAX in that procedure of top or will I have t
o
> do this for the application (programming even)'?
If you want to add a new column with that max value, then the easiest
way is doing it on the client. Else you have to add another case:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
from
(
select
month(DataPesquisa) as Mes,
sum(case when FConceito = '4' then 1 else 0) as Excelente,
sum(case when FConceito = '3' then 1 else 0) as Bom,
sum(case when FConceito = '2' then 1 else 0) as Regular,
sum(case when FConceito = '1' then 1 else 0) as Ruim,
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
If you just need that max info:
select
month(DataPesquisa) as Mes,
max(cnt)
from
(
select
month(DataPesquisa) as Mes,
FConceito,
count(*) as cnt
from satisfacao
where month(Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa), FConceito
) dt
all queries untested...
Dieter|||Thank you for the help
I used your code making the necessary fittings and I have new question.
After arranging, Query was like this:
select
dt.*,
case
when Excelente > Bom and Excelente > Regular and Excelente > Ruim
then Excelente
when Bom > Regular and Bom > Ruim then Bom
when Regular > Ruim then Regular
else Ruim
end
as Maior
from (
select month(DataPesquisa) as Mes,
sum (case DConceito When '4' then 1 else 0 End) as Excelente,
sum (case DConceito When '3' then 1 else 0 End) as Bom,
sum (case DConceito When '2' then 1 else 0 End) as Regular,
sum (case DConceito When '1' then 1 else 0 End) as Ruim
from satisfacao x
where month(x.Datapesquisa) = '01' and x.referencia = '2005'
group by x.referencia, month(x.DataPesquisa)
) dt
results it is it:
Mes Excelente Bom Regular Ruim Maior
Now: Does have as I place in Adult's place the name of the field that the
largest value is (Good or Bad)?
"Dieter Noeth" <dnoeth@.gmx.de> escreveu na mensagem
news:Ou28y4vHFHA.2984@.TK2MSFTNGP15.phx.gbl...
> Frank Dulk wrote:
>
'2005'
'2005'
'2005'
> Instead of several self joins you can rewrite that query to a single
> aggregation:
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
>
give
to
> If you want to add a new column with that max value, then the easiest
> way is doing it on the client. Else you have to add another case:
> select
> dt.*,
> case
> when Excelente > Bom and Excelente > Regular and Excelente > Ruim
> then Excelente
> when Bom > Regular and Bom > Ruim then Bom
> when Regular > Ruim then Regular
> else Ruim
> end
> from
> (
> select
> month(DataPesquisa) as Mes,
> sum(case when FConceito = '4' then 1 else 0) as Excelente,
> sum(case when FConceito = '3' then 1 else 0) as Bom,
> sum(case when FConceito = '2' then 1 else 0) as Regular,
> sum(case when FConceito = '1' then 1 else 0) as Ruim,
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa)
> ) dt
>
> If you just need that max info:
> select
> month(DataPesquisa) as Mes,
> max(cnt)
> from
> (
> select
> month(DataPesquisa) as Mes,
> FConceito,
> count(*) as cnt
> from satisfacao
> where month(Datapesquisa) = '01' and x.referencia = '2005'
> group by x.referencia, month(x.DataPesquisa), FConceito
> ) dt
>
> all queries untested...
> Dieter

Matrix assistance required

Hi,
I'm trying to build a report displaying a matrix that looks like the
following but have some trouble composing it:
Required output :
Code, Name, Target, Batch 1, Batch 2, Batch 3
1, One, 12, 10, 11, 12
2, Two, 9, 10, 9, 11
Totals: 21, 20, 20, 23
From the following data
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
(Assumption: all targets for a specified code are the same, batch=1 code=1
target=12 -> all targets for code=1 equal 12)
The main issue I have is adding the target totals.
Could someone please help me with this?
ErikErik,
Unless I am missing something, in your case you can simply add a matrix
subtotal element by right-clicking on a matrix group in the report layout
and choose Subtotal.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to build a report displaying a matrix that looks like the
> following but have some trouble composing it:
> Required output :
> Code, Name, Target, Batch 1, Batch 2, Batch 3
> 1, One, 12, 10, 11, 12
> 2, Two, 9, 10, 9, 11
> Totals: 21, 20, 20, 23
> From the following data
> BatchId, Code, Name, Target, Actual
> 1, 1, One, 12, 10
> 1, 2, Two, 9, 10
> 2, 1, One, 12, 11
> 2, 2, Two, 9, 9
> 3, 1, One, 12, 12
> 3, 2, Two, 9, 11
> (Assumption: all targets for a specified code are the same, batch=1 code=1
> target=12 -> all targets for code=1 equal 12)
> The main issue I have is adding the target totals.
> Could someone please help me with this?
> Erik
>|||Hi Teo,
Thank you for the reply, but that doesn't fix the problem.
The target column exists only once and doesn't repeat like the "Batch ..."
column does. I don't know exactly how to explain this issue further more
because my "matrix-knowledge" doesn't reach that far.
What I did to create the matrix is the following:
- Add a matrix control
- Drop the "BatchId" field into the "columns" cell.
- Drop the "Code", "Name" and "Target" fields into the "rows" cells (the
seconds and third fields added as row-groups.
I managed to get subtotals for the "Batch..." columns by adding a new
row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
rather not see this column in my report and it doesn't totalize the target
column.
Maybe you could post me an example based on the data described below? (if
it's not too much trouble) I like solving my own problems, but are pulling
my hairs out on this one.
Erik Tamminga, MCSD
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message
news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> Erik,
> Unless I am missing something, in your case you can simply add a matrix
> subtotal element by right-clicking on a matrix group in the report layout
> and choose Subtotal.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
> message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm trying to build a report displaying a matrix that looks like the
> > following but have some trouble composing it:
> >
> > Required output :
> >
> > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > 1, One, 12, 10, 11, 12
> > 2, Two, 9, 10, 9, 11
> > Totals: 21, 20, 20, 23
> >
> > From the following data
> >
> > BatchId, Code, Name, Target, Actual
> > 1, 1, One, 12, 10
> > 1, 2, Two, 9, 10
> > 2, 1, One, 12, 11
> > 2, 2, Two, 9, 9
> > 3, 1, One, 12, 12
> > 3, 2, Two, 9, 11
> >
> > (Assumption: all targets for a specified code are the same, batch=1
code=1
> > target=12 -> all targets for code=1 equal 12)
> >
> > The main issue I have is adding the target totals.
> >
> > Could someone please help me with this?
> >
> > Erik
> >
> >
>|||Eric,
I see now what the issue is. You need to create a subtotal on a row column
(Target). Unfortunately, this is not supported. As a workaround (if
acceptable), can you transform the data at the data source and make the
Target a Batch? In other words, instead of
BatchId, Code, Name, Target, Actual
1, 1, One, 12, 10
1, 2, Two, 9, 10
2, 1, One, 12, 11
2, 2, Two, 9, 9
3, 1, One, 12, 12
3, 2, Two, 9, 11
Have
BatchId, Code, Name, Actual
1, 1, One, 10
1, 2, Two, 10
2, 1, One, 11
2, 2, Two, 9
3, 1, One, 12
3, 2, Two, 11
Target, 1, One, 12
Target, 2, Two, 9
and so on...
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote in
message news:u5l62PufEHA.636@.TK2MSFTNGP12.phx.gbl...
> Hi Teo,
> Thank you for the reply, but that doesn't fix the problem.
> The target column exists only once and doesn't repeat like the "Batch ..."
> column does. I don't know exactly how to explain this issue further more
> because my "matrix-knowledge" doesn't reach that far.
> What I did to create the matrix is the following:
> - Add a matrix control
> - Drop the "BatchId" field into the "columns" cell.
> - Drop the "Code", "Name" and "Target" fields into the "rows" cells
(the
> seconds and third fields added as row-groups.
> I managed to get subtotals for the "Batch..." columns by adding a new
> row-group (expression =1) and Selecting "Subtotals" for this cell. But I'd
> rather not see this column in my report and it doesn't totalize the target
> column.
> Maybe you could post me an example based on the data described below? (if
> it's not too much trouble) I like solving my own problems, but are pulling
> my hairs out on this one.
> Erik Tamminga, MCSD
> "Teo Lachev" <teo@.nospam.prologika.com> wrote in message
> news:%23IHSQLtfEHA.904@.TK2MSFTNGP09.phx.gbl...
> > Erik,
> >
> > Unless I am missing something, in your case you can simply add a matrix
> > subtotal element by right-clicking on a matrix group in the report
layout
> > and choose Subtotal.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > http://www.prologika.com
> >
> >
> > "Erik Tamminga" <REVERSE_THIS_agnimmate@.REVERSE_THIS_nerrats.ln> wrote
in
> > message news:e2vLhFrfEHA.3612@.TK2MSFTNGP12.phx.gbl...
> > > Hi,
> > >
> > > I'm trying to build a report displaying a matrix that looks like the
> > > following but have some trouble composing it:
> > >
> > > Required output :
> > >
> > > Code, Name, Target, Batch 1, Batch 2, Batch 3
> > > 1, One, 12, 10, 11, 12
> > > 2, Two, 9, 10, 9, 11
> > > Totals: 21, 20, 20, 23
> > >
> > > From the following data
> > >
> > > BatchId, Code, Name, Target, Actual
> > > 1, 1, One, 12, 10
> > > 1, 2, Two, 9, 10
> > > 2, 1, One, 12, 11
> > > 2, 2, Two, 9, 9
> > > 3, 1, One, 12, 12
> > > 3, 2, Two, 9, 11
> > >
> > > (Assumption: all targets for a specified code are the same, batch=1
> code=1
> > > target=12 -> all targets for code=1 equal 12)
> > >
> > > The main issue I have is adding the target totals.
> > >
> > > Could someone please help me with this?
> > >
> > > Erik
> > >
> > >
> >
> >
>

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

Master..sys_datatypes_info and SysxLogins

Hello all,
Recently while testing are application agaisnt Sql 2005, I noticed that the
above tables are missing. i know that it is bad practice to code agaisnt
system tables but it is done. I need to know the equivalent tables in SQL
2005 or better still a process for retrieving literal_suffix and
literal_prefix information.
Thanks
Ronald
> Recently while testing are application agaisnt Sql 2005
http://www.aspfaq.com/sql2005/show.asp?id=1