Count all the DISTINCT program names by program type and push number SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] FROM cm_production WHERE push_number=@push_number GROUP BY program_type DISTINCT COUNT(*) will return a row for each unique count. What you want is COUNT(DISTINCT <expression>): evaluates expression for each row in a group and returns the number … Read more
Congratulations, you’ve hit my favorite pet peeve with JDBC: Date class handling. Basically databases usually support at least three forms of datetime fields which are date, time and timestamp. Each of these have a corresponding class in JDBC and each of them extend java.util.Date. Quick semantics of each of these three are the following: java.sql.Date … Read more
Finally, Microsoft SQL Server 2012 was released, I really like its simplicity for a pagination, you don’t have to use complex queries like answered here. For getting the next 10 rows just run this query: SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql#using-offset-and-fetch-to-limit-the-rows-returned Key points to consider … Read more
Now that MySQL 8.0 supports recursive queries, we can say that all popular SQL databases support recursive queries in standard syntax. WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree; … Read more
You don’t need wildcards in the REPLACE – it just finds the string you enter for the second argument, so the following should work: UPDATE dbo.xxx SET Value = REPLACE(Value, ‘123’, ”) WHERE ID <=4 If the column to replace is type text or ntext you need to cast it to nvarchar UPDATE dbo.xxx SET … Read more
Via SQL as per MSDN SET IDENTITY_INSERT sometableWithIdentity ON INSERT INTO sometableWithIdentity (IdentityColumn, col2, col3, …) VALUES (AnIdentityValue, col2value, col3value, …) SET IDENTITY_INSERT sometableWithIdentity OFF The complete error message tells you exactly what is wrong… Cannot insert explicit value for identity column in table ‘sometableWithIdentity’ when IDENTITY_INSERT is set to OFF.
See this post: SQL to Select a random row from a database table. It goes through methods for doing this in MySQL, PostgreSQL, Microsoft SQL Server, IBM DB2 and Oracle (the following is copied from that link): Select a random row with MySQL: SELECT column FROM table ORDER BY RAND() LIMIT 1 Select a random … Read more
Strictly, method a is the least resource intensive: a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: Most efficient way in SQL Server to get a date from date+time? I saw a similar test elsewhere … Read more
If you’re using SQL Server 2008 (or above), then this is the better solution: SELECT o.OrderId, (SELECT MAX(Price) FROM (VALUES (o.NegotiatedPrice),(o.SuggestedPrice)) AS AllPrices(Price)) FROM Order o All credit and votes should go to Sven’s answer to a related question, “SQL MAX of multiple columns?” I say it’s the “best answer” because: It doesn’t require complicating … Read more
That format requires you to use either: CASE ebv.db_no WHEN 22978 THEN ‘WECS 9500’ WHEN 23218 THEN ‘WECS 9500’ WHEN 23219 THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system Otherwise, use: CASE WHEN ebv.db_no IN (22978, 23218, 23219) THEN ‘WECS 9500’ ELSE ‘WECS 9520’ END as wecs_system