FILTER
-
Comments posted to this topic are about the item FILTER
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
my query unexpectedly took 15 minutes to run and I cancelled it after that ..
then I came to ssc and an easy one to start the tuesday ...
felt relieved ...time to stress on another pawn ..
thanks for the question..
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Easy one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Thanks for the question Lokesh-ji, (so simple and yet so much to consider).
(actually i did not focus on the collation but saw the count(*) and then I chose 6 :hehe: , not a right way but got by 1 point 😉 )
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Raghavendra Mudugal (9/17/2012)
Thanks for the question Lokesh-ji, (so simple and yet so much to consider). )Glad to know that Raghu..sometimes we are so engrossed in the perplexities of complex issues that we forget the basics 😉 It was just a small effort to make you all revise:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Sorry for taking this thread out of the scope... (this is nothing to do with the QToD's objective, but was playing around and kind of stuck with this...:Whistling: )
Considering the same data given, can anyone advise what is happening on #3 ?
It is giving me this result...
COL2(No column name)
AB2
xy1
But I was expecting
COL2(No column name)
AB2
aB 1
xy1
-- #1
SELECT COL2, COUNT(COL2)
FROM test
WHERE col2 IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2
-- #2
SELECT COL2, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)
FROM test
WHERE col2 IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2
-- #3
SELECT COL2, COUNT(COL2)
FROM test
WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2
-- #4
SELECT COL2, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)
FROM test
WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Raghu,
The results you are getting are due to you only specifying the collation on the where clause.
The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.
However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.
You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)
SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)
FROM testQOTD
WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS
-
I think it should work ...
SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2 COLLATE SQL_Latin1_General_CP1_CS_AS)FROM #test
WHERE col2 IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
philip.cullingworth (9/18/2012)
Raghu,The results you are getting are due to you only specifying the collation on the where clause.
The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.
However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.
You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)
SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)
FROM testQOTD
WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS
that is what I meant , and yes there is no need to add collation in the count ....
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
Nice question to start the day. Thanks. 😀
-
Good question; the explanation could have been better.
"To make this query case sensitive we need to either set the collation level to Case Sensitive or use 'COLLATE SQL_Latin1_General_CP1_CS_AS' in the select query with col2"
To me, the above sentence reads as if there are two methods. The second one is to force a different collation in the query (as demonstrated in the profivded code fragment); the first one is to, somehow, "set the collation level". And that, again: to me, sounds as if some SET statement would be used.
This may all be due to my English reading skills (I'm not a native English speaker), but in any case, I wanted to add a comment to clarify this in case ohers have the same misunderstanding,
First, there is no SET statement to control the default collation used in comparisons. The collation used in a comparison will always be the collation of the column used, unless an explicit COLLATE clause is used to override that.
The only way to make this query use a case sensitive collation without specifying it in the table is to change the column's collation. This can be done by adding a COLLATE clause to the CREATE TABLE (or ALTER TABLE) statement. Or it can be done by changing the default collation of the database (using the COLLATE clause of the CREATE DATABASE or ALTER DATABASE statement) and not specifying a collation when creating the table. Finally, the last way to get case sensitive comparisons by default is to specify a case sensitive collation when installing the SQL Server instance, then using the defaults when creating database and table. Changing the default collation for an already installed instance is very hard; it requires rebuilding the master.
The link in the explanation does not work, unfortunately. I think the author intended to post two links, but accidentally mashed them together. Here are the links I think he tried to supply:
* On MSDN, the official documentation on COLLATE: http://msdn.microsoft.com/en-us/library/ms184391.aspx
* A recent article on SQLServerCentral about case sensitive searches: http://www.sqlservercentral.com/blogs/nycnet/2012/01/19/case-sensitive-searches-in-t-sql/[/url]
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Awesome Philip, thank you 🙂 for the details.
philip.cullingworth (9/18/2012)
Raghu,The results you are getting are due to you only specifying the collation on the where clause.
The query is selecting rows 1, 3 and 6 since they are the rows that match when the case sensitive collation is specified.
However the Group By clause does not specifiy Case Sensitive and so the results are grouped case insensitive, combining the AB and aB.
You would need the code below to get your expected result (assuming the count next to AB was meant to be 1)
SELECT COL2 COLLATE SQL_Latin1_General_CP1_CS_AS, COUNT(COL2)
FROM testQOTD
WHERE col2 COLLATE SQL_Latin1_General_CP1_CS_AS IN ( 'AB', 'aB', 'xy' )
GROUP BY COL2 COLLATE SQL_Latin1_General_CP1_CS_AS
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
This was removed by the editor as SPAM
-
Nice question! Thanks for reminding the basics.
Sujeet Singh -
Thanks, easy one for a slow Tuesday!:-D
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply