    SELECT * FROM table
    ORDER BY NEWID()



This is the simplest solution:

    SELECT quote FROM quotes ORDER BY RAND() 

Although it is not the most efficient. [This one][1] is a better solution.


  [1]: http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/

The usual method is to use the NEWID() function, which generates a unique GUID. So,

    SELECT * FROM dbo.Foo ORDER BY NEWID();

To be efficient, and random, it might be best to have two different queries.

Something like...

SELECT table_id FROM table

Then, in your chosen language, pick a random id, then pull that row&#39;s data.

SELECT * FROM table WHERE table_id = $rand_id

But that&#39;s not really a good idea if you&#39;re expecting to have lots of rows in the table.  It would be better if you put some kind of limit on what you randomly select from.  For publications, maybe randomly pick from only items posted within the last year.

Here&#39;s an example ([source](http://www.rankspike.com/seo-helpful-tips/how-to-randomize-in-sql/)):

    SET @randomId = Cast(((@maxValue + 1) - @minValue) * Rand() + @minValue AS tinyint);

 


***SQL Server / MS Access Syntax:***

    SELECT TOP 1 * FROM table_name ORDER BY RAND()

***MySQL Syntax:*** 

    SELECT * FROM table_name ORDER BY RAND() LIMIT 1





Myself and my group are horrendous at incrementing assembly version numbers and we frequently ship assemblies with 1.0.0.0 versions.  Obviously, this causes a lot of headaches.

We&#39;re getting a lot better with our practices via our [CI][1] platform and I&#39;d really like to set it up to auto increment the values within the `assemblyinfo.cs` file so that the versions of our assemblies are auto updated with the code changes in that assembly.

