In Mysql there is a cheap trick to do this: mysql> select ~0; +———————-+ | ~0 | +———————-+ | 18446744073709551615 | +———————-+ the tilde is the bitwise negation. The resulting value is a bigint. See: http://dev.mysql.com/doc/refman/5.1/en/bit-functions.html#operator_bitwise-invert For the other integer flavours, you can use the right bitshift operator >> like so: SELECT ~0 as max_bigint_unsigned … Read more
Consider the following Employee table with a single column for salary. +——+ | Sal | +——+ | 3500 | | 2500 | | 2500 | | 5500 | | 7500 | +——+ The following query will return the Nth Maximum element. select SAL from EMPLOYEE E1 where (N – 1) = (select count(distinct(SAL)) from EMPLOYEE … Read more
Baran’s answer fixed and modified for SQL 2005 SQL 2008 and above: — ============================================= — Author: Baran Kaynak (modified by Kodak 2012-04-18) — Create date: 14.03.2011 — Description: 09:30 ile 17:30 arasındaki iş saatlerini hafta sonlarını almayarak toplar. — ============================================= CREATE FUNCTION [dbo].[WorkTime] ( @StartDate DATETIME, @FinishDate DATETIME ) RETURNS BIGINT AS BEGIN DECLARE @Temp … Read more
Thanks a lot Craig Ringer, works, finally works! You are my new idool now! The steps are: open the cmd SET PGCLIENTENCODING=utf-8 chcp 65001 psql -h your.ip.addr.ess -U postgres
I’m loading 50,000 records in 15 or so seconds using Array Binding in ODP.NET It works by repeatedly invoking a stored procedure you specify (and in which you can do updates/inserts/deletes), but it passes the multiple parameter values from .NET to the database in bulk. Instead of specifying a single value for each parameter to … Read more
You probably need to increase the timeout. Try increasing the value of sqlBulkCopy.BulkCopyTimeout from the default which is 30 seconds.
As you’ve noticed, the WHERE clause doesn’t allow you to use aggregates in it. That’s what the HAVING clause is for. HAVING t1.field3=MAX(t1.field3)
Have a look at this DECLARE @Reviews TABLE( ReviewID INT, ReviewDate DATETIME ) DECLARE @Reviewers TABLE( ReviewerID INT, ReviewID INT, UserID INT ) DECLARE @Users TABLE( UserID INT, FName VARCHAR(50), LName VARCHAR(50) ) INSERT INTO @Reviews SELECT 1, ’12 Jan 2009′ INSERT INTO @Reviews SELECT 2, ’25 Jan 2009′ INSERT INTO @Users SELECT 1, ‘Bob’, … Read more
In PostgreSQL you can do: SELECT * FROM Requirements ORDER BY string_to_array(version, ‘.’)::int[]; This last ::int[] makes it convert string values into integers and then compare as such.
Here is the solution (MySQL) for Hackerrank challenge The Blunder SELECT CEIL((AVG(salary)) – (AVG(REPLACE(salary, ‘0’, ”)))) AS avg_salary FROM employees; REPLACE() : used to remove 0 from salary. AVG() : used to calculate average salary. CEIL() : used to get next rounded integer.