How to move up not null alphabetically sorted values in MySQL?

What you seem to want is something rather unusuarl (at least from my perspective), where the concept of row is no longer important, and you care only about columns, ignoring from which row its values came from. This is a bit tricky, because SQL understands columns together with rows. So you need, somehow, to deconstruct your tables and reconstruct them later on.

First thing we will do is have a different table (result of a subquery) with two columns: a row_number() and one NameX:

For every column, we will do something like:

-- First column
SELECT
     @rownum := @rownum + 1 AS rank,
     Name1
FROM
    t,  (SELECT @rownum := 0) r
ORDER BY
    -- 'zzzzzzz' = value larger than any realistically one
    -- in column Name1. Change if necessary.
    COALESCE(Name1, 'zzzzzzzzzz'),

... and that would result in:

rank | Name1
---: | :----
   1 | Julia
   2 | Zoe
   3 | null
   4 | null
   5 | null
   6 | null
   7 | null
   8 | null
   9 | null
  10 | null

This is one deconstruction (to put it a name).

NOTE: @rownum := @rownum + 1 is equivalent to using ROW_NUMBER() in stanrdard SQL. See ROW_NUMBER() in MySQL. MySQL doesn't have a row_number() function, but lets you instead use variables in your queries (See MySQL 5.7 Reference Manual  /  Language Structure  /  User-Defined Variables / 9.4 User-Defined Variables)

With this in mind, we then need to do:

Repeat 4 times, for Name1 .. Name4, to get actually 4 (virtual) tables. We will have actually deconstructed the original table.
We have now 4 (virtual) tables with a common column (rank) and with the same number of rows as the original table. We JOIN them together on this common column.
We get rid of the rows that are NULL end to end by adding a WHERE NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

The full query will then be:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT   @rownum1 := @rownum1 + 1 AS rank, Name1
    FROM      t, (SELECT @rownum1 := 0) AS r1
    ORDER BY  coalesce(Name1, 'zzzzzzzzz')
    )  c1
    JOIN
    (SELECT   @rownum2 := @rownum2 + 1 AS rank, Name2
    FROM      t, (SELECT @rownum2 := 0) AS r2
    ORDER BY  coalesce(Name2, 'zzzzzzzzz')
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT   @rownum3 := @rownum3 + 1 AS rank, Name3
    FROM      t, (SELECT @rownum3 := 0) AS r3
    ORDER BY  coalesce(Name3, 'zzzzzzzzz')
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT   @rownum4 := @rownum4 + 1 AS rank, Name4
    FROM      t, (SELECT @rownum4 := 0) AS r4
    ORDER BY  coalesce(Name4, 'zzzzzzzzz')
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

And this will give you your intended result:

Name1 | Name2  | Name3 | Name4
:---- | :----- | :---- | :----
Julia | Ashley | Jane  | Eve
Zoe   | Belvet | Mark  | Jenny
null  | Maria  | null  | Ketty

You can check everything together at dbfiddle here

If you're using MariaDB 10.2 or other database that knows of WINDOW functions, you can simplify this query to:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by coalesce(Name1, 'zzzzzzzzz')) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by coalesce(Name2, 'zzzzzzzzz')) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by coalesce(Name3, 'zzzzzzzzz')) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by coalesce(Name4, 'zzzzzzzzz')) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL)

and you will get the same result.

dbfiddle here

You can even play to your advantage the fact that MariaDB sorts NULLS LAST, and ignore the COALESCE:

SELECT
    Name1, Name2, Name3, Name4
FROM
    (SELECT Name1, row_number() over (order by Name1) AS rank
    FROM t
    )  c1
    JOIN
    (SELECT Name2, row_number() over (order by Name2) AS rank
    FROM t
    )  c2
    ON c2.rank = c1.rank
    JOIN
    (SELECT Name3, row_number() over (order by Name3) AS rank
    FROM t
    )  c3
    ON c3.rank = c1.rank
    JOIN
    (SELECT Name4, row_number() over (order by Name4) AS rank
    FROM t
    )  c4
    ON c4.rank = c1.rank
WHERE
    NOT (Name1 IS NULL AND Name2 IS NULL AND Name3 IS NULL AND Name4 IS NULL);

dbfiddle here

NOTE: The execution plans are identical, so the last two queries are expected to take about the same time to perform.


sql join query through Adventure works database

Select pr.Color, count(*), sum(sr.UnitPrice * sr.OrderQty) as total
from Production.Product as pr
Join Sales.SalesOrderDetail as sr
on sr.ProductID=pr.ProductID
WHERE pr.Color IS NOT NULL
GROUP BY pr.Color
HAVING  sum(sr.UnitPrice * sr.OrderQty) >50000
 ;


json_array_elemnts() in FROM in postgresql 9.3 acting as where clause

This query requests the cartesian product between incidents_incident and json_array_elements(...).

When one of the elements of a product is empty, the result is empty.

This result doesn't depend on the WHERE clause or the fact that one the product comes from json_array_elements(). An empty table would have the same effect.

Presumably you want an unconstrained LEFT JOIN instead of the cartesian product:

SELECT count(*)
FROM incidents_incident
LEFT JOIN json_array_elements(incidents_incident.incident_data -> 'field_52ca447762ba2')
  ON (true)
WHERE resort_id = 1
AND dt_created >= '2015-06-01 00:00:00'
AND dt_created <= '2015-08-10 00:00:00';


How to make this query use my multicolumn index?

The expression lower(name::text) is not covered by your index, which also does not seem to be useful at all, at least for what we see in your question:

CREATE INDEX crm_customer_big_index
    ON crm_customer (name ASC,
                     name text_pattern_ops,
                     (id::text) text_pattern_ops,
                     phone text_pattern_ops,
                     mail text_pattern_ops,
                     rating);

This index would cover your query:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(name::text) text_pattern_ops
, address_id);  -- for the join