I had previously setup (before we found [Hudson][2]) a way to increment the value through either `msbuild` or the command line (can&#39;t remember), but with Hudson, that will update the SVN repository and trigger ANOTHER build. That would result in a slow infinite loop as Hudson polls SVN every hour.

Is having Hudson increment the version number a bad idea? What would be an alternative way to do it?

Ideally, my criteria for a solution would be one that:

 - Increments the build number in `assemblyinfo.cs` before a build
 - Only increments the build number in assemblies that have changed.  This may not be possible as Hudson wipes out the project folder every time it does a build
 - Commits the changed assemblyinfo.cs into the code repository (currently [VisualSVN][3])
 - Does not cause Hudson to trigger a new build the next time it scans for changes

Working this out in my head, I could easily come up with a solution to most of this through batch files / commands, but all of my ideas would cause Hudson to trigger a new build the next time it scans.  I&#39;m not looking for someone to do everything for me, just point me in the right direction, maybe a technique to get Hudson to ignore certain SVN commits, etc.

Everything I&#39;ve found so far is just an article explaining how to get the version number automatically incremented, nothing takes into account a CI platform that could be spun into an infinite loop.

  [1]: http://en.wikipedia.org/wiki/Continuous_integration
  [2]: http://en.wikipedia.org/wiki/Hudson_%28software%29
  [3]: http://en.wikipedia.org/wiki/VisualSVN

 
 

How can I auto increment the C# assembly version via our CI platform (Hudson)?

When I&#39;m writing my DAL or other code that returns a set of items, should I always make my return statement:

    public IEnumerable&lt;FooBar&gt; GetRecentItems()

or 

    public IList&lt;FooBar&gt; GetRecentItems()


Currently, in my code I have been trying to use IEnumerable as much as possible but I&#39;m not sure if this is best practice?  It seemed right because I was returning the most generic datatype while still being descriptive of what it does, but perhaps this isn&#39;t correct to do.



Should I always return IEnumerable&lt;T&gt; instead of IList&lt;T&gt;?

Is it possible to write SQL query that returns table rows in random order every time the query run?


Return rows in random order

<p>Is it possible to write SQL query that returns table rows in random order every time the query run?</p>


I need to add the current year as a variable in an SQL statement, how can I retrieve the current year using SQL?

i.e.
&lt;pre&gt;
  BETWEEN 
    TO_DATE(&#39;01/01/&lt;i&gt;**currentYear**&lt;/i&gt; 00:00:00&#39;, &#39;DD/MM/YYYY HH24:MI:SS&#39;)
    AND
    TO_DATE(&#39;31/12/&lt;i&gt;**currentYear**&lt;/i&gt; 23:59:59&#39;, &#39;DD/MM/YYYY HH24:MI:SS&#39;)
&lt;/pre&gt;

How do I get the current year using SQL on Oracle?

I have the following SQL, which I am trying to translate to LINQ:

    SELECT f.value
    FROM period as p 
    LEFT OUTER JOIN facts AS f ON p.id = f.periodid AND f.otherid = 17
    WHERE p.companyid = 100

I have seen the typical implementation of the left outer join (ie. `into x from y in x.DefaultIfEmpty()` etc.) but am unsure how to introduce the other join condition (`AND f.otherid = 17`)

**EDIT**

Why is the `AND f.otherid = 17` condition part of the JOIN instead of in the WHERE clause?
Because `f` may not exist for some rows and I still want these rows to be included. If the condition is applied in the WHERE clause, after the JOIN - then I don&#39;t get the behaviour I want.

Unfortunately this:

    from p in context.Periods
    join f in context.Facts on p.id equals f.periodid into fg
    from fgi in fg.DefaultIfEmpty()
    where p.companyid == 100 &amp;&amp; fgi.otherid == 17
    select f.value

seems to be equivalent to this:

    SELECT f.value
    FROM period as p 
    LEFT OUTER JOIN facts AS f ON p.id = f.periodid 
    WHERE p.companyid = 100 AND f.otherid = 17

which is not quite what I&#39;m after.



LINQ to SQL - Left Outer Join with multiple join conditions

I know the syntax:

    ALTER TABLE [TheTable] DROP CONSTRAINT [TheDefaultConstraint]

but how to I drop the default constraint when I don&#39;t know its name? (That is, it was autogenerated at `CREATE TABLE` time.)




How do you drop a default value or similar constraint in T-SQL?

I need to take the first N rows for each group, ordered by custom column.

Given the following table:

    db=# SELECT * FROM xxx;
     id | section_id | name
    ----+------------+------
      1 |          1 | A
      2 |          1 | B
      3 |          1 | C
      4 |          1 | D
      5 |          2 | E
      6 |          2 | F
      7 |          3 | G
      8 |          2 | H
    (8 rows)

I need the first 2 rows (ordered by &lt;b&gt;name&lt;/b&gt;) for each &lt;b&gt;section_id&lt;/b&gt;, i.e. a result similar to:

     id | section_id | name
    ----+------------+------
      1 |          1 | A
      2 |          1 | B
      5 |          2 | E
      6 |          2 | F
      7 |          3 | G
    (5 rows)

I am using PostgreSQL 8.3.5.

Grouped LIMIT in PostgreSQL: show the first N rows for each group?

My current query looks like this:

    SELECT * FROM fiberbox f WHERE f.fiberBox LIKE &#39;%1740 %&#39; OR f.fiberBox LIKE &#39;%1938 %&#39; OR f.fiberBox LIKE &#39;%1940 %&#39;

I did some looking around and can&#39;t find anything similar to a LIKE IN() - I envision it working like this:

    SELECT * FROM fiberbox f WHERE f.fiberbox LIKE IN(&#39;%140 %&#39;, &#39;%1938 %&#39;, &#39;%1940 %&#39;)

Any ideas? Am I just thinking of the problem the wrong way - some obscure command I&#39;ve never seen.

MySQL 5.0.77-community-log

MySQL LIKE IN()?

I have a SQL Server 2000 with a table containing an `image` column. 

How do I insert the binary data of a file into that column by specifying the path of the file?

    CREATE TABLE Files
    (
      FileId int,
      FileData image
    )

How can I insert binary file data into a binary SQL field using a simple insert statement?

My best google result was [this][1]:

 * below 11 are warnings, not errors
 * 11-16 are available for use
 * above 16 are system errors
 * there is no behavioral difference among 11-16

But, from BOL, &quot;Severity levels from 0 through 18 can be specified by any user.&quot;

In my particular stored procedure, I want the error returned to a .Net client application, so it looks like any severity level between 11-18 would do the trick.  Does anyone have any authoritative information about what each of the levels mean, and how they should be used?


  [1]: http://www.xaprb.com/blog/2005/11/23/be-consistent-with-raiserror-severitites/


What do the different RAISERROR severity levels mean?

*I thought this would be trivial, but it isn&#39;t... I&#39;m sure there is a simple way to do it but I can&#39;t manage to find it. Shame on me.*

I want to import/export the database itself, the tables, the constraints (foreign keys and so on). I&#39;d rather not get the data with it, but I can get rid of it after if there&#39;s no other way.

So... how do you export a database using MS SQL Server Management Studio ? How do you import it?

The only solution I found was right click on the tables and &quot;script to Create&quot;, but I have something like 100 tables, so I&#39;d rather avoid this.

Thanks!


Import / Export database with SQL Server Server Management Studio

I am just confused with the execution sequence of a SQL query when we use `GROUP BY` and `HAVING` with a `WHERE` clause. Which one gets executed first?  What is the sequence?



Execution sequence of Group By, Having and Where clause in SQL Server?

I have recently been running into many different areas of SQL Server that I normally don&#39;t mess with. One of them that has me confused is the area of Logins and Users. Seems like it should be a pretty simple topic...

It appears that each login can only have 1 user and each user can only have 1 login. 

A login can be associated to multiple tables thus associating that user to many tables. 

So my question is why even have a login and a user? they seem to be pretty much one in the same. What are the differences, or what is it that I seem to be missing?

Difference between a User and a Login in SQL Server

What is the main purpose of using [CROSS APPLY][1]?

I have read (vaguely, through posts on the Internet) that `cross apply` can be more efficient when selecting over large data sets if you are partitioning. (Paging comes to mind)

I also know that `CROSS APPLY` doesn&#39;t require a UDF as the right-table.

In most `INNER JOIN` queries (one-to-many relationships), I could rewrite them to use `CROSS APPLY`, but they always give me equivalent execution plans. 

Can anyone give me a good example of when `CROSS APPLY` makes a difference in those cases where `INNER JOIN` will work as well?


---

**Edit:**

Here&#39;s a trivial example, where the execution plans are exactly the same.  (Show me one where they differ and where `cross apply` is faster/more efficient)

    create table Company (
    	companyId int identity(1,1)
    ,	companyName varchar(100)
    ,	zipcode varchar(10) 
    ,	constraint PK_Company primary key (companyId)
    )
    GO
    
    create table Person (
    	personId int identity(1,1)
    ,	personName varchar(100)
    ,	companyId int
    ,	constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
    ,	constraint PK_Person primary key (personId)
    )
    GO
    
    insert Company
    select &#39;ABC Company&#39;, &#39;19808&#39; union
    select &#39;XYZ Company&#39;, &#39;08534&#39; union
    select &#39;123 Company&#39;, &#39;10016&#39;
    
    
    insert Person
    select &#39;Alan&#39;, 1 union
    select &#39;Bobby&#39;, 1 union
    select &#39;Chris&#39;, 1 union
    select &#39;Xavier&#39;, 2 union
    select &#39;Yoshi&#39;, 2 union
    select &#39;Zambrano&#39;, 2 union
    select &#39;Player 1&#39;, 3 union
    select &#39;Player 2&#39;, 3 union
    select &#39;Player 3&#39;, 3 
    
    
    /* using CROSS APPLY */
    select *
    from Person p
    cross apply (
    	select *
    	from Company c
    	where p.companyid = c.companyId
    ) Czip
    
    /* the equivalent query using INNER JOIN */
    select *
    from Person p
    inner join Company c on p.companyid = c.companyId


  [1]: http://technet.microsoft.com/en-us/library/ms175156.aspx

When should I use CROSS APPLY over INNER JOIN?

How can I select data in the same query from two different databases that are on two different servers in SQL Server?

Selecting data from two different servers in SQL Server

I want to move a table into a specific Schema using T-SQL? I am using SQL Server 2008.

Content Type	Original Author	Original Content on Stackoverflow
Question	Arsen Mkrtchyan	View Question on Stackoverflow
Solution 1 - Sql	Dave Barker	View Answer on Stackoverflow
Solution 2 - Sql	Alec Smart	View Answer on Stackoverflow
Solution 3 - Sql	devstuff	View Answer on Stackoverflow
Solution 4 - Sql	nilamo	View Answer on Stackoverflow
Solution 5 - Sql	PinoyDev	View Answer on Stackoverflow
Solution 6 - Sql	Dan Both	View Answer on Stackoverflow

Return rows in random order

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

Solution 5 - Sql

Solution 6 - Sql

Should I always return IEnumerable<T> instead of IList<T>?

How can I auto increment the C# assembly version via our CI platform (Hudson)?

Attributions