distinct: removing dups

should be a simple resolution but I'm not familiar enough...

I have the following [simplified] query which generates duplicate rows that I need to get rid of.

SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE

When I insert the distinct command, DB2 tells me:
SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

The datatype of DESCRIPTION is LONG VARCHAR and that cannot change nor the need to query that column. It seems this is preventing distinct from working. It will work without DESCRIPTION being pulled, of course, but again - I need that column.

How do I use SQL to remove the duplicates I am getting since distinct seemingly cannot be used in this scenario ? Can a "WHERE" clause somehow help ?

ThanksCan you use:SELECT MY_ID, DESCRIPTION, NAME
FROM MYTABLE
GROUP BY MY_ID, DESCRIPTION, NAME-PatP|||looks like group by doesn't like DESCRIPTION either... same error.

SQL0134N Improper use of a string column, host variable, constant, or
function "DESCRIPTION". SQLSTATE=42907

Distinct Rows but All Columns

I searched but did not find the answer to my specific question...
I have a table where I need to return all columns, however, I need only
distinct rows for one of the columns. The problem is that the data
types are uniqueidentifiers.
The DISTINCT keyword works on the entire row so I cannot simply use
SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
FROM dbo.ReportingTransactions AS A
I have looked at grouping with no luck either. How can I get all
columns but distinct rows on one of the columns?
Here's my table:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
Sample Data in Table
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:30:01.123
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:42:59.200
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357
937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:14:25.857
Desired Results
--
D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F 20
05-11-12
00:33:32.873
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB 20
05-11-08
20:49:11.450
D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C 20
05-11-11
21:38:01.543
A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD 20
05-11-14
16:13:21.577
CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF 20
05-11-10
20:15:36.357SELECT * FROM MyTable
WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
See if that helps you.
Yosh
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Sorry, I wasn't clear. I need the column called 'SomeForeignKey' to be
distinct. Using the same basic query you suggested but with the other
column doesn't work.
SELECT * FROM MyTable WHERE SomeForeignKey in (SELECT DISTINCT
SomeForeignKey FROM MyTable)
Returns all rows...not the rows with a DISTINCT SomeForeignKey value.|||Won't this return exactly the same recordset as SELECT * FROM MYTABLE since
LASTUPDATE will *always* be in the dataset returned by (SELECT DISTINCT
LastUpdated FROM MyTable)?
"Yosh" <yoshi@.nospam.com> wrote in message
news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
> SELECT * FROM MyTable
> WHERE LastUpdated in (SELECT DISTINCT LastUpdated FROM MyTable)
> See if that helps you.
> Yosh
>
> <Doug@.icr-consulting.com> wrote in message
> news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>|||How would you determine which row to return? From the looks of the
desired results, what you really want is the last updated row for a
particular FK value - which is different than distinct on one column only.
-- correlated subquery
select MyPK, SomeForeignKey, LastUpdated
from mytable t1
where lastupdate = (select max(lastupdated) from mytable where
someforeignkey = t1.someforeignkey)
or
-- derived table
select t1.MyPK, t1.SomeForeignKey, t1.LastUpdated
from mytable t1
join (
select someforeignkey, max(lastUpdated) as lastupdated
from mytable
group by someforeignkey
) t2
on t1.someforeignkey = t2.someforeignkey
and t1.lastupdated = t2.lastupdated
Doug@.icr-consulting.com wrote:
> I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
2005-11-10
> 20:15:36.357
>|||This comes very close:
CREATE TABLE [dbo].[MyTable]
(
[MyPK] [uniqueidentifier] NOT NULL,
[SomeForeignKey] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL
)
insert into mytable values('D301D519-BC09-411B-8F31-8EACFD2E4775',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:33:32.873')
insert into mytable values('2827DA4D-EE8F-46ED-95D2-2372F727F510',
'F6DA8213-E958-4AE4-A2AB-032EE120831F', '2005-11-12 00:30:01.123')
insert into mytable values('AC1B46B6-9C85-4FD7-830D-144E573CEFF2',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:49:11.450')
insert into mytable values('E1C45075-DEEE-47CB-8E8A-CFA37EFFA377',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:47:27.967')
insert into mytable values('9EC6A9E1-BDE1-494E-9010-13D0C786557E',
'ACA0EA1A-C729-477E-993A-073F12601FDB', '2005-11-08 20:42:59.200')
insert into mytable values('D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD',
'7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C', '2005-11-11 21:38:01.543')
insert into mytable values('A46E3001-0B4C-4669-8EA6-1409CDD1FDC5',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('7AD20272-39FD-43AA-B18C-7F6D265E3962',
'8FC9E770-0B49-4656-A74A-5BD8B3C71CBD', '2005-11-14 16:13:21.577')
insert into mytable values('CF356908-9A77-4B70-8CBD-A4221DED72FC',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:15:36.357')
insert into mytable values('937143F8-4509-400D-81D9-B19EB02F97B0',
'9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF', '2005-11-10 20:14:25.857')
SELECT *
FROM MYTABLE T1
WHERE LASTUPDATED = (SELECT MAX(LASTUPDATED) FROM MYTABLE T2 WHERE
T1.SOMEFOREIGNKEY = T2.SOMEFOREIGNKEY)
drop table [MyTable]
The only real problem that I see is that when there are two values with the
same SOMEFOREIGNKEY and LASTUPDATED values it still returns multiple rows.
I'd have to think about that one a bit. I think the crux of the issue here
is that there is actually nothing distinct about the record that you want to
select.
<Doug@.icr-consulting.com> wrote in message
news:1132090281.548288.36160@.g49g2000cwa.googlegroups.com...
>I searched but did not find the answer to my specific question...
> I have a table where I need to return all columns, however, I need only
> distinct rows for one of the columns. The problem is that the data
> types are uniqueidentifiers.
> The DISTINCT keyword works on the entire row so I cannot simply use
> SELECT DISTINCT A.TransactionID, A.OfferID, A.LastUpdated
> FROM dbo.ReportingTransactions AS A
> I have looked at grouping with no luck either. How can I get all
> columns but distinct rows on one of the columns?
> Here's my table:
> CREATE TABLE [dbo].[MyTable]
> (
> [MyPK] [uniqueidentifier] NOT NULL,
> [SomeForeignKey] [uniqueidentifier] NOT NULL,
> [LastUpdated] [datetime] NOT NULL
> )
> Sample Data in Table
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> 2827DA4D-EE8F-46ED-95D2-2372F727F510 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:30:01.123
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:47:27.967
> 9EC6A9E1-BDE1-494E-9010-13D0C786557E ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:42:59.200
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> 7AD20272-39FD-43AA-B18C-7F6D265E3962 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
> 937143F8-4509-400D-81D9-B19EB02F97B0 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:14:25.857
> Desired Results
> --
> D301D519-BC09-411B-8F31-8EACFD2E4775 F6DA8213-E958-4AE4-A2AB-032EE120831F
> 2005-11-12
> 00:33:32.873
> AC1B46B6-9C85-4FD7-830D-144E573CEFF2 ACA0EA1A-C729-477E-993A-073F12601FDB
> 2005-11-08
> 20:49:11.450
> D5D5004E-C1C5-4FC2-AD2B-310BF08F26DD 7F4FE5BF-5D1F-4BF6-ABEF-51BA15EF9A5C
> 2005-11-11
> 21:38:01.543
> A46E3001-0B4C-4669-8EA6-1409CDD1FDC5 8FC9E770-0B49-4656-A74A-5BD8B3C71CBD
> 2005-11-14
> 16:13:21.577
> CF356908-9A77-4B70-8CBD-A4221DED72FC 9B5C8A0F-7FFC-4615-A837-5E6F6B398DCF
> 2005-11-10
> 20:15:36.357
>|||Actually, the LastUpdated column is purely informational (as far as my
use of it). It's the SomeForeignKey column that I need to be unique.
Utlimately, I will use the SomeForeignKey column to join on another
table. Once I get the query to return the SomeForeignKey column in
distinct rows I can figure out the rest.
BTW: Thanks for you input thus far.|||Yes. You are correct.
What was I thinking.
Thanks,
Yosh
"Steve Hamilton" <shamilton@.community.nospam> wrote in message
news:OoT25Hj6FHA.3544@.TK2MSFTNGP09.phx.gbl...
> Won't this return exactly the same recordset as SELECT * FROM MYTABLE
> since LASTUPDATE will *always* be in the dataset returned by (SELECT
> DISTINCT LastUpdated FROM MyTable)?
>
>
> "Yosh" <yoshi@.nospam.com> wrote in message
> news:ORtQG5i6FHA.1020@.TK2MSFTNGP15.phx.gbl...
>|||If it is not the combination of SOMEFOREIGNKEY and LASTUPDATED then I am
having a hard time grasping what is distinct about the dataset that you want
returned. It sounds like what you want is one single record returned for
each distinct SomeForeignKey value in your table. The problem with that is
that multiple records exist in your table for the value and you have to in
some form or another tell sql server exactly which record to return, it is
not going to guess on your behalf It sounds like what you need to do is to
define some rule to determine which record for the particular SOMEFOREIGNKEY
value will be returned. Once you have done that crafting the query in the
syntax of what I submitted earlier should be feasible. Hope this helps.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>|||I looked at your postings and my replies and decided to try and clarify
things a bit. In your example that you originally posted you wanted the
following record in the returned result:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
In your example data the following records contain that particular
SomeForeignKey value:
AC1B46B6-9C85-4FD7-830D-144E573CEFF2 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:49:11.450
E1C45075-DEEE-47CB-8E8A-CFA37EFFA377 | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:47:27.967
9EC6A9E1-BDE1-494E-9010-13D0C786557E | ACA0EA1A-C729-477E-993A-073F12601FDB
| 2005-11-08 20:42:59.200
In this case how did you pick the particular record that you wanted to
return? Once you identify the logic to pick the specific record it should
be possible to write a query that returns the expected result. If the
particular record doesn't matter you could simply use MAX(CAST(MYKEY AS
VARCHAR(36))) to identify a single distinct record.
<Doug@.icr-consulting.com> wrote in message
news:1132093840.266390.103410@.g43g2000cwa.googlegroups.com...
> Actually, the LastUpdated column is purely informational (as far as my
> use of it). It's the SomeForeignKey column that I need to be unique.
> Utlimately, I will use the SomeForeignKey column to join on another
> table. Once I get the query to return the SomeForeignKey column in
> distinct rows I can figure out the rest.
> BTW: Thanks for you input thus far.
>

distinct row count in a table.

Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.Hi,

I want a count of distinct rows in a table through a single query -- is it possible?

eg.

table-

create table ch1 (a int, b int, c int, d int)

insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,1,1,1)
insert ch1 values (2,2,2,2)
insert ch1 values (1,3,4,5)

Here distinct row count in a table is 3 which I want to achieve thro a query.

if I do

select count(distinct a) from ch1 it works fine and gives me output as 2.

but this is not working

select count(distinct a,b,c,d) from ch1 - any workaround to find the distinct row count in a table??

Please reply.

Cheers!
Ram.|||Try this...

SELECT COUNT(*)
FROM
(SELECT DISTINCT * FROM ch1)ch1|||Or
SELECT COUNT(DISTINCT *) AS Distinct_Rows FROM ch1|||:shocked:
select sum(case when count(*)>1 then 1 else 1 end)
from ch1 group by a,b,c,d|||threads merged

ramshree, please do not post the same question into multiple forums|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1|||I have an example below: You should use the "having" clause.
db2 "select serialno,count(*) from svcprd.bcbs_unix_sysinfo group by serialno having count(*)>1I think that you're "close, but no banana" on this... The code that you posted will actually count the non-distinct rows (how many rows have at least one duplicated row elsewhere).

-PatP

DISTINCT on one column only

I have a table I'm running a query on:
SELECT DISTINCT guid, Department FROM table
I only want rows with unique guid's to be returned (there are a couple rows
with identical guids, and I can't fix the real problem of having multiple
guids)
This returns rows with distinct guids and departments obviously. I tried to
modify the query to:
SELECT DISTINCT(guid), Department FROM table
Trying to get it to run the distinct on just the guid column. Still didn't
do it.
What do I need to do to get just the unique guids?
PS The query:
SELECT DISTINCT guid, FROM table
works perfectly.Based on your narrative, you seem to be struggling with a poorly chosen
identifier namely guid. In any case, DISTINCT return distinct rows from a
table, to extract distinct values from a column you will have to use an
aggregate function with a GROUP BY clause like:
SELECT MAX( guid ), department
FROM tbl
GROUP BY department ;
Anith|||SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
David Portas
SQL Server MVP
--|||The unit of work in a SELECT statement is a entire **row**, not a
**column**. The SELECT DISTINCT is for a whole row. You still think
this is "left to right, one field at a time" file system. No wonder
you would have such a poor choice of keys -- you are mimicing a record
number in a file system. You need to stop programming and get a book
on RDBMS basics.
Actually, you need to get rid of that GUID column and get a valid
relational key.
SELECT silly_guid
FROM Foobar
GROUP BY silly_guid
HAVING COUNT(*) = 1;|||- Steve - wrote:
> I have a table I'm running a query on:
> SELECT DISTINCT guid, Department FROM table
> I only want rows with unique guid's to be returned (there are a
> couple rows with identical guids, and I can't fix the real problem of
> having multiple guids)
> This returns rows with distinct guids and departments obviously. I
> tried to modify the query to:
> SELECT DISTINCT(guid), Department FROM table
> Trying to get it to run the distinct on just the guid column. Still
> didn't do it.
> What do I need to do to get just the unique guids?
> PS The query:
> SELECT DISTINCT guid, FROM table
> works perfectly.
You need to figure out which duplicate qualifies as the row you want
returned and then implement the technique that David and Anith describe.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||This is really close to what I want.
The only problem is that the two rows with the same guid but different
departments, isn't showing at all. I'd like one line of it to show up. (I
don't care which one)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121876475.045441.292920@.g47g2000cwa.googlegroups.com...
> SELECT guid, MIN(department)
> FROM tbl
> GROUP BY guid
> --
> David Portas
> SQL Server MVP
> --
>|||> Actually, you need to get rid of that GUID column and get a valid
> relational key.
I only get to use the data. I have no saying whatsoever in how the data is
managed. It's completley out of my control.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121877566.139416.161400@.g47g2000cwa.googlegroups.com...
> The unit of work in a SELECT statement is a entire **row**, not a
> **column**. The SELECT DISTINCT is for a whole row. You still think
> this is "left to right, one field at a time" file system. No wonder
> you would have such a poor choice of keys -- you are mimicing a record
> number in a file system. You need to stop programming and get a book
> on RDBMS basics.
>
> Actually, you need to get rid of that GUID column and get a valid
> relational key.
> SELECT silly_guid
> FROM Foobar
> GROUP BY silly_guid
> HAVING COUNT(*) = 1;
>|||What you've described isn't what I'd expect. Try the following, which
works for me:
CREATE TABLE tbl (guid UNIQUEIDENTIFIER NOT NULL, department
VARCHAR(10) NOT NULL /* PRIMARY KEY not specified */)
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','A'
INSERT INTO tbl SELECT '9EF7940E-B5A9-4E81-8959-244A7CF31E5F','B'
INSERT INTO tbl SELECT 'C1864626-28CE-4BE3-8171-F4E989DDF114','C'
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
Result:
guid
-- --
9EF7940E-B5A9-4E81-8959-244A7CF31E5F A
C1864626-28CE-4BE3-8171-F4E989DDF114 C
(2 row(s) affected)
Did you do something different? Post some code to reproduce it (like
I've done) if you need more help.
Note that you could also show just the duplicated rows:
SELECT guid, MIN(department)
FROM tbl
GROUP BY guid
HAVING COUNT(*)>1
David Portas
SQL Server MVP
--|||>> I only get to use the data. I have no saying whatsoever in how the data
is managed. It's completley out of my control. <<
Sorry about that.
I do not drive the train
I cannot ring the bell
but let the damn thing jump the track
and see who catches Hell.

DISTINCT MonthName for a lot of dates....

Hi all,
I have a table with several rows, each has a datetime field.
I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...
table structure:
PregnancyLog
LogID int
LogDateTime datetime
sample data
LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05
Expected results
MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8
Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.
Regards
Rob"Rob Meade" wrote ...

> Any help would be appreciated
I hate it when this happens...looks like I might have sussed it myself...
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
Does that look acceptable to anyone? It gives me the results I wanted but I
just wanted to make sure..
Regards
Rob|||On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

>"Rob Meade" wrote ...
>
>I hate it when this happens...looks like I might have sussed it myself...
>SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
>FROM PregnancyLog
>GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
>ORDER BY MONTH(LogDateTime)
>Does that look acceptable to anyone? It gives me the results I wanted but
I
>just wanted to make sure..
>Regards
>Rob
>
Hi Rob,
Looks good.
Here's an (untested) alternative:
SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)
Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.
But its still less code than I had - many thanks :o)
Regards
Rob|||Hi Hugo,
Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...
Ie...
MonthName MonthNumber Counter
January 1 2
February 2 6
March 3 15
Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again...nightmare..
Any help appreciated.
Regards
Rob|||On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

>Hi Hugo,
>Any ideas how I would add a "count" to the end of the result set of the
>number of log items for each month returned by the existin query...
>Ie...
>MonthName MonthNumber Counter
>January 1 2
>February 2 6
>March 3 15
>Any help would be really appreciated, I've tried adding COUNT(LogID) to my
>query, but then I get message telling me that things need adding to the
>aggregate function or the group by clause, which I did try adding again but
>then I have to lose the order by or else I get EVERY row
>again...nightmare..
>Any help appreciated.
>Regards
>Rob
>
Hi Rob,
If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
> COUNT(LogID) AS Counter
> FROM PregnancyLog
> GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
> ORDER BY MONTH(LogDateTime)
> should work. If not, you'll need to provide more information, as
> described in www.aspfaq.com/5006.
Hi Hugo,
Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY...
Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)
Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)
Regards
Rob|||On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:
(snip)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
Hi Rob,
If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->
Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast.....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

distinct count with multiple partitions?

We analyze much of our data using distinct measures. Some of the underlying sources - i.e. daily page views are heavy and have millions of rows a day. Because of this I've created daily partitions to only process the incremental data that's arrived. However, I'm curious - how does distinct count perform when it needs to rollup over multiple partitions?

IE Say it's Dec 25th, and I have 25 unique partitions for each day of December thus far. Internally, how do the distinct measures correctly accumulate the unique instances of my measure? Are there any significant performance concerns to be aware of?Distinct Count measure will work fine with multiple partitions. AS keeps the distinct count values inside the partitions, and therefore it can correctly aggregate across them. Partition per day should be OK too.|||Arjun: Make sure you are on at least SQL 2005 SP1 QFE rollup, preferably SP2 CTP2. There were a couple of bugs fixed for distinct count measures over multiple partitions.|||Good to know, thanks! Jeff, do you have any details on the nature of the issues with distinct counts over multiple partitions?

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying Total number of Rows in a Report in Page Header.

Hi,

I have requirement to display Total number of Rows in a Report in Page Header.

I have written the following code in Page header it shows RowCount for the Page only.

=Count(ReportItems!textboxInTableCell.Value)

Can anyone please help on this?

Regards

Raghav

By Total number of reports in the report do you mean the number of rows returned by the Dataset query? If so, add a textbox in your Report Body with the expression =CountRows("DataSet1") with the name of your Dataset in place of DataSet1.

Then refer to this textbox directly in the Page Header.

This should give you the total row count for your Dataset.

-Aayush

|||

Thanks aayush,

I used =CountRows() in body header and set the RepeatWith property to "tableName" and it works as Page header.

Regards

Raghavendra

Displaying the row number in a query

Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.

Thanks in advance for all.Is it not doable?

Originally posted by RaedT
Hi,
My query is retrieving rows from a table.
All what I need is to display the row number which is simply a consecutive number.
Can any one advise me if there is a direct thing to do this in the SELECT stmt?
I can do it through creating a temp table then add IDENTITY column pla pla pla ..I need a direct way through the query itself.
In Oracle I can use RowNum in the query.

Thanks in advance for all.|||The short answer is NO. TSQL operations are set based, and no ordering is guaranted by the server unless the developer or programmer specifies it, thus row numbering is pointless because the same query run on two different occasions could result in the same record being assigned different row numbers.

The long answer is YES, if your result set is sorted by a unique key or combination of columns, then you can write a Select statement that loops back on itself and counts the number of records less than each record. This is an expensive query to run and can be difficult to debug, so my recommendation to you would be to use a temporary table (actually, a table variable is more efficient) as long as it suits your needs.

Why do you need the results numbered? While there are some circumstances where this is beneficial, it is often a sign of problems with the database schema or the application design concept.

blindman|||If you don't have already a client, I may consider to put your SELECT statement in the software of a (ADO) client, where you can make use of the AbsolutePosition property of a recordset.

displaying some predefined no. of rows

hi,

i wanted to know if there is any sql statement that enables the query to display say first '10' rows only.

Eg: suppose

SELECT * from Location;

returns 25 rows and i want only 10 rows in that to be displayed.

How can this be done? Help please.

You can use TOP statement like

SELECT TOP 10 * from Location

Is good to include order by for example table identity column if you would like to receive last inserted records

SELECT TOP 10 *
from Location
order by loc_ID desc

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?

Displaying rows by month

Hi All,
I have a column in the table of type datetime.I need to get all the rows in the table but month wise.For Ex:

Jan 2003
(Rows whose date is in Jan 2003)
Feb 2003
(Rows whose date is in Feb2003)
.
.
.
Jan 2004
(Rows whose date is in Jan 2004)
Feb 2004
(Rows whose date is in Feb2004)
.
.
so on...

Can any body give my SQL query to get the desired results.
Thanks a lot,
Kumar.For January, 2003:

SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 1 AND YEAR(DateColumn) = 2003

For February, 2003:
SELECT * FROM DateSample
WHERE MONTH(DateColumn) = 2 AND YEAR(DateColumn) = 2003

and so on...|||you need to use just simple order by this datetime field
if I understand your question

Displaying Rows as Columns

Hello All !
I am using MS-SQL Server.
I have following table :
tblExchangeData
Columns are :
------
1) Trans_Date
2) Sales
3) Purchase
4) Purchase_Brokerage
5) Sales_Brokerage
6) Branch_Name
7) ExchageSegment
There are only two ExchangeSegments : BSE & NSE
I want to calculate brokerage for ExchangeSegments.
(Brokerage=Purchase_Brokerage+Sales_Brokerage)
O/p Should be (Group By Branch):
Branch_Name BSE NSE
------------------
xx 10000 20000
.... so on

Please post the query.
Thanks in advance.

SQL Server have three none standard Aggregate functions that are not Relational Compute Sum, Cube and Rollup. The last two are super agreggate functions. Run a search for all three in the BOL(books online). Try the links below for examples. Hope this helps.

http://www.oreilly.com/catalog/wintrnssql/chapter/ch01.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_414d.asp

|||

Here is some source that will allow you to create a SProc and than you pass virtually whatever you want and the Rows Will Become Columns - Some Examples at the end of script!
You know how to configure the data set in MS RS to call a stored Procedure? (With all due respect)
CREATE PROCEDURE spPivot_Table
@.cTable varchar(80),
@.cDown varchar(80),
@.cAcross varchar(80),
@.cFunc varchar(80),
@.cAggFld varchar(80),
@.cWhere varchar(200)
As

Drop Table TempUniq
Drop Table TempPivot

Declare @.cColTtl varchar(80),
@.cSQLStr varchar(200),
@.cSQL varchar(8000),
@.nRows Int,
@.nCntr Int

-- Generate Pivot Key Table
Set @.cSQL = 'Select Distinct'+@.cAcross+' as Pivot_Value Into TempUniq From'+@.cTable+' Where'+@.cWHere+' Order By 1 '
Exec(@.cSQL)
Select IDENTITY(int, 1,1) as Pivot_Row,@.cFunc as Pivot_Func, @.cAggFld as Pivot_AggFld,@.cAcross as Pivot_Fld,@.cColTtl as Pivot_Col,Pivot_Value,@.cSQLStr as Pivot_SQL Into TempPivot From TempUniq Order By Pivot_Value

Update TempPivot Set Pivot_Col = 'Col_'+Replace(RTrim(LTrim(Convert(varchar(80),Pivot_Value))),' ','_')