To optimize, I suggest to adapt your query additionally:

SELECT *
FROM   customer_list
WHERE  lower(name::text) LIKE 'env%'
AND    rating = 3
ORDER  BY lower(name::text), name
LIMIT  20;

Also, if you are only interested in the first few characters of name character varying(100), make the index shorter and faster by just indexing those first few characters:

CREATE INDEX crm_customer_foo_index ON crm_customer (
  rating
, lower(left(name::text, 7)) text_pattern_ops  -- example with local optimum
, address_id);  -- for the join

The query must match the index expression:

WHERE  lower(left(name::text, 7)) LIKE 'env%'
...
ORDER  BY lower(left(name::text, 7)), name

Not sure if the view is in the way somehow. But it should be transparent.

I wouldn't use varchar(n) at all. Just text:

Should I add an arbitrary length limit to VARCHAR columns?

About pattern matching and text_pattern_ops:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Why did I put rating first in the index?

Multicolumn index and performance

There are query techniques to better capitalize on non-leading index columns. Consider the Postgres Wiki on "Loose indexscan".


Usually the cause for this is that you are restart the mongod process as the root user (using sudo, or perhaps invoking directly as root) or some other user besides the appropriate MongoDB user (in your case mongod).  That is then screwing up the permissions in your data folder and giving you the error you are seeing.

If you examine how you are starting/stopping the process and correct it to only start with the correct user (or use the service commands instead) then this problem should stop happening.


Try to test the following:

select
TO_CHAR (issue_date, 'D', 'NLS_DATE_LANGUAGE=HINDI) IN (6, 7)
from table

To see if it works for you. One thing you need to check is, depending on the country, if the week starts at a Monday or Sunday. You might have to change those in values accordingly.
Edit: doing this, you're only changing the value on the query. Isn't it what you wanted? And sorry for not getting the value right. Corrected.


How to pass a table of values to a stored procedure from SSIS?

Is there no way to call the stored procedure and pass it a table?

There is, if you're using SQL Server 2008 or higher: table-valued parameters (TVPs).

I've already blogged about how to use TVPs in SSIS. Essentially, you have to use ADO.NET inside a Script Component. While this isn't the prettiest solution, it gets the job done relatively cleanly with all the code in one spot.


Troubleshooting Failed SQL login from WMI service

tracing WMI activity

http://msdn.microsoft.com/en-us/library/windows/desktop/aa826686%28v=vs.85%29.aspx

Logging WMI activity

http://msdn.microsoft.com/en-us/library/windows/desktop/aa392285%28v=vs.85%29.aspx


Database version is higher than SSMS and throwing error when attempted to create table

AS MSDN Blog Here  Use the SSMS version designed for your database version. You can use the command select @@version; to check which version of SQL Server you are actually using.  Update your SSMS to the latest version and try.  You may have the latest version of SQL Server and older version of SSMS.

To Download the latest SQL Server Management Studio (SSMS) https://msdn.microsoft.com/en-us/library/mt238290.aspx

For your further ref https://stackoverflow.com/questions/25146474/the-backend-version-is-not-supported-to-design-database-diagrams-or-tables


DDL statements being commited within transaction

SQL Server does not auto-commit an explicit transaction.

What might be happening in you case is that the transaction is aborted and rolled back, but your script continues, executing the remainder without a wrapping transaction. This is particularly likely if you have GO statements in your script.

To implement cross batch error handling you can do something like this:

BEGIN TRAN
 CREATE TABLE #OriginalTransaction(i INT);
 BEGIN TRY
   -- do stuff here
 END TRY
 BEGIN CATCH
   IF(@@TRANCOUNT>0)ROLLBACK;
 END CATCH;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
  BEGIN TRY
    -- do more stuff here
  END TRY
  BEGIN CATCH
    IF(@@TRANCOUNT>0)ROLLBACK;
  END CATCH;
 END;

GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   COMMIT;
 END;

GO
 IF(@@TRANCOUNT>0)ROLLBACK;
GO
 IF(OBJECT_ID('tempdb..#OriginalTransaction') IS NOT NULL)
 BEGIN
   DROP TABLE #OriginalTransaction;
 END;


DBeaver: Access denied for user 'root'@'localhost' (using password: YES)

Create new user with password and full privileges as described here:
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'the_password_you_wish_here';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

...and login with this user for further usages.