Tag Info

New answers tagged

0

SAMPLE DATA mysql> drop database if exists tschmitty; Query OK, 2 rows affected (0.03 sec) mysql> create database tschmitty; Query OK, 1 row affected (0.00 sec) mysql> use tschmitty Database changed mysql> create table contacts ( -> contact_id int(8) not null auto_increment, #primary key -> pid int(8) not null, ...


0

I have a way that is functional, but not the best for performance: SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 0, 2 UNION SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 6, 2 UNION SELECT * FROM business WHERE name LIKE 'a%' order by name LIMIT 12, 2 ... You can build this query in your code, and change the limits based ...


0

A possible solution based on set operations. From my understanding you have to display all rows that contain a 'ticket' in the status fields and all rows with an 'import' in the status field where there is not a similar row with a 'ticket' in the status field. select id,name,testfield,status from mytable where status='ticket' union select ...


1

IPs with different created_by: SELECT ip , Count(DISTINCT created_by) As number_of_created_by FROM your_table GROUP BY ip HAVING Count(DISTINCT created_by) > 1 Alternative method: SELECT ip , Min(created_by) As min_created_by , Max(created_by) As max_created_by FROM your_table GROUP BY ip HAVING Min(created_by) < ...


0

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises. Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are ...


0

You cannot create view in Laravel using Eloquent/QueryBuilder. You have to use normal sql CREATE VIEW commands in migrations up() function. Kindly have a look at my detailed answer on this thread of stackoverlflow (since its same question by same user there).


1

I don't know if you want to put it in laravel, but, here's the query: SELECT t.ID, t.amount, s.Name as Supplier, sb.Name as Branch FROM Transactions as t JOIN Subsidiary as s ON (s.ID=t.supplier_id) JOIN Subsidiary as sb ON (sb.ID=t.branch_id) WHERE t.ID = 1; Try it. PD: You can change the JOIN syntax for LEFT JOIN if you want to return the t.ID = 1 ...


2

I won't put all of them together. Even if you query them together, it may occur that your application will be able to post multiple images per post or multiple events per post or multiple posts per event. In this case you would be able to save space. The JSON may be a good idea, but it would be better (in view of indices) to use multiple tables. This way ...


3

If you are really sure that your Server name is correct you can still try to specify the port explicitly like that: sqlcmd -S SQLMAIN,1433 -i rdmdb.sql If you run your SQL Server database as a Contained Database it might be necessary to login to the database direclty using -d: sqlcmd -S SQLMAIN,1433 -d yourDatabase -i rdmdb.sql Make sure that you ...


-2

The following should work as per your requirement. However, if there is a tie on the time too, it would pick a random row. You may want to choose a combination that would give you a unique row. select myTable.* from myTable INNER JOIN (select top 1 Value, Time from myTable where subscriber = 'aaa' order by Value desc, Time ) TT ON myTable.Value = ...


3

It's unclear whether you want the total count or the count in the past 24 hours. For the latter: SELECT count(*), page FROM page_views GROUP BY 2 WHERE created_at >= now() - interval '24 hours' -- guessing column HAVING count(*) > 20 ORDER BY 1 DESC; The condition count(*) > 20 has to go into the HAVING clause. You did not provide a table ...


1

Try with: USE `veeva_new`; DELIMITER $$ DROP TRIGGER IF EXISTS veeva_new.userterritory_ai$$ USE `veeva_new`$$ CREATE DEFINER=`root`@`localhost` TRIGGER `veeva_new`.`userterritory_ai` AFTER INSERT ON `userterritory` FOR EACH ROW BEGIN # [ VARIABLES DECLARATION ] DECLARE vUserID VARCHAR(18) DEFAULT ''; DECLARE vTerritoriRep VARCHAR(45) DEFAULT ...


3

The logical built in place to put this kind of metadata about columns might be in the description box when the column is selected in the table designer. Behind the scenes this gets saved as an extended property with MS_Description as the property name. It would be extremely pushing it to claim that this is "standard" or common practice though.


3

select subscriber, date_column, time_column, value from ( select subscriber, date_column, time_column, value, rank() over (partition by subscriber order by value desc, time_column) as r from mytab ) where r = 1 and subscriber = 'ccc';


-1

select a.value from your_table a where a.value = (select max(value) from your_table) and a.time = (select min(b.time) from your_table b where b.value = a.value) That being said a description of the table would help. Is Time a date time value or a string?


4

Identity values are not affected by ROLLBACK operations, so if you insert rows in your table and then issue a ROLLBACK, the identity column will be incremented anyway. Even if your UNIQUE constraint causes the INSERT commands to fail, the identity values are consumed anyway. Example: IF OBJECT_ID('tempdb..#test_identity') IS NOT NULL DROP TABLE ...


1

You can use a TRIGGER to update db1.reps.lastSyncAt when db2.user.lastmodifieddate is updated. Trigger: DELIMITER $$ DROP TRIGGER IF EXISTS db2.user_BEFORE_UPDATE$$ USE `db2`$$ CREATE DEFINER=`root`@`%` TRIGGER `db2`.`user_BEFORE_UPDATE` BEFORE UPDATE ON `user` FOR EACH ROW BEGIN SET @UserVerification=(select reps.veeva_rep_id from db1.reps where ...


1

I am Powershell dude (sounds like I am attending a Powershell Anonymous meeting!!) so this is the script I would use although I would use the objects returned to write to a database for a report or write to Excel or create an HTML email for my manager or whatever was required. Remember the $Servers should hold SERVERNAME\InstanceName,PORTNumber if any of ...


1

There are performance implications in the way one expresses a predicate in a WHERE or JOIN clause. Consider the analogy of a list of current students and alumni in chronological order by graduation date. To find alumni, you could calculate the difference in days between now and each individual's graduation date and consider only those with a negative ...


4

Use a FULL [OUTER] JOIN, combined with two rounds of window functions: SELECT ts , min(foo) OVER (PARTITION BY foo_grp) AS foo , min(bar) OVER (PARTITION BY bar_grp) AS bar FROM ( SELECT ts, f.foo, b.bar , count(f.foo) OVER (ORDER BY ts) AS foo_grp , count(b.bar) OVER (ORDER BY ts) AS bar_grp FROM foo f FULL JOIN bar b ...


2

Like someone said before, use it in 2 queries. select t1.id, sum(pointsup - pointsdown) as points INTO #points from table1 t1 join table2 t2 on t2.Id = t1.id where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01' group by t1.id select DISTINCT p.* FROM #points p INNER JOIN joindate j ON p.id=j.id ORDER BY p.points, j.joindate


4

You could use the function age() to simplify your expression (returns interval). But it's much more efficient to use a sargable expression to begin with. This operates with the exact time difference (current time is relevant): SELECT * FROM accounts WHERE status = 'PENDING_PAYMENT' AND status_updated_at < now() - interval '10 days' To operate ...


1

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.) DECLARE @i INT = 30; -- interval in minutes DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME; SELECT ...


0

Unfortunately, the starting point of your data makes this rather challenging. If it must be done at runtime, this is one possible solution: -- Mimicing existing data DECLARE @TableName TABLE ([User] VARCHAR(6), [Elapsed Time] VARCHAR(5), [Start Time] DATETIME, [End Time] DATETIME) INSERT INTO @TableName VALUES ('User01', '01:05', '07-02-2015 08:45', ...


0

Fun problem! As Aaron mentions in his comment, a few things are ambiguous. But I put together a script that could be used as a good starting point. The script first identifies all possible 30 minute intervals, then matches user "events" to all matching intervals and does some date math to compute your elapsed times. The script also attempts to handle a ...


0

After researching and reading different threads on this website I was able to resolve my own issue... The syntax below will allow you to place comments either before or after your query, so that the comments display on the output.txt file BEGIN DBMS_OUTPUT.PUT_LINE('TEXT GOES HERE '); END; / Here are some commands you can use while using ...


1

If you give the ALTER TABLE command while there are concurrent insertions, this could be possible, unless you set an high ISOLATION LEVEL. After the command has been issued, every new insertion will provide the correct default value. If null values remain in the column after the initial ALTER TABLE, you can simply change them with an update, e.g. UPDATE ...


2

Maybe I don't get your question right. But here is code for such a ranking. You may need to adapt it to your use-case. -- Create demo data CREATE TABLE #users(user_id int identity(1,1), points int, registration datetime) INSERT INTO #users(registration) VALUES (GETDATE()), -- 1: first, because of highest points (GETDATE()), -- 2: fourth ...


1

The best query very much depends on data distribution. You have many rows per date, that's been established. Since your case burns down to only 26 values in the result, all of these solutions will be blazingly fast as soon as the index is used. The partial index below will be a bit faster if you have many NULL values. For more distinct values it would get ...


2

You could try an indexed view. I know you said you couldn't add an index to the table but this would be creating a view and then indexing it. CREATE VIEW vw_Message WITH SCHEMABINDING AS SELECT month_date= month(SendTime) ,year_date =year(sendtime) ,MessageType, COUNT_BIG(*) AS MESSAGE_COUNT FROM dbo.Message where ...


0

I think you are missing a space before 'Set': SELECT @String = @String + 'Set Internet = 1' Try this: SELECT @String = @String + ' Set Internet = 1'


4

Something seems really, really, really bad in this design. Do you really have a large number of tables that have a column called Internet? What does that column even mean? Do you really want to call this stored procedure such that it updates every row in the specified table? Why? You don't have a one-row table for every location / city / room, do you? ...


1

No. A date in Oracle will always have a year, month, day, hour, minute, and second. You can choose to display only certain components of the date in your front end and to give a consistent default to the other components of the date. It's pretty common, for example, to let the time component of the date default to midnight. You could force the year to be ...


1

Had a situation where a production environment had DB schema updated The developer executed an alter on the base procedure, with no changes, and the execution time returned back to normal and they asked why. Certain changes in a database can cause an execution plan to be either inefficient or invalid, based on the new state of the database. SQL ...


0

I'm not sure how you what the output to display this. DECLARE @vtExp TABLE ( ACCCODE CHAR(5) NOT NULL, ACCName VARCHAR(50) NOT NULL, ACCParentCode CHAR(50) NULL, ACCExpense FLOAT NULL ) INSERT INTO @vtExp (ACCCODE,ACCName,ACCParentCode,ACCExpense) VALUES('00000','Account 00000',NULL,0) ,('00001','Account 00001','00000',0) ...


1

Here's your query: SELECT T.location_id, GROUP_CONCAT(T.service_id) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING serviceids IN ('2,3'); You could use ORDER BY inside the GROUP_CONCAT(): SELECT T.location_id, GROUP_CONCAT(T.service_id ORDER BY service_id ASC) AS serviceids FROM `Table` AS T GROUP BY T.location_id HAVING ...


2

Hmm... I'm a bit confused. By definition, if two events happen at different places, then they're different events. My advice in this scenario is to tave 3 tables as follows: CREATE TABLE Event ( Event_ID INTEGER, Event_Description INTEGER, -- FK Event_Location INTEGER, -- FK Event_Date DATETIME ); And then two other "parent" tables CREATE ...


0

Here's a kludgy way: SELECT location_id, GROUP_CONCAT(service_id) AS service_ids FROM tbl GROUP BY location_id HAVING service_ids = '2,3';


3

Oh Boy! System Databases on C Drive are recipe for disaster ! Change the default database location away from C:\ drive. You can do it using ALTER DATABASE .. MODIFY FILE Change the db file location ALTER DATABASE db_name MODIFY FILE (NAME = logical_data_file, FILENAME = 'D:\data\physical_file.mdf'); ALTER DATABASE db_name MODIFY FILE (NAME = ...


6

;WITH x AS ( SELECT ID, VAL, Prev_VAL = LAG(VAL, 1) OVER (ORDER BY ID) FROM dbo.your_table ) SELECT ID, VAL, Prev_VAL, Cumulative = VAL + Prev_VAL FROM x WHERE VAL < Prev_VAL ORDER BY ID; If you need to support versions older than SQL Server 2012 (when LAG() was introduced), you can do this, but by casual and hardly-scientific observation, it is ...


7

Since you can reset the IDENTITY by issuing a simple TRUNCATE: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10) FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] INNER JOIN sys.identity_columns AS ic ON t.[object_id] = ...


3

You can write a script to call DBCC CHECKIDENT ([TABLENAME], RESEED, 0) over the tables resulting from this query (which will inform of all the tables with an identity field. SELECT t.TABLE_NAME ,c.COLUMN_NAME ,c.TABLE_CATALOG ,c.TABLE_SCHEMA FROM ...


0

I have the following solution, which gives the desired result, except the values for the days that there is no entries for: set @total=0; set @sku_name=''; SELECT date, sku, total, CATEGORY FROM ( SELECT date, sku, @total:=IF(@sku_name=sku, @total, 0)+qty AS total, @sku_name:=sku FROM ( SELECT date, sku, SUM(qty) qty FROM ...


5

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


1

I did this once as an exercise in dynamic SQL. It's NOT a good idea for any production system. Primarily you add a huge risk of SQL Injection. This is where someone would pass an actual SQL command in as part of one of the parameters and you end up running it without realizing it. You can minimize this but even so there is the risk. Also there is a ...


0

From the postgresql documentation: CLUSTER can re-sort the table using either an index scan on the specified index, or (if the index is a b-tree) a sequential scan followed by sorting. It will attempt to choose the method that will be faster, based on planner cost parameters and available statistical information. Your index on labelDate is a btree.. ...


-3

log into sqlplus as username:SYSTEM give previous password sql>password old_password:<oldpassword> new_password:<new password> retype new password:<new password> password is set


0

You should be able to enable logging for debugging purposes, however the question of whether to log every SELECT depends on how much this data is worth to you - are you prepared to spend the money on a server powerful enough to log this data and the price of a backup strategy for this data. Furthermore, there's little point in logging everything if you ...


0

If your system gets pounded with hundreds of queries every second and you have a storage constraint, it does not make sense to log every query.Assume the maximum allowed response time at user-end is x seconds. Other tiers plus network latency may take upto y seconds. So the maximum permissible time at db tier is x-y seconds. Any query that takes more than ...



Top 50 recent answers are included