Know your UNION(s), NULL(s), COUNT(s) ?

  • 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

  • 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)

  • 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

  • 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.

  • 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

  • re PL/SQL: I confess that I have been visiting the 'dark side'.

  • 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

  • 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]

    SQL-4-Life
  • 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.

  • 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