Try this: select distinct * from test where b is not null or a in ( select a from test group by a having max(b) is null) You can get the fiddle here. Note if you can only have one non-null value in b, this can be simplified to: select a, max(b) from test group … Read more
The @ makes it a user defined session variable. Otherwise it would be locally scoped variable (in a stored procedure), you would have to DEFINE your local before you can SET it. You could also set a global system variable (with SET GLOBAL or SET @@global) if you wanted to. As well as a session … Read more
Both INFORMATION_SCHEMA and sys objects are both metadata catalogs that are available in SQL Server. The INFORMATION_SCHEMA set of views are the ANSI/ISO standard catalogs for metadata. Most RDBMSs support the majority of INFORMATION_SCHEMA views, and each view exposes essentially identical information regardless of the vendor. In SQL Server, most, if not all, the INFORMATION_SCHEMA … Read more
You don’t have to group by the exactly the same thing you’re selecting, e.g. : SQL:select priority,count(*) from rule_class group by priority PRIORITY COUNT(*) 70 1 50 4 30 1 90 2 10 4 SQL:select decode(priority,50,’Norm’,’Odd’),count(*) from rule_class group by priority DECO COUNT(*) Odd 1 Norm 4 Odd 1 Odd 2 Odd 4 SQL:select decode(priority,50,’Norm’,’Odd’),count(*) … Read more
Depends on Storage Engine MyISAM (Caches Index Pages From .MYI files) SELECT FLOOR(SUM(index_length)/POWER(1024,2)) IndexSizesMB FROM information_schema.tables WHERE engine=”MyISAM” AND table_schema NOT IN (‘information_schema’,’performance_schema’,’mysql’); Subtract that from key_buffer_size. If the answer > 0, then Yes InnoDB (Caches Data and Index Pages) SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB FROM information_schema.tables WHERE engine=”InnoDB”; Subtract that from innodb_buffer_pool_size. If the answer > … Read more
You cannot DELETE from multiple tables with a single expression in SQL 2005 – or any other standard SQL for that matter. Access is the exception here. The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger.
The only answer I found was in this message: https://www.postgresql.org/message-id/3EE8B4ED.1060200%40pgsqldb.com Basically, I guess, the answer is: select cast(extract(epoch from current_timestamp) as integer);
That’s a new one for me. If I read that correctly the <boolean value expression> grammar defines three predicates solely for use with the boolean datatype IS TRUE, IS FALSE, IS UNKNOWN. These differ from their equality counterparts in that they only evaluate to True or False. Never to Unknown. i.e. UNKNOWN = TRUE would … Read more
In SQL Server, you can query sys.columns. Something like: SELECT t.name FROM sys.columns c inner join sys.tables t on c.object_id = t.object_id WHERE c.name=”NameID” You might want an additional lookup to resolve the schema name, if you have tables in multiple schemas.
I wouldn’t use performance as the deciding factor here – and quite honestly, I don’t think there’s any measurable performance difference between those two cases, really. I would always use case #2 – why? Because in my opinion, you should only put the actual criteria that establish the JOIN between the two tables into the … Read more