To retrieve the number of widgets from each widget category that has more than 5 widgets, you could do this: SELECT WidgetCategory, count(*) FROM Widgets GROUP BY WidgetCategory HAVING count(*) > 5 The “having” clause is something people often forget about, instead opting to retrieve all their data to the client and iterating through it … Read more
I haven’t tested it, so the syntax might not be perfect, but what about something like this : select name, count(*) as frequency from your_table group by name order by count(*) desc Should give you unique names and the corresponding number of times each name appears in the table, ordered by that number.
A view can be simply thought of as a SQL query stored permanently on the server. Whatever indices the query optimizes to will be used. In that sense, there is no difference between the SQL query or a view. It does not affect performance any more negatively than the actual SQL query. If anything, since … Read more
Probably because the * in select * selects two columns with the same name from tip_usage and tips.
UPDATE tablename SET col1 = CASE WHEN name=”name1″ THEN 5 WHEN name=”name2″ THEN 3 ELSE 0 END , col2 = CASE WHEN name=”name1″ THEN ” WHEN name=”name2″ THEN ‘whatever’ ELSE ” END ;
You’re very close. declare @qry nvarchar(max); select @qry = (SELECT ‘DROP INDEX ‘ + quotename(ix.name) + ‘ ON ‘ + quotename(object_schema_name(object_id)) + ‘.’ + quotename(OBJECT_NAME(object_id)) + ‘; ‘ FROM sys.indexes ix WHERE ix.Name IS NOT null and ix.Name like ‘%prefix_%’ for xml path(”)); exec sp_executesql @qry
MySQL doesn’t support ANSI PIVOT/UNPIVOT syntax, so that leave you to use: SELECT t.userid MAX(CASE WHEN t.fieldname=”Username” THEN t.fieldvalue ELSE NULL END) AS Username, MAX(CASE WHEN t.fieldname=”Password” THEN t.fieldvalue ELSE NULL END) AS Password, MAX(CASE WHEN t.fieldname=”Email Address” THEN t.fieldvalue ELSE NULL END) AS Email FROM TABLE t GROUP BY t.userid As you can see, … Read more
I believe your join is wrong: $shares = DB::table(‘shares’) ->join(‘users’, ‘users.id’, ‘=’, ‘shares.user_id’) ->join(‘followers’, ‘followers.user_id’, ‘=’, ‘users.id’) ->where(‘followers.follower_id’, ‘=’, 3) ->get(); I also suggest you to name your table as follows instead, it feels a bit more natural to say user has many followers through follows and user has many followees through follows. Example $shares … Read more
Unique – Guarantees unique values for the column(or set of columns) included in the index Covering – Includes all of the columns that are used in a particular query (or set of queries), allowing the database to use only the index and not actually have to look at the table data to retrieve the results … Read more
Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line. I typically get around using the CONVERT function which allows me to instruct SQL Server to skip those instructions – use something like this: INSERT INTO testfiles (filename, filemeta) VALUES (‘test.mp3’, CONVERT(XML, N'<?xml … Read more