Try this: SELECT a.*, CASE WHEN totalPayments>1 THEN ‘NO’ ELSE ‘YES’ END IsFirstPayment FROM( SELECT p.payment_id, p.user_id, ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber, SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments FROM payment p ) a WHERE paymentNumber = 1
Thanks for your comment. That didn’t quite work but it steered me in the right direction. I was finally able to solve this by adding distinct to both Count() calls: Count(‘tourcomment’, distinct=True)
MySQL SELECT GROUP_CONCAT(t.prodid SEPARATOR ‘,’) FROM PRODUCTS t WHERE t.prodtype=”XYZ” Oracle: There is an excellent summary of the available string aggregation techniques on Tim Hall’s site. SQL Server 2005+ SELECT STUFF((SELECT ‘,’+ t.prodid FROM PRODUCTS t WHERE t.prodtype=”XYZ” FOR XML PATH(”)), 1, 1, ”)
What you need to do is first, unpivot the data and then pivot it. But unfortunately MySQL does not have these functions so you will need to replicate them using a UNION ALL query for the unpivot and an aggregate function with a CASE for the pivot. The unpivot or UNION ALL piece takes the … Read more
UPDATE 2: org.hibernate.jdbc.util.BasicFormatterImpl got moved in release 4.0. It is now located at: org.hibernate.engine.jdbc.internal.BasicFormatterImpl. UPDATE 1: Technology marches on. As noted by Alex, org.hibernate.pretty.Formatter no longer exists as of version 3.3.2.GA. The replacement is org.hibernate.jdbc.util.BasicFormatterImpl: String formattedSQL = new BasicFormatterImpl().format(sql); ORIGINAL ANSWER: If you’re using Hibernate, they’ve got one built-in: org.hibernate.pretty.Formatter String formattedSQL = new … Read more
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/6529a35b-6629-44fb-8ea4-3a44d232d6b9/ var people = entities.People.Where(“it.Name LIKE @searchTerm”, new ObjectParameter(“searchTerm”, searchTerm));
SELECT [schema] = s.name, [table] = t.name FROM sys.schemas AS s INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.identity_columns WHERE [object_id] = t.[object_id] );
In DataGrip as other intellij-based IDEs everything is about source editing. So that can be achieved that way: Open console Start typing sel, you’ll get completion popup (if not, hit Ctrl+Space) Select sel here, which is live-template for select statement Select statement will be generated, asking for table name and column list Select desired table … Read more
A common question, though filtering out suggests you want to ignore the duplicate ones? If so, listing unique values: SELECT col FROM table GROUP BY col HAVING (COUNT(col) =1 ) If you just want to filter so you are left with the duplicates SELECT col, COUNT(col) AS dup_count FROM table GROUP BY col HAVING (COUNT(col) … Read more
You can just use cast it to an integer. It will truncate it, which is equivalent to floor.