`NULL` values *are* sorted last in default *ascending* order. You don&#39;t have to do anything extra.

The issue applies to ***descending*** order, which is the perfect inverse and thus sorts `NULL` values on top.  
**PostgreSQL 8.3** introduced `NULLS LAST`:

    ORDER BY somevalue DESC NULLS LAST

For **PostgreSQL 8.2** and older or other **RDBMS without this standard SQL** feature:

    ORDER BY (somevalue IS NULL), somevalue DESC

`FALSE` sorts before `TRUE`, so `NULL` values come last, just like in the example above.

See:

- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- [The manual on `SELECT`][1]


  [1]: https://www.postgresql.org/docs/current/sql-select.html

Does this make the trick?

    ORDER BY somevalue DESC NULLS LAST

Taken from: http://www.postgresql.org/docs/9.0/static/sql-select.html

I want to declare a variable in SQLite and use it in `insert` operation.

Like in MS SQL:

    declare @name as varchar(10)
    set name = &#39;name&#39;
    select * from table where name = @name

For example, I will need to get `last_insert_row` and use it in `insert`.

I have found something about binding but I didn&#39;t really fully understood it.

Declare variable in SQLite and use it

I just wrote a tiny method to count the number of pages for cell phone SMS. I didn&#39;t have the option to round up using `Math.ceil`, and honestly it seems to be very ugly.

Here is my code:

    public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
       String message = &quot;today we stumbled upon a huge performance leak while optimizing a raycasting algorithm. Much to our surprise, the Math.floor() method took almost half of the calculation time: 3 floor operations took the same amount of time as one trilinear interpolation. Since we could not belive that the floor-method could produce such a enourmous overhead, we wrote a small test program that reproduce&quot;;
       
       System.out.printf(&quot;COunt is %d &quot;,(int)messagePageCount(message));
       
       
       
    }

    public static double messagePageCount(String message){
        if(message.trim().isEmpty() || message.trim().length() == 0){
            return 0;
        } else{
            if(message.length() &lt;= 160){
                return 1;
            } else {
                return Math.ceil((double)message.length()/153);
            }
        }
    }

I don&#39;t really like this piece of code and I&#39;m looking for a more elegant way of doing this. With this, I&#39;m expecting 3 and not 3.0000000. Any ideas?

How to round up integer division and have int result in Java?

Is there a way with PostgreSQL to sort rows with `NULL` values in fields to the end of the selected table?

Like:

    SELECT * FROM table ORDER BY somevalue, PUT_NULL_TO_END

Sort NULL values to the end of a table

Is there a way with PostgreSQL to sort rows with <code>NULL</code> values in fields to the end of the selected table?
Like:
<pre><code class="hljs language-sql">SELECT * FROM table ORDER BY somevalue, PUT_NULL_TO_END
</code></pre>

I keep all my functions in a text file with `&#39;CREATE OR REPLACE FUNCTION somefunction&#39;`.  
So if I add or change some function I just feed the file to psql.

Now if I add or remove parameters to an existing function, it creates an overload with the same name and to delete the original I need type in all the parameter types in the exact order which is kind of tedious.

Is there some kind of wildcard I can use to `DROP` all functions with a given name so I can just add `DROP FUNCTION` lines to the top of my file?

DROP FUNCTION without knowing the number/type of parameters?

I have a field of time Timestamp in my MySQL database which is mapped to a `DATE` datatype in my bean. Now I want a query by which I can fetch all records in the database for which the difference between the current timestamp and the one stored in the database is &gt; 20 minutes.

How can I do it?

**What i want is:**

    SELECT * FROM MyTab T WHERE T.runTime - now &gt; 20 minutes

Are there any MySQL functions for this, or any way to do this in SQL?


Calculating time difference between 2 dates in minutes

If the column in Postgres&#39; table has the name `year`, how should look `INSERT` query to set the value for that column?

E.g.: `INSERT INTO table (id, name, year) VALUES ( ... );` gives an error near the _year_ word.

Escaping keyword-like column names in Postgres

