Tag Info

New answers tagged

0

select usr.nick_name, count(ans.ans_given) from user usr, question que, answer ans where usr.user_id = ans.user_id and que.question_id = ans.question_id and que.right_ans = ans.ans_given group by usr.user_id That was the solution, but how to show 0 if an user did not answer anything correctly?(thanks szakwani)


0

You can learn from this example : create table dbo.ProductsCount (prdid int , cnt int) go insert into dbo.ProductsCount (prdid, cnt) select 111,2 union all select 111,5 union all select 112,10 union all select 112,20 go select * from dbo.ProductsCount order by prdid; go -- delete duplicates with CTE and ROW_NUMBER() ; WITH C AS ( select ...


0

select u.id, u.name, bool_or (a.admin) as is_admin, bool_and (a.admin) as is_complete_admin from users u inner join associations a on u.id = a.user group by u.id, u.name


0

You can utilize the returning keyword which will return the value of the created serial column. If you wrap you Insert in a With expression, you can then access the returned id and insert into the second table. WITH getval(id) as (INSERT INTO table_a (some_col) VALUES (some_val) RETURNING id) INSERT into table_b (id) SELECT id from getval; where id ...


1

A better index would be (assuming ORDER BY IdTrackerPosition DESC is correct, and the query should not specify ORDER BY [Date] DESC instead): CREATE UNIQUE INDEX i -- Choose a better name! ON dbo.TrackerPositions (TrackerId, IdTrackerPosition DESC) INCLUDE (Position, [Date], Speed, NbSatellites, Direction, HDOP); The execution plan should look ...


2

Can anyone tell how exactly apply works and how will it effect the performance in very large data APPLY is a correlated join (called a LATERAL JOIN in some products and newer versions of the SQL Standard). Like any logical construction, it has no direct impact on performance. In principle, we should be able to write a query using any logically ...


0

The first query may run parallel by only one request to sql server. It fetched the all record and gives output based on filter criteria. But in case of second one it runs row by row and for each row Table2 will be scanned and appended to result. if your outer query has less record then Second one is better(OUTER APPLY). But if first query may get more data ...


-1

select usr.nick_name, count(ans.ans_given) from user usr, question que, answer ans where usr.user_id = ans.user_id and que.question_id = ans.question_id and que.right_ans = ans.ans_given; it would help if you could create the DDL in sql fiddle and we could have a working environment. If you chose to vote down, kindly leave a comment.


7

You have a heap. Heaps don't clear out space with DELETEs in most cases. You could truncate the table, or you could put a clustered index on the table. Heaps are great for insert-heavy systems, but not great if there are lots of deletes.


2

Your index should be on (TrackerId ASC, [Date] DESC) INCLUDE (Position) so that it can easily locate the most recent one for each Tracker. But I really don't like the query from EF. Edit: ...because "most recent" should mean "latest datetime", not "latest identity value"


0

in MySQL you can create a trigger. I tried on my db and it works like a champ. CREATE TRIGGER update_id_on_b AFTER INSERT ON a FOR EACH ROW INSERT INTO b SET a_id = NEW.id; INSERT INTO a VALUES(148,'test 1'); SELECT * FROM b;


1

Your combinations in order of typical performance: 1. > 2. > 4. ( > 3.) 3. is invalid. If rows are only unique per (code, class_id), the lookup by code alone can return multiple rows and is different from the rest. 2. is pointless. If code is unique, there is no point in adding another predicate on class_id - except to verify that a given code ...


0

Note the extra brackets. SQL> variable d varchar2(100); SQL> exec :d := '09-DEC-2014'; PL/SQL procedure successfully completed. SQL> select '''' || (to_date(:d, 'DD-MON-YYYY') + 1) || '''' from dual; ''''||(TO_DATE(:D,'D -------------------- '10-DEC-14' Changing date format: SQL> alter session set nls_date_format='DD-MON-YYYY'; Session ...


6

You really need to look at the definition of sp_spaceused to find your answer of why unused is zero. exec sp_helptext 'sp_spaceused'; go Take a look at a snippet here of the stored procedure: begin /* ** Now calculate the summary data. * Note that LOB Data and Row-overflow Data are counted as Data Pages. */ SELECT ...


1

You're looking for a pivot. Either of these queries will work: SqlFiddle /* case */ select CustomerId , FirstName =max(case when [Key]= 'FirstName' then Value end) , LastName =max(case when [Key]= 'SecondName' then Value end) , Age =max(case when [Key]= 'Age' then Value end) , Gender =max(case when [Key]= 'Gender' ...


0

I always favor PowerShell when it comes to needing OS level information such as file system info or manipulation, and even WMI calls to get hardware info of a server. So this is just my version using PowerShell...Oh and in the event you ask I learned how to do it this way via Laerte Junior's article The PoSh DBA: Solutions using PowerShell and SQL Server. ...


1

In addition to Mark Iannucci's comments, I'd recommend making ArtistID a field in Files, not Albums, to support soundtracks and other multi-artist collections. You may retain an AlbumArtistID in the Albums table, if you think it would be valuable. If you want to be really thorough, you could consider having several artist fields in Files: ComposerID, ...


1

I recommend using unique keys instead of artist or album names. Doing this will improve the database design because currently a change in an artist's name would require updating each entry in your audio_files table for that particular artist. You and I likely agree it would be strange for an artist to change their name, however they have a penchant for ...


0

If you are looking for a running total for each row, you have to set up a user variable. The purpose of the user variable is to keep the total value after each row like a spreadsheet cell. PROPOSED CODE SET @rt = 0; SELECT *,(@rt:=@rt+IF(transtype='D',MemTot,-MemTot)) RunningTotal FROM member_trans; SAMPLE DATA I am using the first ten(10) rows from ...


0

I'm going to have to make some assumptions here. I've done something similar to this in the past so I think I know what you are looking for. Note: I'm using SQL Server 2012 since you did not specify a DBMS but the theory will work for anything. First you have a detail table with a CustomerId and a series of values for that customer. In this case call it ...


0

Would that work? :) It does work in SQL Server 2012 SELECT t.mem_id, (MAX(CASE WHEN t.transtype = 'D' THEN t.MemTot END) - MAX(CASE WHEN t.transtype = 'C' THEN t.MemTot END)) AS Diff FROM ( SELECT 1 AS mem_id, 'D' AS transtype, 1129.80 AS MemTot UNION ALL SELECT 1, 'C', 750.90 UNION ALL SELECT 2, 'D', 568.60 UNION ALL SELECT 2, ...


0

Try www.sqlscripter.com It supports INSERT statements as well as UPDATE etc., so you can do something like this: IF NOT EXISTS (...) INSERT ... ELSE UPDATE ... It works for us so it may work for you.


2

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question. If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy ...


0

Dont kill the SPID's instead try to see you can stop from your end to see. Also the suspend means it is waiting for some resources as in your case it is cxpacket- Firstly the query which is running from the SPID's is normal(consider it is running many days with the same ) or did any changes happened like any logic has changed or any outdated statistics(if ...


4

Restore the backup you have from 15.12.2014 to a new database and then you can delete the rows from your production database that does not exist in your restored backup database. There is no hidden datetime information in the table that you can use.


0

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set. You are trying to capture more than one column and presumably more than one row. MySQL variables can only hold one scalar value. It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult ...


0

This is what worked for me in the end. SELECT [StartTime].[PossibleTime] AS [StartTime] ,[EndTime].[PossibleTime] AS [EndTime] , (SELECT COUNT(1) FROM [Log] AS [L] WHERE [StartTime].[PossibleTime] >= [L].[loginTime] AND [EndTime].[PossibleTime] <= [L].[logoutTime]) AS [UserCount] SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM] FROM ( ...


1

"a boolean column stating whether or not a group has any users" Use EXISTS: CREATE VIEW group_info AS SELECT g.name, NOT EXISTS (SELECT 1 FROM users u WHERE u.group = g.id) AS empty FROM groups g; This returns 1 row per group, no matter whether there are users or not - not one row per user like you had, but probably didn't want - so we don't need ...


0

I was able to figure this out by just using multiple CTE's, then selecting what I needed to combine.


2

Here is a working FOR XML EXPLICIT example. They are a bit harder to code, but I tend to build them up, section by section so they're not so bad: USE tempdb GO SET NOCOUNT ON GO IF OBJECT_ID('dbo.Borrowers') IS NOT NULL DROP TABLE dbo.Borrowers CREATE TABLE dbo.Borrowers ( ID INT IDENTITY PRIMARY KEY, FirstName ...


1

Check for DBCC OPENTRAN for any transaction on these tables. If NOT then you are free to KILL these sessions as these are only select statements. Find the sessions by querying sp_who2 and kill by syntax KILL :-)


1

To add single quotes in a string literal, just double it, or use the chr function. E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote. To get your fixed end time, just concatenate it. select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from ...


2

To get partition elimination, your queries need to be explicit about the bit data type, for example: ...WHERE Archivable = CONVERT(bit, 0) -- NOT ...WHERE Archivable = 0 Without this, the risk of truncation in the implicit conversion means partition elimination is not applied. The implicit conversion in the CREATE PARTITION FUNCTION statement is not a ...


4

If you do 'NewID' as [field/@id] you will get a field element with an id attribute. On the next line you add B.ID as [field] ot get the value of ID as the node value to the field node you created on the line before. <field id="NewID">1</field> After that you want a new field node and to create that you can use a node without a name that has no ...


1

Apparently, creation of CDATA nodes is possible only in the FOR XML EXPLICIT mode. Moreover, Microsoft implementation of XML does not respect them, in accordance with W3C recommendations. Here is a link: How to get [CDATA] with FOR XML PATH? So, if you truly need this, you'll have to write an ugly monster, and don't even think about the TYPE option - it ...


1

Figured it out SELECT temp1.[file], temp1.[ChangeDate] AS [AddedDate] ,temp1.[OldFolder] ,temp1.[NewFolder] ,(SELECT MIN(temp2.[ChangeDate]) FROM [Changes] AS temp2 WHERE temp2.[OldFolder] = temp1.[NewFolder] AND temp2.[ChangeDate] > temp1.[ChangeDate] AND temp2.[file] = temp1.[file] ) AS [RemovedDate] FROM [Changes] AS temp1


0

Here is a non-correlated version that should be more efficient since it isn't doing the lookup per each row: UPDATE t1 SET t1.[temp] = t2.[temp] FROM [myTable] t1 INNER JOIN [myTable] t2 ON t2.[uid] = (t1.[uid] + 1) WHERE t1.[uid] <= 9;


1

You should be able to use SQL Server's proprietary and admittedly awkward self-reference form of delete: DELETE RemoteTable FROM OPENQUERY(...) AS RemoteTable ...


0

Got it !! -- shift column values up one row. UPDATE [myTable] SET [temp] = (SELECT [temp] FROM [myTable] AS s2 WHERE s2.[uid] = ([myTable].[uid] +1)) WHERE [uid] <= 9; This moves just the specific cells up one row, as desired. It was more complicated than I thought it would be, but this seems ...


1

I recommend using a Calendar table or Date Dimension (whichever name you prefer). Here is an answer with using a quick CTE. /* Date Range CTE */ -- Updated based on @AaronBertrand's articles linked in the comments -- Basically ends up being the same query as the last half of @AaronBertrand's post declare @FromDate date; declare @ThruDate date; set ...


1

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft. Instead, you should ...


1

Well, you should have a Numbers table or a Calendar table. I'll start with a Numbers table: CREATE TABLE dbo.Numbers(Number INT PRIMARY KEY CLUSTERED); INSERT dbo.Numbers WITH (TABLOCKX) (Number) SELECT TOP (1000000) Number = ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; Then: DECLARE ...


0

You need to work with your storage vendor to figure out why the writes are being torn. If it's happening to tempdb it's very likely that it's happening to another database as well. This is especially true if it's happened more than once.


2

UPDATE ... SET (a, b, c) = (1, 2, 3) ; Yes, your understanding is correct and this is perfectly legal SQL syntax. As @a_horse_with_no_name mentioned in the comments, you can also use it in conditions (WHERE, HAVING, CASE WHEN, ...). Examples: WHERE (a,b,c) = (1, 2, 3) WHERE (a,b,c) >= (1, 2, 3) WHERE (a,b,c) IN ((1, 2, 3), (1, 1, 1), (2, 2, 2), (4, ...


1

I'd say that this is pretty standard stuff ; Create table employees (id int primary key, name varchar(100) -- Plus some additional columns ) Create table responsibilities (id int primary key, description varchar(100) ...


3

First off, you do not want to use char(50). Use varchar(50) or just text. Read more: Any downsides of using data type “text” for storing strings? Assuming the following rules: Basic slugs never end with a dash. Duplicate slugs are suffixed with a dash and a sequential number (-123). Note that all of the following methods are subject to a race ...


0

Thanks for your input. Actually i found it. Those tables created by one SP which is set to execute on start-up of database engine service and configured with SP_PROCOPTION system stored procedure.


1

I don't think that this completely satisfies your request, but it is a start. If you want further ordering, I think you need to go into more detail with the sorting rules. Also, the example data from your fiddle has "five" with a created date older than "four". SqlFiddle select * from table1 order by coalesce(position,2147483647) , created; ...


0

It may be more efficient to simply use a cascading delete via a foreign key. When a row with the same product_code is deleted from products, it will be deleted in sales_detail as well. ALTER TABLE sales_details ADD FOREIGN KEY fk_product_code(product_code) REFERENCES products(product_code) ON DELETE CASCADE; But if you'd like to use a trigger to delete ...


2

Yes, it looks like you're using Ola Hallengren's backup scripts which operate in hours, so 14 days * 24 hours = 336 hours. Every time this is run, a full backup of the user databases will be taken and any copies older than 2 weeks will be deleted. Edit: Based on the OP's edit, you are fine to schedule a job executing your first script as a replacement ...



Top 50 recent answers are included