Tag Info

Hot answers tagged

3

SELECT column_a ,column_b ,column_c ,column_d FROM mytable WHERE column_a = column_b AND column_a = column_c AND column_a = column_d ETC. SQL Fiddle Link My Notes CREATE TABLE mytable ( column_a int, column_b int, column_c int, column_d int ); INSERT INTO mytable VALUES (1,2,4,7); INSERT INTO mytable VALUES (6,3,5,6); INSERT INTO mytable ...


2

You could unpivot the data. I'm going to add an surrogate ID column to your table because this will make reconciliation easier. The unpivoted data will look like this: id SourceCol SourceVal 1 A 3 1 B 3 1 C 3 ... 1 N 3 2 A 5 2 B 5 2 C 5 ... 2 N 5 Then you group by ID and ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...


1

Let me help you to understand how a "standard" index works. Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The ...


1

select c.classid, c.classname, count(case when s.status = 'failed' then 1 end) as no_of_failed, count(case when s.status = 'passed' then 1 end) as no_of_passed from class c left join students s on c.classid = s.classid where c.schedule = 'MWF' group by c.classiid, c.classname; This exploits the fact that aggregate functions will ...


1

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters: DECLARE @someParam INT = 5; EXEC LinkedServerName.master.sys.sp_executesql @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;', @params = N'@i INT', @i = @someParam;


1

In Postgres 9.4 you can simplify with the aggregate FILTER clause: CREATE VIEW fumbbl.matches AS SELECT m.fmid, m.time, d.name AS division , min(s.coachbracket) FILTER (WHERE NOT s.away) AS hbracket , min(t.name) FILTER (WHERE NOT s.away) AS hteam , min(c.name) FILTER (WHERE NOT s.away) AS hcoach , min(s.score) ...


1

You're correct: FULLTEXT search didn't hit InnoDB until MySQL 5.6. This leaves you with a few options: Update to MySQL 5.6 and use a FULLTEXT index Change the contract of your function to only allow prefix searches; that is, 'term%'. It will fulfill many use cases while saving your DB. Convert to a MyISAM table, or create a spare MyISAM table that you can ...



Only top voted, non community-wiki answers of a minimum length are eligible