You may want to use:

    SELECT Name, &#39;Unpaid&#39; AS Status FROM table;

---

The `SELECT` clause syntax, as defined in [MSDN: SELECT Clause (Transact-SQL)][1], is as follows:

    SELECT [ ALL | DISTINCT ]
    [ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ] 
    &lt;select_list&gt; 

Where the `expression` can be a **constant**, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

  [1]: http://msdn.microsoft.com/en-us/library/ms176104.aspx

The following fails with `Errno::ENOENT: No such file or directory`, even if the file exists:

    open(&#39;~/some_file&#39;)

However, I can do this:

    open(File.expand_path(&#39;~/some_file&#39;))

I have two questions:

 1. Why doesn&#39;t `open` process the tilde as pointing to the home directory?
 2. Is there a slicker way than `File.expand_path`?

How to open files relative to home directory

check this example:

    &gt; a = matrix(1:9, nrow = 3, ncol = 3, dimnames = list(LETTERS[1:3], LETTERS[1:3]))
    &gt; a
      A B C
    A 1 4 7
    B 2 5 8
    C 3 6 9

the table displays correctly. There are two different ways of writing it to file... 

    
`write.csv(a, &#39;a.csv&#39;)` which gives as expected:

    &quot;&quot;,&quot;A&quot;,&quot;B&quot;,&quot;C&quot;
    &quot;A&quot;,1,4,7
    &quot;B&quot;,2,5,8
    &quot;C&quot;,3,6,9

and `write.table(a, &#39;a.txt&#39;)` which screws up

    &quot;A&quot; &quot;B&quot; &quot;C&quot;
    &quot;A&quot; 1 4 7
    &quot;B&quot; 2 5 8
    &quot;C&quot; 3 6 9

indeed, an empty tab is missing.... which is a pain in the butt for downstream things.
Is this a bug or a feature?
Is there a workaround? (other than `write.table(cbind(rownames(a), a), &#39;a.txt&#39;, row.names=FALSE`)

Cheers,
yannick


write.table writes unwanted leading empty column to header when has rownames

I&#39;m trying to execute a SELECT statement that includes a column of a static string value.  I&#39;ve done this in Access, but never with raw SQL.  Is this possible?

Example:

     Name  | Status
     ------+--------
     John  | Unpaid
     Terry | Unpaid
     Joe   | Unpaid

In the above example, the &quot;Status&quot; column doesn&#39;t exist in the database.
     

Include in SELECT a column that isn&#39;t actually in the database

I'm trying to execute a SELECT statement that includes a column of a static string value. I've done this in Access, but never with raw SQL. Is this possible?
Example:
<pre><code class="hljs language-lua"> Name | Status
 ------+--------
 John | Unpaid
 Terry | Unpaid
 Joe | Unpaid
</code></pre>
In the above example, the "Status" column doesn't exist in the database.

Compare these 2 queries. Is it faster to put the filter on the join criteria or in the `WHERE` clause. I have always felt that it is faster on the join criteria because it reduces the result set at the soonest possible moment, but I don&#39;t know for sure.

I&#39;m going to build some tests to see, but I also wanted to get opinions on which would is clearer to read as well.

**Query 1**

    SELECT      *
    FROM        TableA a
    INNER JOIN  TableXRef x
            ON  a.ID = x.TableAID
    INNER JOIN  TableB b
            ON  x.TableBID = b.ID
    WHERE       a.ID = 1            /* &lt;-- Filter here? */

**Query 2**

    SELECT      *
    FROM        TableA a
    INNER JOIN  TableXRef x
            ON  a.ID = x.TableAID
            AND a.ID = 1            /* &lt;-- Or filter here? */
    INNER JOIN  TableB b
            ON  x.TableBID = b.ID


**EDIT**

I ran some tests and the results show that it is actually very close, but the `WHERE` clause is actually slightly faster! =)

I absolutely agree that it makes more sense to apply the filter on the `WHERE` clause, I was just curious as to the performance implications.

**ELAPSED TIME WHERE CRITERIA:** 143016 ms  
**ELAPSED TIME JOIN CRITERIA:** 143256 ms

**TEST**

    SET NOCOUNT ON;

    DECLARE @num    INT,
            @iter   INT

    SELECT  @num    = 1000, -- Number of records in TableA and TableB, the cross table is populated with a CROSS JOIN from A to B
            @iter   = 1000  -- Number of select iterations to perform

    DECLARE @a TABLE (
            id INT
    )

    DECLARE @b TABLE (
            id INT
    )

    DECLARE @x TABLE (
            aid INT,
            bid INT
    )

    DECLARE @num_curr INT
    SELECT  @num_curr = 1
            
    WHILE (@num_curr &lt;= @num)
    BEGIN
        INSERT @a (id) SELECT @num_curr
        INSERT @b (id) SELECT @num_curr
        
        SELECT @num_curr = @num_curr + 1
    END

    INSERT      @x (aid, bid)
    SELECT      a.id,
                b.id
    FROM        @a a
    CROSS JOIN  @b b

    /*
        TEST
    */
    DECLARE @begin_where    DATETIME,
            @end_where      DATETIME,
            @count_where    INT,
            @begin_join     DATETIME,
            @end_join       DATETIME,
            @count_join     INT,
            @curr           INT,
            @aid            INT

    DECLARE @temp TABLE (
            curr    INT,
            aid     INT,
            bid     INT
    )

    DELETE FROM @temp

    SELECT  @curr   = 0,
            @aid    = 50

    SELECT  @begin_where = CURRENT_TIMESTAMP
    WHILE (@curr &lt; @iter)
    BEGIN
        INSERT      @temp (curr, aid, bid)
        SELECT      @curr,
                    aid,
                    bid
        FROM        @a a
        INNER JOIN  @x x
                ON  a.id = x.aid
        INNER JOIN  @b b
                ON  x.bid = b.id
        WHERE       a.id = @aid
            
        SELECT @curr = @curr + 1
    END
    SELECT  @end_where = CURRENT_TIMESTAMP
    
    SELECT  @count_where = COUNT(1) FROM @temp
    DELETE FROM @temp
    
    SELECT  @curr = 0
    SELECT  @begin_join = CURRENT_TIMESTAMP
    WHILE (@curr &lt; @iter)
    BEGIN
        INSERT      @temp (curr, aid, bid)
        SELECT      @curr,
                    aid,
                    bid
        FROM        @a a
        INNER JOIN  @x x
                ON  a.id = x.aid
                AND a.id = @aid
        INNER JOIN  @b b
                ON  x.bid = b.id
        
        SELECT @curr = @curr + 1
    END
    SELECT  @end_join = CURRENT_TIMESTAMP

    SELECT  @count_join = COUNT(1) FROM @temp
    DELETE FROM @temp

    SELECT  @count_where AS count_where,
            @count_join AS count_join,
            DATEDIFF(millisecond, @begin_where, @end_where) AS elapsed_where,
            DATEDIFF(millisecond, @begin_join, @end_join) AS elapsed_join


Which SQL query is faster? Filter on Join criteria or Where clause?

How can I access the number of rows affected by:

    cursor.execute(&quot;SELECT COUNT(*) from result where server_state=&#39;2&#39; AND name LIKE &#39;&quot;+digest+&quot;_&quot;+charset+&quot;_%&#39;&quot;)

Python: Number of rows affected by cursor.execute(&quot;SELECT ...)

I have the following two tables:

    Table1
    ----------
    ID   Name
    1    A
    2    B
    3    C

    Table2
    ----------
    ID   Name
    1    Z

I need to insert data from `Table1` to `Table2`. I can use the following syntax:

    INSERT INTO Table2(Id, Name) SELECT Id, Name FROM Table1

However, in my case, duplicate IDs might exist in `Table2` (in my case, it&#39;s just &quot;`1`&quot;) and I don&#39;t want to copy that again as that would throw an error. 

I can write something like this:

    IF NOT EXISTS(SELECT 1 FROM Table2 WHERE Id=1)
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 
    ELSE
    INSERT INTO Table2 (Id, name) SELECT Id, name FROM Table1 WHERE Table1.Id&lt;&gt;1

Is there a better way to do this without using `IF - ELSE`? I want to avoid two `INSERT INTO-SELECT` statements based on some condition.

Avoid duplicates in INSERT INTO SELECT query in SQL Server

Can I run a select statement and get the row number if the items are sorted?

I have a table like this:

    mysql&gt; describe orders;
    +-------------+---------------------+------+-----+---------+----------------+
    | Field       | Type                | Null | Key | Default | Extra          |
    +-------------+---------------------+------+-----+---------+----------------+
    | orderID     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
    | itemID      | bigint(20) unsigned | NO   |     | NULL    |                |
    +-------------+---------------------+------+-----+---------+----------------+

I can then run this query to get the number of orders by ID:

    SELECT itemID, COUNT(*) as ordercount
    FROM orders
    GROUP BY itemID ORDER BY ordercount DESC;

This gives me a count of each `itemID` in the table like this:

    +--------+------------+
    | itemID | ordercount |
    +--------+------------+
    |    388 |          3 |
    |    234 |          2 |
    |   3432 |          1 |
    |    693 |          1 |
    |   3459 |          1 |
    +--------+------------+

I want to get the row number as well, so I could tell that `itemID=388` is the first row, `234` is second, etc (essentially the ranking of the orders, not just a raw count). I know I can do this in Java when I get the result set back, but I was wondering if there was a way to handle it purely in SQL.

**Update**

Setting the rank adds it to the result set, but not properly ordered:

    mysql&gt; SET @rank=0;
    Query OK, 0 rows affected (0.00 sec)

    mysql&gt; SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
        -&gt; FROM orders
        -&gt; GROUP BY itemID ORDER BY rank DESC;
    +------+--------+------------+
    | rank | itemID | ordercount |
    +------+--------+------------+
    |    5 |   3459 |          1 |
    |    4 |    234 |          2 |
    |    3 |    693 |          1 |
    |    2 |   3432 |          1 |
    |    1 |    388 |          3 |
    +------+--------+------------+
    5 rows in set (0.00 sec)

MySQL - Get row number on select

if I set

    &lt;property name=&quot;show_sql&quot;&gt;true&lt;/property&gt;

in my **hibernate.cfg.xml** configuration file in the console I can see the SQL.

But it&#39;s not *real* SQL... Can I see the SQL code that will be passed directly to database?

Example:

I see 

    select this_.code from true.employee this_ where this_.code=?

Can I see

    select employee.code from employee where employee.code=12

the *real* SQL?




Hibernate show real SQL

In our production database, we ran the following pseudo-code SQL batch query running every hour:

    INSERT INTO TemporaryTable
        (SELECT FROM HighlyContentiousTableInInnoDb
         WHERE allKindsOfComplexConditions are true)

Now this query itself does not need to be fast, but I noticed it was locking up `HighlyContentiousTableInInnoDb`, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that&#39;s how long that other query takes).

Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

But I don&#39;t really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the `HighlyContentiousTable` to another table and do the query there.

How to improve INSERT INTO ... SELECT locking behavior

I often find these three variants:

    SELECT COUNT(*) FROM Foo;
    SELECT COUNT(1) FROM Foo;
    SELECT COUNT(PrimaryKey) FROM Foo;

As far as I can see, they all do the same thing, and I find myself using the three in my codebase. However, I don&#39;t like to do the same thing different ways. To which one should I stick? Is any one of them better than the two others?

COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

I have tried this which did not work.

    select top 5 * from [Table_Name]

How to get Top 5 records in SqLite?

I would like to do the equivalent of this SQL but with Solr as my data store.

    SELECT
       DISTINCT txt
    FROM
       my_table;

What syntax would force Solr to only give me distinct values?

    http://localhost:8983/solr/select?q=txt:?????&amp;fl=txt

**EDIT:** So faceted searching seems to fit, but as I investigated it, I realized I had only detailed half of the problem.

My SQL query should have read...

    SELECT
       DISTINCT SUBSTR(txt,0,3)
    FROM
       my_table;

Any possibility of this with Solr?

How to select distinct field values using Solr?

I am using R and need to select rows with aged (age of death) less than or equal to laclen (lactation length). I am trying to create a new data frame to only include rows/ids whereby the value of column&#39;aged&#39; is less than its corresponding &#39;laclength&#39; value.
 
    df:
     id1   id2    laclen    aged
    9830  64526    26       6 
    7609  64547    28       0 
    9925  64551     3       0 
    9922  64551     3       5 
    9916  64551     3       8 
    9917  64551     3       8 
    9914  64551     3       2 

the new data frame should look like this:

    dfnew:
    id1   id2    laclen    aged
    9830  64526    26       6 
    7609  64547    28       0 
    9925  64551     3       0 
    9914  64551     3       2


Any help would be appreciated!

Bazon

Content Type	Original Author	Original Content on Stackoverflow
Question	Cypher	View Question on Stackoverflow
Solution 1 - Sql	Daniel Vassallo	View Answer on Stackoverflow

Include in SELECT a column that isn't actually in the database

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

write.table writes unwanted leading empty column to header when has rownames

How to open files relative to home directory

Attributions