June 4, 2008 at 3:23 pm
This is in regards to the previous post:
Why we can select count from other positions?
SELECT COUNT (25) FROM #TEST
We can not order by 25:
SELECT * FROM #TEST ORDER BY 25 DESC
this returns a message:
Msg 108, Level 16, State 1, Line 1
The ORDER BY position number 25 is out of range of the number of items in the select list.
But we do can do COUNT(25)
Regards,Yelena Varsha
June 4, 2008 at 3:34 pm
Aha,
I am wrong in assuming COUNT (1) refers to the first column.
SELECT COUNT (25)
--or
SELECT COUNT ('something')
these both return 1, as the count of the literal expression, be it integer or character
Similar to
SELECT 1,2,3,'something' FROM #TEMP
Notice that 1,2,3, and the string above are literals.
Can anyone thing of another instance where you would refer to the ordinal positions of columns as 1,2, or 3... other than and ORDER BY?
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
June 4, 2008 at 4:07 pm
Sure! A lot. In VB or other front end in relation to Recordset or Dataset references or controls like GRID control.
How it relates to SQL? We can use this code in CLR Stored Procedures for example.
This is an example for VBA:
http://msdn.microsoft.com/en-us/library/aa192404.aspx
If Activedocument.Tables.Count >= 1 Then
Set myTable = Activedocument.Tables(1)
myTable.Columns.Add BeforeColumn:=myTable.Columns(1)
myTable.Columns.DistributeWidth
End If
OR
http://msdn.microsoft.com/en-us/library/wc06dx4f.aspx
// Set the column header names.
dataGridView.Columns[0].Name = "Recipe";
dataGridView.Columns[1].Name = "Category";
dataGridView.Columns[2].Name = thirdColumnHeader;
dataGridView.Columns[3].Name = "Rating";
Regards,Yelena Varsha
June 4, 2008 at 5:35 pm
So, in which situation would you use count(1) instead of a count(*)? Or is it even a good practice to use count(1)??
To me it seems that they are essentially doing the same job when used with a FROM clause but the meaning of count(*) is very clear.
June 10, 2008 at 11:41 am
I suppose that one would use count(1) to increase the mystery in one's uncommented code.:hehe:
Here's a justification for count(1):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1242198&SiteID=1
June 10, 2008 at 11:42 am
re PL/SQL: I confess that I have been visiting the 'dark side'.
September 16, 2008 at 6:22 am
I went to check my guess and ran:
WITH DATA (Numbers) AS
(SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION
SELECT 3)
SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA
WHERE Numbers IS NULL
and got:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'.
Ooops
September 16, 2008 at 6:28 am
if your with is not the first line of code, then you need a ; before it
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
September 16, 2008 at 6:47 am
Christopher Stobbs (9/16/2008)
if your with is not the first line of code, then you need a ; before it
Ok, it worked. Thanks.
December 5, 2012 at 11:02 pm
gr8 question....
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply