query assistance: distinct

Showing posts with label distinct. Show all posts

Wednesday, March 28, 2012

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '

>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query Help

How can I select the count of the distinct values of a
column '
Thanks.Try:
select
count (distinct MyCol)
from
MyTable
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
How can I select the count of the distinct values of a
column '
Thanks.|||SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
FROM YourTable
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.|||Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Please ignore the message about the column being a char
column........
Thanks.
>--Original Message--
>How can I select the count of the distinct values of a
>column '
>Thanks.
>.
>|||It makes no difference. You wanted a count of the distinct values. From
Northwind, try:
select
count (distinct CustomerID)
from
Orders
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:503401c4c5c7$6ed7d9a0$a301280a@.phx.gbl...
Thanks but I forgot to add that it is a char column with
the numbers in it '
>--Original Message--
>SELECT COUNT(DISTINCT YourColumn) AS CountYourColumn
>FROM YourTable
>--
>Adam Machanic
>SQL Server MVP
>http://www.sqljunkies.com/weblog/amachanic
>--
>
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
>> How can I select the count of the distinct values of a
>> column '
>> Thanks.
>
>.
>|||Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>|||Thanx much. It sure means a lot. I saw Itzik and his wife in April.
They're both doing well. :-)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"AnthonyThomas" <Anthony.Thomas@.CommerceBank.com> wrote in message
news:OBcRNPtyEHA.3820@.TK2MSFTNGP11.phx.gbl...
Hey, Tom!
Good to read you here.
Just wanted to pass along to you my enjoyment of your book with Itzik.
Thanks for taking the time to write it.
If you run into him before I do, pass along my appreciation to Itzik as
well.
Best regards,
Anthony Thomas
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23zQlmZcxEHA.908@.TK2MSFTNGP11.phx.gbl...
> Try:
> select
> count (distinct MyCol)
> from
> MyTable
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Jeff" <anonymous@.discussions.microsoft.com> wrote in message
> news:502a01c4c5c5$45115710$a301280a@.phx.gbl...
> How can I select the count of the distinct values of a
> column '
> Thanks.
>

Query help

Hello Everyone,

I have a query that looks something like this:

DEFINE @.VAR_A VARCHAR(6)

DECLARE trsite_cursor CURSOR FOR
SELECT DISTINCT AppField
FROM TABLE_1
ORDER BY 1

OPEN trsite_cursor

FETCH NEXT FROM trsite_cursor INTO @.VAR_A

WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 =' + @.VAR_A +
' ORDER BY 1,2,3;'
END
...
...
...
EXEC xp_sendmail @.query = @.SELCMD,
...
...

and the rest of the query.....

The column "AppField" in TABLE_1 has been defined as varchar. Let's
assume it contains the value: ABCD. When I run it, the query fails at
the SET @.SELCMD statement, saying that the column name ABCD is
invalid. It assumes that ABCD is a column name & not a value. However,
if AppField contains a numeric value, ex: 123, I don't get any errors
& the query outputs the desired results.

So, I guess, the question is: how do I make the SET @.SELCMD treat the
value in AppField as "ABCD" or 'ABCD' and not just ABCD?

Thanks,
Suhassurround the variable in quotes in the string

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = '' ' + @.VAR_A +
'' ' ORDER BY 1,2,3;'

also you need another fetch before the END in the while loop or you
loop infinitly|||"Suhas" <sgtembe@.hotmail.com> wrote in message
news:5c440ee2.0311201306.4e7f5323@.posting.google.c om...
> SET @.SELCMD ='SELECT * FROM TABLE_2
> WHERE Field1 =' + @.VAR_A +
> ' ORDER BY 1,2,3;'
> assume it contains the value: ABCD. When I run it, the query fails at
> the SET @.SELCMD statement, saying that the column name ABCD is
> invalid. It assumes that ABCD is a column name & not a value. However,

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

You need to enclose the value contained in @.VAR_A in single quotes when you
build the @.SELCMD string. In other words, you need to create a string that
contains single quotes. To do that, you use a *pair* of single quotes. So,
the SET command should look like this:

SET @.SELCMD ='SELECT * FROM TABLE_2
WHERE Field1 = ''' + @.VAR_A +
''' ORDER BY 1,2,3;'
END

If @.VAR_A = ABCD then this set command sets @.SELCMD to:
SELECT * FROM TABLE_2 WHERE Field1 = 'ABCD' ORDER BY 1,2,3;

--BEGIN PGP SIGNATURE--
Version: GnuPG v1.0.6 (MingW32)
Comment: For info see http://www.gnupg.org

iEYEARECAAYFAj+9Pw0ACgkQFt8ABY6ZYSJ0bQCdE0eEaDLP8l 0QhwQVjnMe6DME
BY8Anjp68HxMiMxCqs9zLHWEHgTkQtGw
=agcy
--END PGP SIGNATURE--

Wednesday, March 21, 2012

Query for Distinct Parameter with newest Date

I am having trouble setting up a query for my inspection test results for a given work piece.

Example Table [Inspection Data]

JOBSERIALPARAMMINMAXVALUEPFDATETIME

11Test101011F6/3/2007

11Test10105P6/4/2007

11Test2286P6/3/2007

11Test2281F6/4/2007

12Test10104P6/3/2007

12Test2285P6/4/2007

11Test3687P6/3/2007

Query table [Inspection Data] for:

JOB = 1

SERIAL = 1

MAX( DATETIME ) for each test

Expected Results:

JOBSERIALPARAMMINMAXVALUEPFDATETIME

11Test10105P6/4/2007

11Test2281F2/4/2007

11Test3687P6/3/2007

Thanks,

Sam

Here you go...

Code Snippet

Create Table #inspectiondata (

[JOB] int ,

[SERIAL] int ,

[PARAM] Varchar(100) ,

[MIN] int ,

[MAX] int ,

[VALUE] int ,

[PF] Varchar(100) ,

[DATETIME] Datetime

);

Insert Into #inspectiondata Values('1','1','Test1','0','10','11','F','6/3/2007');

Insert Into #inspectiondata Values('1','1','Test1','0','10','5','P','6/4/2007');

Insert Into #inspectiondata Values('1','1','Test2','2','8','6','P','6/3/2007');

Insert Into #inspectiondata Values('1','1','Test2','2','8','1','F','6/4/2007');

Insert Into #inspectiondata Values('1','2','Test1','0','10','4','P','6/3/2007');

Insert Into #inspectiondata Values('1','2','Test2','2','8','5','P','6/4/2007');

Insert Into #inspectiondata Values('1','1','Test3','6','8','7','P','6/3/2007');

--For SQL Server 2005

;With CTE

(

Select *, Row_Number() OVER(Partition By PARAM Order By [DATETIME] Desc) RowId From #inspectiondata

Where [JOB] = 1 And [SERIAL] = 1

)

Select

[JOB]

,[SERIAL]

,[PARAM]

,[MIN]

,[MAX]

,[VALUE]

,[PF]

,[DATETIME]

From

CTE

Where

RowId = 1

--For SQL Server 2000

Select

Data.[JOB]

,Data.[SERIAL]

,Data.[PARAM]

,Data.[MIN]

,Data.[MAX]

,Data.[VALUE]

,Data.[PF]

,Data.[DATETIME]

From

#inspectiondata Data

Join (

Select

Max([DateTime]) [DateTime]

,PARAM

From

#inspectiondata

Where

[JOB] = 1 And [SERIAL] = 1

Group By

PARAM

) as MaxData On MaxData.[DateTime] = Data.[DateTime] And MaxData.PARAM = Data.PARAM

Where

[JOB] = 1

And [SERIAL] = 1

|||

I was not familiar with CTEs until I read your posting and they seem very easy to read but I cannot get it to compile. The error I get if I name the CTE 'CTE' is 'CTE is not a recognized option'.

Are CTEs available with the Express version of SQL Server?

|||first google result would support this

http://msdn2.microsoft.com/en-us/library/bb264566(SQL.90).aspx

Give the error of why it won't compile.|||

The reason why it was not working is because I had an alter procedure call at the beginning of the stored procedure.

Everything is working perfectly now.

Thank you for your help.

|||

Also, The Alter Procedure call had to be moved to the first line in the sql code.

Thanks again,

Sam

Tuesday, March 20, 2012

Query error during runtime

I get Invalid object name 'bstr'. when I try to run this query

Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From
(SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN (
'{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB2F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB31-DB97-11D6-A551-00B0D021E10A}',
'{1449DB33-DB97-11D6-A551-00B0D021E10A}',
'{1449DB35-DB97-11D6-A551-00B0D021E10A}',
'{1449DB37-DB97-11D6-A551-00B0D021E10A}',
'{1449DB39-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB3F-DB97-11D6-A551-00B0D021E10A}',
'{1449DB43-DB97-11D6-A551-00B0D021E10A}',
'{1449DB45-DB97-11D6-A551-00B0D021E10A}',
'{1449DB47-DB97-11D6-A551-00B0D021E10A}',
'{1449DB49-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4B-DB97-11D6-A551-00B0D021E10A}',
'{1449DB4D-DB97-11D6-A551-00B0D021E10A}',
'{1449DB51-DB97-11D6-A551-00B0D021E10A}',
'{DAA598D9-E7B5-4155-ABB7-0C2C24466740}',
'{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}',
'{128F17D4-2014-480A-96C6-370599F32F67}',
'{9F3A64C9-28F3-440B-B694-3E341471ED8E}',
'{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}',
'{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}',
'{2BB836D3-2DC1-4899-9406-6A495ED395C3}',
'{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}',
'{E18E470B-B297-43D2-B9CD-71AF65654970}',
'{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}',
'{0E91AC62-7929-4B42-B771-7A6399A9E3B0}',
'{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}',
'{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}',
'{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}',
'{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}',
'{19082B90-EF02-45CC-B037-AFD0CF91D69E}',
'{6F76CEF7-EBC0-48C6-8B78-C5330324C019}',
'{18492042-B22A-4370-BFA3-D0481800BBC7}',
'{A71343AD-CC09-4033-A224-D2D8C300904A}',
'{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}',
'{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}',
'{A923226F-B920-4CFA-9B0D-F422D1C36902}',
'{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}',
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,

(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
) As bstr

LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

thanks
Sunitsjoshi (sjoshi@.ingr.com) writes:
> I get Invalid object name 'bstr'. when I try to run this query
> Select distinct c0.oid, c1.Value, c2.Value, c3.Value
> From
> (SELECT oid FROM dbo.COREAttribute
> WHERE CLSID IN (
> '{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
>...
> '{C31DB61A-5221-42CF-9A73-FE76D5158647}'
> )) AS c0 ,
> (select oid, dispid, value
> FROM dbo.COREBSTRAttribute
> WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}'
> ) As bstr
> LEFT JOIN bstr AS c1
> ON (c0.oid = c1.oid)
> AND c1.dispid = 28
> LEFT JOIN bstr AS c2
> ON (c0.oid = c2.oid)
> AND c2.dispid = 112
> LEFT JOIN bstr AS c3
> ON (c0.oid = c3.oid)
> AND c3.dispid = 192

You cannot refer a virtual table in this way in a query. What you are
trying is a Common Table Expression, which is a new feature in SQL 2005
(culled from ANSI SQL). There you would write:

WITH bstr AS
(select oid, dispid, value
FROM dbo.COREBSTRAttribute
WHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}')
Select distinct c0.oid, c1.Value, c2.Value, c3.Value
From (SELECT oid FROM dbo.COREAttribute
WHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}',
...
'{C31DB61A-5221-42CF-9A73-FE76D5158647}'
)) AS c0 ,
LEFT JOIN bstr AS c1
ON (c0.oid = c1.oid)
AND c1.dispid = 28
LEFT JOIN bstr AS c2
ON (c0.oid = c2.oid)
AND c2.dispid = 112
LEFT JOIN bstr AS c3
ON (c0.oid = c3.oid)
AND c3.dispid = 192

In SQL 2000, you will have to paste in the query in all the three
LEFT JOIN. Or put the stuff into a temp table or table variable first,
so that the query is evaluated only once. In fact this is necessary
in SQL 2005, as the nice syntax only acts as a macro definition.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Wednesday, March 28, 2012

Query Help

Query Help

Query help

Wednesday, March 21, 2012

Query for Distinct Parameter with newest Date

Tuesday, March 20, 2012

Query error during runtime

query assistance

Blog Archive

About Me