-- Build and Execute Pivot SQL
Update TempPivot Set Pivot_SQL=LTrim(RTrim(Pivot_Col))+'='+Pivot_Func+'(case when '+Pivot_Fld+'=Pivot_Value and Pivot_Col='''+Pivot_Col+''' Then '+Pivot_AggFld+' else Null end)'
Select @.nRows=Max(Pivot_Row),@.nCntr=Min(Pivot_Row) From TempPivot
Set @.cSQL=''
While @.nCntr <= @.nRows
Begin
Select @.cSQL=@.cSQL+','+Pivot_SQL From TempPivot WherePivot_Row=@.nCntr
Set @.nCntr=@.nCntr+1
End

Set @.cSQL='Select'+@.cDown+','+Substring(@.cSQL,2,8000)+' From'+@.cTable+' Join TempPivot on('+@.cAcross+'=Pivot_Value) Where'+@.cWhere+' Group By'+@.cDown+' Order By'+@.cDown
Exec(@.cSQL)

EXAMPLES OF HOW TO USE -- Just execuate the SP within SQL Query Analyzer These samples use the the Northwind DB -

-- Capabilities
-- Any Combination to "Down" or By Field -- s
-- Functions Available: Sum, Avg, Min, M -- ax, Count, STD, ...
-- Across may be an expression Substring -- (ShipCountry,1,1) = Across A,B,C,D,...-- r>
-- Samples
-- Exec spPivot_Table {Table},{By Fields -- },{Across Colums},{Agg Function},{Pivot -- Field},{Filer}
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- 1=1'
-- Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Year(OrderDate)','Sum', -- 'Freight','1=1'
-- Exec spPivot_Table 'Orders','Employee -- ID,ShipCountry','Substring(ShipCountry,1 -- ,1)','Sum','Freight','1=1'

|||hi,
can the above store procedure method be used in reporting services...because i need to generate result like the example of displaying rows as columns.|||

Angela:

Yes, I have used the SP many times within Reporting Services.

Try these steps and I think you will get the results you want.

Create the Stored Procedure - CREATE PROCEDURE spPivot_Table You can by the way name this procedure anything you want and I normally name stored procedure the same name as my reporting services report.
(This stored procedure is really executing YOUR SQL statement (Dynamic SQL) and since Dynamic SQL is being executed within the Stored Procedure ensure you eliminate ANY "white space or blanks" in your SQL Statement.
After you create the Stored Procedure open SQL Query Analyzer and construct your SQL statement - once your SQL statement works open a new window in SQL Query Analyzer and then execute the following:
Exec Stored Procedure Name 'YOUR SQL STATEMENT' and within SQL Query Analyzer you should see the results of the Stored Procedure and the cross tab created on the pivot column name. After you get the results you want from the stored procedure and your SQL statement just copy the statement in SQL Query Analyzer and then go to your report in Reporting Services.
In Reporting Services (and your report you are working on) create a data set and when creating the data set Specify COMMAND TYPE as a storedprocedure and in the QUERY STRING "Paste" your statement you copied from SQL Query Analyzer then select OK...
Test or run your data set in Reporting Services and you should see the same results that Reporting Services displays after executing the stored procedure that you would see within SQL Query Analyzer. VOILA! The fields names returned from this stored procedure are different from the names returned within reporting services with a standard SQL statement - but they are obvious once you run the data set for the first time with the pivot table stored procedure.
You can accomplish the same by using a Matrix within your report with reporting services in that the matrix is really doing the pivot table or cross tab for you but I find that it is easier to use the pivot table stored procedure.
Hope this helps!
Best Regards - Joe

|||Also, please look at the post "Limiting Matrix Columns" I provided another solution there as well.........|||Hello !
Yes, this stored procedure is very useful. I have used it many times. And performance is also good.
Thanx Joe|||

Please help. i created the SP in sql 2000 in Northwind Database. This worked. I tried to

run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
and below was the result.

Not sure if my synatx above is causing this or if there are other problems too.
Thanks

Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 11
Cannot drop the table 'TempUniq', because it does not exist in the system catalog.
Server: Msg 3701, Level 11, State 5, Procedure spPivot_Table, Line 12
Cannot drop the table 'TempPivot', because it does not exist in the system catalog.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Where'.
Server: Msg 208, Level 16, State 1, Procedure spPivot_Table, Line 25
Invalid object name 'TempUniq'.

|||Hello:
Please execute the following statement:
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'
Your statement contained invalid field names from the table:
run the following : Exec spPivot_Table 'Orders','ShipCoun -- try','Year(OrderDate)','Sum','Freight',' -- Year(OrderDate)>1996'
Please see the BOLD field names above.
The first time you execute the stored procedure - you will get messages that the temp tables are not there - so a message is provided that they can't be deleted because they do not exist. Any further executions will not show those initial messages.
Also, look at the post for "Limiting Matrix Columns - I provided another script that behaves a little differently -
Hope this helps!
Best Regards,
Joe|||Hello:
Please do not take this the wrong way! Please do not use SP as a prefix for creating your stored procedures - this is a Microsoft nameing convention and things may get screwed up in the future. I always prefix my stored procedures with 'EX' for execute.
Best Reagrds,
Joe|||Run it as :
Exec spPivot_Table 'Orders','ShipCountry','Year(OrderDate)','Sum','Freight','Year(OrderDate)>1996'|||There is no problem using an "sp" prefix for a stored procedurename. The issue is with the "sp_" prefix, as SQL Server willfirst try to look in the Master database for such-named storedprocedures. I have recently read an article which explores thisissue, and the author found there is actually a negligible performancepenalty with the "sp_" prefix -- not worth the trouble to go back andrename existing stored procedures.
|||

Thanks, can i create a report using asp.net using the Stored procedure, and if so please

point me in the right direction, allowing a refresh at a push of a button or something like that ??

Thanks

Gavin

|||Hi there,
i have a table result showing like this
Code OrderName
-- ----
123 AAA
111 BBB
and i select some other fields frm another table with column name call
TestResult but i wan to display the result like this;
TestResult Para1 Para2 Para3 Para4
---- -- --- -- ---
1 123 AAA 111 BBB
2 123 AAA 111 BBB
is there anyway of geting something like the above example?
|||You want to display each and every cell as column. But what is use of it?

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from
a table, for eg if the table has 5 columns and two rows have same values for
all these five columns , the sql statement should be able to find all such
matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harshaselect col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/feat...cle.php/2235081
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All,
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching row
s in the table and display them to the user.
How would i go about doing this.
thank you
harsha
select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/featu...le.php/2235081
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying related rows from a table

Dear All
I want to write an SQL program that would display all identical fields from a table, for eg if the table has 5 columns and two rows have same values for all these five columns , the sql statement should be able to find all such matching rows in the table and display them to the user
How would i go about doing this
thank yo
harshaharsha,
You can code a self-join on the table.
Ex. SELECT A.* FROM mytable A JOIN mytable B ON
a.col1 = b.col1 and a.col2 = b.col2 and b.col3 = b.col3
and ....
Might be other ways, but this should work for you.
Doug
>--Original Message--
>Dear All,
> I want to write an SQL program that would
display all identical fields from a table, for eg if the
table has 5 columns and two rows have same values for all
these five columns , the sql statement should be able to
find all such matching rows in the table and display them
to the user.
>How would i go about doing this.
>thank you
>harsha
>.
>|||select col1, col2, col3, col4, col5 from yourtable
group by col1, col2, col3, col4, col5
having count(*) > 1
Here is more information about finding duplicates:
http://www.databasejournal.com/features/mssql/article.php/2235081
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"harsha mogaligundla" <anonymous@.discussions.microsoft.com> wrote in message
news:B1AC6266-23C0-4D19-B47C-9B95948140FD@.microsoft.com...
> Dear All,
> I want to write an SQL program that would display all
identical fields from a table, for eg if the table has 5 columns and two
rows have same values for all these five columns , the sql statement should
be able to find all such matching rows in the table and display them to the
user.
> How would i go about doing this.
> thank you
> harsha

displaying records in two tables

HI,
Need some help in displaying the records in two table( from a single
dataset).
To be precise i have a dataset which returns say 10 rows, i want to
display the odd numbered rows( i mean the alternate rows) in the first
table and then the even rows in the second table. I'm using RS 2K.
This is a do or die situation with RS reports, else i might have to all
my reports using crystal reports. Appreciate if some one can quickly
fill my hope.
Thanks in advance,
--VenkatSimple enough.
First create your initial table. Set the Visibility/Hidden property of
the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
will give you your odd numbered rows.
Copy that table and paste the copy into the report body. Edit the
Visibility/Hidden property of the copy's detail row to
=IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
your even numbered rows.
Make sure you set the property for the row and not individual cells or
you'll end up with weird white spaces showing up.
It's probably also possible to set up filters for the dataset that
would accomplish this but you would have to find an alternative to the
RowNumber() function as it can't be used in filters. If your datset
includes an incrementally numbered field that could work.
Best regards
toolman
venkat.oar@.gmail.com wrote:
> HI,
> Need some help in displaying the records in two table( from a single
> dataset).
> To be precise i have a dataset which returns say 10 rows, i want to
> display the odd numbered rows( i mean the alternate rows) in the first
> table and then the even rows in the second table. I'm using RS 2K.
> This is a do or die situation with RS reports, else i might have to all
> my reports using crystal reports. Appreciate if some one can quickly
> fill my hope.
> Thanks in advance,
> --Venkat|||Please note my editting below:
toolman wrote:
> Simple enough.
> First create your initial table. Set the Visibility/Hidden property of
> the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> will give you your odd numbered rows.
> Copy that table and paste the copy into the report body. Edit the
> Visibility/Hidden property of the copy's detail row to
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> your even numbered rows.
EDITTING: The above should be reversed.
=IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
mod 2 = 1, True,False) will HIDE the odd numbers.
Sorry for the mix-up.
> Make sure you set the property for the row and not individual cells or
> you'll end up with weird white spaces showing up.
> It's probably also possible to set up filters for the dataset that
> would accomplish this but you would have to find an alternative to the
> RowNumber() function as it can't be used in filters. If your datset
> includes an incrementally numbered field that could work.
> Best regards
> toolman
> venkat.oar@.gmail.com wrote:
> > HI,
> >
> > Need some help in displaying the records in two table( from a single
> > dataset).
> >
> > To be precise i have a dataset which returns say 10 rows, i want to
> > display the odd numbered rows( i mean the alternate rows) in the first
> > table and then the even rows in the second table. I'm using RS 2K.
> >
> > This is a do or die situation with RS reports, else i might have to all
> > my reports using crystal reports. Appreciate if some one can quickly
> > fill my hope.
> >
> > Thanks in advance,
> > --Venkat|||Thank you for your response, i will implement it to see if every thing
works fine, mean while can you tell me how to restrict the # of columns
displayed per table
Appreciate your help.
--Venkat
toolman wrote:
> Please note my editting below:
> toolman wrote:
> > Simple enough.
> > First create your initial table. Set the Visibility/Hidden property of
> > the detail row to =IIF(RowNumber(Nothing) mod 2 = 1, True,False). This
> > will give you your odd numbered rows.
> > Copy that table and paste the copy into the report body. Edit the
> > Visibility/Hidden property of the copy's detail row to
> > =IIF(RowNumber(Nothing) mod 2 = 0, True,False). This will give you
> > your even numbered rows.
> EDITTING: The above should be reversed.
> =IIF(RowNumber(Nothing) mod 2 = 0, True,False) will HIDE the even
> numbered rows so the odd numbered ones show. =IIF(RowNumber(Nothing)
> mod 2 = 1, True,False) will HIDE the odd numbers.
> Sorry for the mix-up.
> > Make sure you set the property for the row and not individual cells or
> > you'll end up with weird white spaces showing up.
> > It's probably also possible to set up filters for the dataset that
> > would accomplish this but you would have to find an alternative to the
> > RowNumber() function as it can't be used in filters. If your datset
> > includes an incrementally numbered field that could work.
> > Best regards
> > toolman
> >
> > venkat.oar@.gmail.com wrote:
> > > HI,
> > >
> > > Need some help in displaying the records in two table( from a single
> > > dataset).
> > >
> > > To be precise i have a dataset which returns say 10 rows, i want to
> > > display the odd numbered rows( i mean the alternate rows) in the first
> > > table and then the even rows in the second table. I'm using RS 2K.
> > >
> > > This is a do or die situation with RS reports, else i might have to all
> > > my reports using crystal reports. Appreciate if some one can quickly
> > > fill my hope.
> > >
> > > Thanks in advance,
> > > --Venkat

displaying just the differences between 2 tables datasets

Hi, I have 2 identically defined tables that should have duplicate rows
(majority). I want a way of displaying just the data that doesn't exit in
either table, one table at a time for reporting purposes.
I have coded this already using a 3rd table that holds all data that matches
2 tables and then deleting from both tables the data that matches the third
table and then doing a select from the result in each table.
I want to know how to do this more efficiently as this way seems clumsy and
slow. Can anyone help?For non-nullable columns:
SELECT A.*
FROM A
LEFT JOIN B
ON A.col1 = B.col1
AND A.col2 = B.col2
AND ... etc
WHERE B.col1 IS NULL
If you need to cope with NULLs by treating them as equal values in the
comparison:
SELECT col1, col2, ...
FROM
(SELECT 1 AS x, col1, col2, ...
FROM A
UNION ALL
SELECT 2 AS x, col1, col2, ...
FROM B) AS T
GROUP BY col1, col2, ...
HAVING MAX(x)=1
David Portas
SQL Server MVP
--|||select * from table1 where ID not in (select ID from table2)
union
& vice versa
Does this help?
Daniel
"sysbox27" <sysbox27@.discussions.microsoft.com> schrieb im Newsbeitrag
news:E691E4AE-E0B6-4120-A072-B42001AE47EB@.microsoft.com...
> Hi, I have 2 identically defined tables that should have duplicate rows
> (majority). I want a way of displaying just the data that doesn't exit in
> either table, one table at a time for reporting purposes.
> I have coded this already using a 3rd table that holds all data that
> matches
> 2 tables and then deleting from both tables the data that matches the
> third
> table and then doing a select from the result in each table.
> I want to know how to do this more efficiently as this way seems clumsy
> and
> slow. Can anyone help?
>|||Allow me to illustrate:
Let's compare these two tables:
create table dbo.Names1
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
create table dbo.Names2
(
NameID int identity (1, 1)
,[Name] nvarchar(64) primary key
)
go
insert dbo.Names1
(
[Name]
)
select N'Jack' as [Name]
union
select N'Phil'
union
select N'Rod'
union
select N'Bing'
go
insert dbo.Names2
(
[Name]
)
select N'Jack' as [Name]
union
select N'Tommy'
union
select N'Midge'
union
select N'Bing'
go
Like this:
select Combination.[Description] as [Description]
,Combination.[Name] as [Name]
from (
select 'Exists in Names1' as [Description]
,dbo.Names1.[Name] as [Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
union
select 'Exists in Names2'
,dbo.Names2.[Name]
from dbo.Names1
full join dbo.Names2
on dbo.Names2.[Name] = dbo.Names1.[Name]
where (dbo.Names1.NameID is null or dbo.Names2.NameID is null)
) Combination
where (Combination.[Name] is not null)
go
Is this what you're looking for?
ML|||thank you to everyone for taking the time to assist me.
much appreciated.