Here is another nice solution for the Max functionality using T-SQL and SQL Server SELECT [Other Fields], (SELECT Max(v) FROM (VALUES (date1), (date2), (date3),…) AS value(v)) as [MaxDate] FROM [YourTableName] Values is the Table Value Constructor. “Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows … Read more
You need to enable logging for the the following categories: org.hibernate.SQL – set to debug to log all SQL DML statements as they are executed org.hibernate.type – set to trace to log all JDBC parameters So a log4j configuration could look like: # logs the SQL statements log4j.logger.org.hibernate.SQL=debug # Logs the JDBC parameters passed to … Read more
All solutions I found were much more complex than necessary and none worked for me. Here is the solution that solved my problem. There isn’t any need to restart mysqld or start it with special privileges. sudo mysql — for MySQL ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘root’; — for MariaDB ALTER USER ‘root’@’localhost’ … Read more
It’s true that if your primary key is just an identity value auto incremented, you would have no real use for ON UPDATE CASCADE. However, let’s say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that … Read more
You could use GROUP_CONCAT aggregated function to get all years into a single column, grouped by id and ordered by rate: SELECT id, GROUP_CONCAT(year ORDER BY rate DESC) grouped_year FROM yourtable GROUP BY id Result: ———————————————————– | ID | GROUPED_YEAR | ———————————————————– | p01 | 2006,2003,2008,2001,2007,2009,2002,2004,2005,2000 | | p02 | 2001,2004,2002,2003,2000,2006,2007 | ———————————————————– And then … Read more
You’re inserting values for OperationId that is an identity column. You can turn on identity insert on the table like this so that you can specify your own identity values. SET IDENTITY_INSERT Table1 ON INSERT INTO Table1 /*Note the column list is REQUIRED here, not optional*/ (OperationID, OpDescription, FilterID) VALUES (20, ‘Hierachy Update’, 1) SET … Read more
The exists keyword can be used in that way, but really it’s intended as a way to avoid counting: –this statement needs to check the entire table select count(*) from [table] where … –this statement is true as soon as one match is found exists ( select * from [table] where … ) This is … Read more
There is no such thing as “auto_increment” or “identity” columns in Oracle as of Oracle 11g. However, you can model it easily with a sequence and a trigger: Table definition: CREATE TABLE departments ( ID NUMBER(10) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL); ALTER TABLE departments ADD ( CONSTRAINT dept_pk PRIMARY KEY (ID)); CREATE SEQUENCE dept_seq … Read more
I would just add group_id to the GROUP BY. When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how … Read more
An index is used to speed up searching in the database. MySQL have some good documentation on the subject (which is relevant for other SQL servers as well): http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html An index can be used to efficiently find all rows matching some column in your query and then walk through only that subset of the table … Read more