I&#39;m trying to create upgrade and backout scripts in SQL.  The upgrade script adds a column like so:

    IF NOT EXISTS (SELECT * FROM sys.columns WHERE Name = N&#39;ColumnName&#39; 
        AND object_id = OBJECT_ID(N&#39;[dbo].[TableName]&#39;))

    ALTER TABLE TableName
        ADD ColumnName bit NOT NULL DEFAULT(0) 

The backout script removes the column like so:

    IF EXISTS (SELECT * FROM sys.columns WHERE Name = N&#39;ColumnName&#39; 
        AND object_id = OBJECT_ID(N&#39;[dbo].[TableName]&#39;))

    ALTER TABLE TableName
        DROP COLUMN ColumnName

However, the backout script throws this error:

    Msg 5074, Level 16, State 1, Line 5
        The object &#39;DF__TableName__ColumnName__1BF3D5BD&#39; is dependent on column &#39;ColumnName&#39;.
    Msg 4922, Level 16, State 9, Line 5
        ALTER TABLE DROP COLUMN ColumnName failed because one or more objects access this column.

I know how to drop the constraint, but the constraint&#39;s name changes everytime (the suffix changes).  I either need SQL Server to stop creating this randomly-named constraint OR I need to be able to remove the constraint in my script using wild-card characters, since the name changes.

Why does SQL Server keep creating a DF constraint?

If I have a MySQL table looking something like this:

&lt;pre&gt;
company_name	action	pagecount
-------------------------------
Company A		PRINT	3
Company A		PRINT	2
Company A		PRINT	3
Company B		EMAIL	
Company B		PRINT	2
Company B		PRINT	2
Company B		PRINT	1
Company A		PRINT	3
&lt;/pre&gt;

Is it possible to run a MySQL query to get output like this:

&lt;pre&gt;
company_name	EMAIL	PRINT 1 pages	PRINT 2 pages	PRINT 3 pages
-------------------------------------------------------------
CompanyA		0		0				1				3
CompanyB		1		1				2				0
&lt;/pre&gt;

The idea is that `pagecount` can vary so the output column amount should reflect that, one column for each `action`/`pagecount` pair and then number of hits per `company_name`. I&#39;m not sure if this is called a pivot table but someone suggested that?


How can I return pivot table output in MySQL?

I want to destroy the database but I&#39;m not sure what the command would be. Does anyone know how to do this?

Destroying a Postgres DB on Heroku

Say I have created some user-defined types in the DB, 

i.e. `CREATE TYPE abc ... `


Is it then possible to determine if the user-defined type exists or not? Perhaps, using any of the postgres information tables?

The main reason for this is since PostgreSQL does not seem to support `CREATE OR REPLACE TYPE ... `, and if a certain type gets created more than once, I want to be able to drop the existing one first, then re-load the new one.

Check if a user-defined type already exists in PostgreSQL

I am using following query:

    ALTER TABLE presales ALTER COLUMN code TYPE numeric(10,0); 

to change the datatype of a column from `character(20)` to `numeric(10,0)` but I am getting the error:

&gt; column &quot;code&quot; cannot be cast to type numeric

How to change column datatype from character to numeric in PostgreSQL 8.4

I found this behaviour odd and wanted more experienced users to share their thoughts and workarounds.
On running the code sample below in R:

    sampleList &lt;- list()
    d&lt;- data.frame(x1 = letters[1:10], x2 = 1:10, stringsAsFactors = FALSE)
    for(i in 1:nrow(d)) {
        	sampleList[[i]] &lt;- d$x1[i]
    }

    print(sampleList[[1]])
    #[1] &quot;a&quot;
    print(sampleList[[2]])
    #[1] &quot;b&quot;
    print(sampleList[[3]])
    #[1] &quot;c&quot;
    print(length(sampleList))
    #[1] 10
    
    sampleList[[2]] &lt;- NULL
    print(length(sampleList))
    #[1] 9
    print(sampleList[[2]])
    #[1] &quot;c&quot;
    print(sampleList[[3]])
    #[1] &quot;d&quot;


The list elements get shifted up. 
Maybe this is as expected, but I am trying to implement a function where I merge two elements of a list and drop one. I basically want to lose that list index or have it as NULL. 

Is there any way I can assign NULL to it and not see the above behaviour?

Thank you for your suggestions.


Assigning NULL to a list element in R?

Before I posted this question, I already looked [this][1], but I couldn&#39;t get what I was looking for.

I know that for the query I wrote there may exist only one row or none at all. So, there is not reason for me to use `getResultList()`.
 
Here is my code:

    String hql=&quot;from DrawUnusedBalance where unusedBalanceDate= :today&quot;;
    Query query=em.createQuery(hql);
    query.setParameter(&quot;today&quot;,new LocalDate());
    
    DrawUnusedBalance drawUnusedBalance= 
        (DrawUnusedBalance)query.getSingleResult();// we can have only a
                                                   // single datum per day
    //`System.out.println(drawUnusedBalance.toString());`

The problem is, if there is no row, it throws an exception, and if not it works fine. I know the problem but I am also looking for the best solution. 

What I wanted is, if there is no row in the DB I wanted to get a null object (instead of getting an exception) so I will insert a new data, if it is not null, I just want to update it.

There is one way to handle this, which I believe is not the right way to do it. It is: I will have a try-catch block and if it throws an exception I can write to insert new data in to the DB on the catch block. But I believe there will be a better way.

  [1]: https://stackoverflow.com/questions/4848776/no-entity-found-for-query-exception

javax.persistence.NoResultException: No entity found for query

I have a question regarding `NULL` in PHP:

      $a = &#39;&#39;;
      if($a == NULL) {
          echo &#39;is null&#39;;
      }

Why do I see **is null** when `$a` is an empty string? Is that a bug?

php is null when empty?

I&#39;m looking for a `std::ostream` implementation that acts like `/dev/null`.   It would just ignore anything that is streamed to it.  Does such a thing exist in the standard libraries or Boost?  Or do I have to roll my own?

Is there a null std::ostream implementation in C++ or libraries?

I have a table with this layout:

    CREATE TABLE Favorites
    (
      FavoriteId uuid NOT NULL PRIMARY KEY,
      UserId uuid NOT NULL,
      RecipeId uuid NOT NULL,
      MenuId uuid
    )

I want to create a unique constraint similar to this:

    ALTER TABLE Favorites
    ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

However, this will allow multiple rows with the same `(UserId, RecipeId)`, if `MenuId IS NULL`.  I want to allow `NULL` in `MenuId` to store a favorite that has no associated menu, but I only want at most one of these rows per user/recipe pair.

The ideas I have so far are:

 1. Use some hard-coded UUID (such as all zeros) instead of null.  
However, `MenuId` has a FK constraint on each user&#39;s menus, so I&#39;d then have to create a special &quot;null&quot; menu for every user which is a hassle.

 2. Check for existence of a null entry using a trigger instead.  
I think this is a hassle and I like avoiding triggers wherever possible. Plus, I don&#39;t trust them to guarantee my data is never in a bad state.

 3. Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don&#39;t have this constraint.

I&#39;m using Postgres 9.0.

Is there any method I&#39;m overlooking?

Create unique constraint with null columns

I want to sort the following data items in the order they are presented below (numbers 1-12):
&lt;pre&gt;
1
2
3
4
5
6
7
8
9
10
11
12&lt;/pre&gt;


However, my query - using `order by xxxxx asc` sorts by the first digit above all else:
&lt;pre&gt;
1
10
11
12
2
3
4
5
6
7
8
9&lt;/pre&gt;

Any tricks to make it sort more properly?

Further, in the interest of full disclosure, this could be a mix of letters and numbers (although right now it is not), e.g.:
&lt;pre&gt;
A1
534G
G46A
100B
100A
100JE
&lt;/pre&gt;
etc....

Thanks!

**update: people asking for query**

    select * from table order by name asc

MySQL &#39;Order By&#39; - sorting alphanumeric correctly

I&#39;ve [a table][1] like:

    +-----------+-------+------------+
    | client_id | views | percentage |
    +-----------+-------+------------+
    |         1 |     6 |         20 |
    |         1 |     4 |         55 |
    |         1 |     9 |         56 |
    |         1 |     2 |         67 |
    |         1 |     7 |         80 |
    |         1 |     5 |         66 |
    |         1 |     3 |         33 |
    |         1 |     8 |         34 |
    |         1 |     1 |         52 |







I tried `group_concat`:

    SELECT li.client_id, group_concat(li.views) AS views,  
    group_concat(li.percentage) FROM li GROUP BY client_id;

    +-----------+-------------------+-----------------------------+
    | client_id | views             | group_concat(li.percentage) |
    +-----------+-------------------+-----------------------------+
    |         1 | 6,4,9,2,7,5,3,8,1 | 20,55,56,67,80,66,33,34,52  |
    +-----------+-------------------+-----------------------------+

But I want to get the views in order, like:

    +-----------+-------------------+----------------------------+
    | client_id | views             | percentage                 |
    +-----------+-------------------+----------------------------+
    |         1 | 1,2,3,4,5,6,7,8,9 | 52,67,33,55,66,20,80,34,56 |
    +-----------+-------------------+----------------------------+


  [1]: http://googledrive.com/host/0B53jM4a9X2fqfkhfeV83Tm05VnU4cV9ZSWZlMUNTQzRZUUJQTFdQZUptOEJkdXkyVXFIYmM

GROUP_CONCAT ORDER BY

What is the most efficient method of showing my data in descending order?


    public String getRank() {

		String[] rank = new String[]{ KEY_ROWID };
		Cursor c = scoreDb.query(DATABASE_TABLE, rank, null, null, null, null, null);	//reading information from db.
		String rankResult = &quot;&quot;;

		int iRow = c.getColumnIndex(KEY_ROWID); //Cursor looking for column setting equal to these ints.
		

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) { 
			//Move to first row - where cursor starts and moves to next row as long it is not after last row.
			rankResult = rankResult + c.getString(iRow) + &quot;\n&quot;; 
			//Returning value of row that it is currently on.
		}
		return rankResult;	//returning result
	}
	
	public String getName() {

		String[] name = new String[]{ KEY_NAME };
		Cursor c = scoreDb.query(DATABASE_TABLE, name, null, null, null, null, null);	//reading information from db.
		String nameResult = &quot;&quot;;

		int iRow1 = c.getColumnIndex(KEY_NAME); //Cursor looking for column setting equal to these ints.
		

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) { 
			//Move to first row - where cursor starts and moves to next row as long it is not after last row.
			nameResult = nameResult + c.getString(iRow1) + &quot;\n&quot;; 
			//Returning value of row that it is currently on.
		}
		return nameResult;	//returning result
	}
	
	public String getScore() {

		String[] score = new String[]{ KEY_SCORE };
		Cursor c = scoreDb.query(DATABASE_TABLE, score, null, null, null,null, null);	//reading information from db.
		String scoreResult = &quot;&quot;;
		
		int iRow2 = c.getColumnIndex(KEY_SCORE); //Cursor looking for column setting equal to these ints.
		

		for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) { 
			//Move to first row - where cursor starts and moves to next row as long it is not after last row.
			scoreResult = scoreResult + c.getString(iRow2) + &quot;\n&quot;; 
			//Returning value of row that it is currently on.
		}
		return scoreResult;	//returning result
	}

How do I order my SQLITE database in descending order, for an android app?

Lets say I have mongo documents like this:

**Question 1**

    {
        answers:[
           {content: &#39;answer1&#39;},
           {content: &#39;2nd answer&#39;}
        ]
    }


**Question 2**

    {
        answers:[
           {content: &#39;answer1&#39;},
           {content: &#39;2nd answer&#39;}
           {content: &#39;The third answer&#39;}
        ]
    }

Is there a way to order the collection by size of answers? 

After a little research I saw suggestions of adding another field, that would contain number of answers and use it as a reference but may be there is native way to do it?

Mongo order by length of array

Content Type	Original Author	Original Content on Stackoverflow
Question	helle	View Question on Stackoverflow
Solution 1 - Sql	Erwin Brandstetter	View Answer on Stackoverflow
Solution 2 - Sql	Mosty Mostacho	View Answer on Stackoverflow

Sort NULL values to the end of a table

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

How to round up integer division and have int result in Java?

Declare variable in SQLite and use it

Attributions