I’m trying to get the MAX on a column which is generated dynamically using the SUM statement. The SUM statement is used together with the ‘GROUP by’ syntax. This is the original query, however it needs to be modified to work with grouping, sums and of course MAX. As you can see SUM is adding…
Tag: sql
Convert timestamp/date time from UTC to EST Oracle SQL
I have a field with a date/time value like this: It’s in UTC. In the query how can I convert this to EST? I’m trying something like this but it throws an error. Answer I had to tweak it slightly to get it to work on my database, but this worked: The key is the “at time zone” syntax.
SQL constraint minvalue / maxvalue?
Is there a way to set a SQL constraint for a numeric field that min value should be 1234 and max value should be 4523?
Combining data from different tables to form a news feed
Let’s say I have 2 tables, Message and Product. Whenever a user post a new products or messages, users who subscribe to that particular user will have their feed updated.It is similar to Facebook feed….
sql while loop with date counter
I need to do something like this in sql: How can I do the above correctly in SQL? Basically, my startdate and enddate are strings and not datetimes because my business logic is referencing string column in another table with a date as the name of the column-But I need to loop through a bunch of columns, each …
Reset or Update Row Position Integer in Database Table
I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this R…
SELECT (* – some_columns) FROM TABLE in SQL
I have a table with many columns among which I’d like to omit a few alone in my ‘select’ query. Something like select (* – columns_to_be_omitted) from myTable. Is there a way to do this, other than listing all the other columns in the query? This is a one-time manual query, so I’…
Trunc(sysdate) in SQL Server
What is the equivalent of: …in SQL Server 2005? Answer Recommended: This is another alternative, but it’s risky because of casting to a FLOAT. It’s also been demonstrated to not scale performance as well as the DATEADD/DATEDIFF approach.
Efficient way to check if a SQL query will return results
I would like to write a query that simply returns 1 or 0 depending if there will be results. I am thinking to use this That’s the general premise. The final results will actually be a far more complex query, taking one to many parameters and the string built up and executed using sp_executesql My questi…
Insert if not exists Oracle
I need to be able to run an Oracle query which goes to insert a number of rows, but it also checks to see if a primary key exists and if it does, then it skips that insert. Something like: Is this at all possible with Oracle? Bonus points if you can tell me how to do this in PostgreSQL