New answers tagged

1

While there's still no way to do anything about the select all columns but one bit, but you can use json_agg(to_json(b.col_1, b.col_2, b.col_3 ...)) to get a json array of jsons each in the format {"col_1":"col_1 value", ...}. So the query would look something like: SELECT a.id, a.name, json_agg(to_json(b.col_1,b.col_2,b.col_3...)) as item FROM a ...


0

My CLR SQL Server Aggregate Function (Spread) EDIT: I aded my Split function to make working with the results easier. These might not be the best example. What it does is lists the unique values comma separated and can give you the occurrence count or percentage, Considering this table Use these queries to get the corresponding results. ...


6

declare @a varchar(30) set @a = 'qWeRtY kEyBoArD TEST<>&''"X' select @a as [Normal text], upper(@a) as [Uppercase text], lower(@a) as [Lowercase text], ( select ' '+T2.X.value('concat(upper-case(substring(. , 1, 1)), lower-case(substring(. , 2)))', 'varchar(30)') from ...


0

If there is a failure, I want to 'ROLLBACK' the transaction so nothing is inserted That happens automatically if any exception is raised inside a function (by you or by Postgres). A function is always atomic and can only be run within a transaction context - which is the main distinction from real stored procedures (currently implemented in Postgres). ...


5

Instead of doing an expensive CROSS JOIN plus aggregating a potentially large number of rows you can use this approach: SUM the days including the duplicates and subtract the number of rows with duplicate dates. ;with cte as ( select *, row_number() over (partition by ToolId order by StartDate, EndDate) as rn from ...


0

You have three cursors here, but one is implicit in the for loop. Your code indicates that for each row in the testnirmal table you want a column name from the table and then a row from the testnirmal table that has a literal value of that column name. It is unclear what you are trying to accomplish, but here is one interpretation: CREATE OR REPLACE ...


5

You can avoid the expensive recursive CTE by using a date table. These are easy to construct and very useful. I used a variation of the code here to generate one. Note: I'm using a temp table for the date here. This is just for the demo code. The permanent solution should use a permanent date table. It should probably also include some/all of the ...


2

I've used Openrowset a number of times for this task. This code will create a table in SQL. SELECT * INTO EXCEL_IMPORT FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1', 'SELECT * FROM [Sheet1$]'); Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. ...


6

I think this will get you what you want. It creates a recursive CTE (common table expression) that generates all the dates between January 1 and March 1. It then joins those dates to the tool rental data, checking to see if each date record falls between the rental dates. This gives you one record per toolid for every day within the range of rental ...


-3

try this SELECT ToolId,SUM(RentalDays) As RentalDaysSum FROM #tmpToolRentalDays GROUP BY ToolId , Cast(StartDate as DATE)


0

SELECT name FROM world WHERE gdp > ( SELECT Max(gdp) FROM world WHERE continent = 'Europe' )


0

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed. SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *, [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) ...


-2

Unsure why you'd be using ALL rather than SUM. I'd be inclined to reword as follows select name from world where gdp > sum(case when continent='Europe' then gdp else 0 end)


0

You could try : select distinct parentId, substring(( select ','+cast(childId as varchar) from MyTable t1 with(nolock) where t1.parentId = t2.parentId for xml path ('') ),2, 1000) as childId from MyTable t2


4

You can use a pattern like this: SELECT files.database_id, db.name AS DatabaseName, STUFF((SELECT ', ' + names.name FROM sys.master_files names WHERE names.database_id = files.database_id FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)') , 1, 2, '') AS NameList FROM sys.master_files files ...


0

update cartographic_text_vm set primary_key = new.primary_key from (select * from cartographic_text_vm where (fid, ckpt) in (select fid, min(ckpt) from cartographic_text_vm group by fid) ) new where cartographic_text_vm.fid = new.fid and cartographic_text_vm.primary_key != new.primary_key;


1

Two remarks: This is called an associative table, not pivot. You already do a join, you just use the old syntax: SELECT * FROM tbl_profile_follow A JOIN tbl_profile_follow B ON (B.profile_id = A.profile_id AND B.followed_id = A.followed_id) AND (B.profile_id = A.followed_id AND B.followed_id = A.profile_id) This is another way ...


2

The query can be made easier: select least(profile_id,followed_id), greatest(profile_id, followed_id), count(*) from tbl_profile_follow group by least(profile_id,followed_id), greatest(profile_id, followed_id) having count(*) > 1; Using this approach you can also create a unique index on the table create unique index idx_unique_pair on ...


4

Basically it's a crosstab query: PostgreSQL Crosstab Query The dynamic result type is a problem, though. ... is it possible to create a dynamic query which will output the right thing regardless of the number of runs? No. Currently (including Postgres 9.6) not possible with a single SELECT statement. Not unless you know the return type at call time ...


4

Since you need a subquery in either case, I would use a plain aggregate in the subquery (may be cheaper): SELECT count(*) FROM (SELECT race_id, max(rating100) AS rating100 FROM horse_main GROUP BY 1) x JOIN horse_main h USING (race_id, rating100) WHERE h.race_result = 1; If there are many rows per race_id, it will be faster to get ...


4

Your query is quite close. In addition to the max rating100 per group, the derived table should also return the individual rating100 values, so that you can check if the row's rating matches its group's maximum. SELECT COUNT(*) FROM ( SELECT horse_name, race_id, race_result, rating100, max(rating100) OVER (partition by ...


1

Your [sub_tree] derived table is using an ORDER BY with no TOP operator. Try something like the following: SELECT TOP (1) node.name [...] ORDER BY node.lft ) AS sub_tree Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather ...


2

Perhaps a big operation that was rolled back. If you really want to know, you can inspect the log and see exactly how the growth occurred. See How to read and interpret the SQL Server log. Simply pick one current unused page and then find the transaction that allocated it to the table. From the context of that transaction you will be able to tell exactly ...


5

System procedure sp_spaceused tells you how the data is distributed, and is usually more than accurate enough. Run the following simple TSQL: DECLARE @R TABLE (TabName sysname, [Row Count] bigint, Reserved varchar(128), Data varchar(128), [Idx Size] varchar(128), Unused varchar(128)) INSERT INTO @R exec sp_spaceused 'tContactHistory' SELECT TabName , ...


1

Did I understand this correctly that your customer has MSSQL Server? Source Database MSSQL or other You have two options: Script your own replication Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration. Source Database Oracle Additionally to the options mentioned in "Source Database MSSQL ...


0

May be you can try this: CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER) INSERT INTO #tempFileTable EXEC xp_dirtree 'D:\Test1', 1, 1 SELECT TOP 1 Fname FROM #tempFileTable; DECLARE @DBName varchar(255) = 'Test' select 'RESTORE DATABASE ' + @DBName + ' FROM DISK = D:\Test1\' + fname + ' WITH FILE = 1' from ...


0

Maybe you could try something like : declare @file varchar(50) declare @DBName varchar(50) declare @sql varchar(200) CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER) INSERT INTO #tempFileTable EXEC xp_dirtree 'D:\', 1, 1 select @file = FName from #tempFileTable set @DBName = 'MyDB' set @sql = 'RESTORE DATABASE '+@DBName+' ...


0

Using an index consisting of the columns display_name, id, active and is_company would enable the query to be run using the index alone. Ordering the columns in the order provided would remove the sort requirement. This comes at a slightly update cost, and could replace the ix_displayname index. If active were rare, it would be faster to order the columns ...


0

I figured out a way to do this in excel using a very small macro, then I just re-imported the data.


3

You can calculate a RANK/ROW_NUMBER based on this logic: ROW_NUMBER() OVER (PARTITION BY IMMS_ID ORDER BY CASE WHEN status='DI' THEN 1 WHEN status='OI' THEN 2 WHEN status='FI' THEN 3 WHEN status='OV' THEN 4 WHEN status='FV' THEN 5 END)


7

What if one needs to query to find '1234 ' but not '1234' If you are using Unicode data, you can simply use LIKE. From the documentation: When all arguments (match_expression, pattern, and escape_character, if present) are ASCII character data types, ASCII pattern matching is performed. If any one of the arguments are of Unicode data type, all ...


0

Check if your PDB is in restricted mode by entering show pdbs. If under Restricted it says YES, then you have to just shut down the instance and start it up again. Here's how: SQL> shutdown immediate Pluggable Database closed. SQL> startup Pluggable Database opened. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ...


3

This is not possible: the display settings can not be specified per column. If you set the font to one which definitely supports relevant Unicode code points, you can use these characters instead of relying on a legacy symbols font, while still having normal characters available. For example "Arial Unicode MS" supports CHECK MARK (U+2713) and HEAVY BALLOT ...


-2

There is a performance difference. The newer JOIN syntax *ansi-92 will outperform older ansi-89 syntax, at the very least on SQL Server and MySQL. I can't speak directly to Oracle.


4

You can use the following Stored Procedure, scheduled via a SQL Server Agent job, to archive records on a continual basis. You don't need to worry about the initial move of "everything older than 6 months" as it will be taken care of naturally by this process since it will be able to archive records faster than you are inserting them. Of course, if you want ...


2

Two approaches that I have seen used. Approach 1 Create an identical table to audlog, say audlog_new. Adjust Identity seed if necessary to be greater than the largest ID on your current audlog table. Rename your existing table to something else (audlog_old) and then rename audlog_new to audlog. Now you have all the time in the word to process your old ...


3

You can use a recursive CTE to fetch Id at parent' levels: DECLARE @Id int = 4; WITH cte(level, Id, ParentId) AS( SELECT 0, Id, ParentId FROM @data WHERE id = @Id UNION ALL SELECT c.level+1, d.Id, d.ParentId FROM cte c INNER JOIN @data d ON c.ParentId = d.Id ) SELECT Id, Level FROM cte ; Output With @Id = 4, This query returns the path ...


5

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way. Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query ...


2

The idea I wrote in the comment http://sqlfiddle.com/#!9/785bc/5 : select a.userid, date(a.CHECKTIME), timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as diff from CHECKINOUT a join CHECKINOUT b on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid where a.CHECKTYPE = 'I' AND b.CHECKTYPE = 'O' group by date(a.CHECKTIME), ...


1

This is generally developer's choice but in my opinion restaurant_tables implies one-to-many relationship. When a table resolves many-to-many relationship I prefer names like restaurants_X_dishes. Again, this is up to my taste.


2

I think the complexity of performing this task is small and does not change with size. The same SQL that will find a missing recipient from 100 users will also find a single missing recipient from 100M users. The time to do so, however, is likely to be linear in the number of posts and the number of users. I can see two ways to organize the data. One is a ...


0

Without knowledge of your schema, query attempted and statistics from explain analyze, any response can only deal in generics. In this sense and in terms of SQL, there are generally two commonly used strategies for dealing with finding missed relatinons: NOT EXISTS and LEFT JOIN x WHERE x IS NULL. NOT EXISTS: SELECT * users WHERE NOT EXISTS (SELECT 1 FROM ...


4

An alternative to srutzky's answer to the "what if" portion of the question: What if one needs to query to find '1234 ' but not '1234' Just make sure the last characters of what you're comparing aren't white-space. SELECT * FROM ( SELECT CONVERT(VARCHAR(50),'1234 ') AS TestVal UNION ALL SELECT ...


1

YES, it is incorrect. The inner FETCH NEXT should go at the END of the loop. The code above would result in the following: Hello, I am # 2 Hello, I am # 3 Hello, I am # 4 Hello, I am # 5 Hello, I am # 5 Correcting the problem, and putting the FETCH NEXT at the end of the loop, results in the correct & expected output: Hello, I am # 1 Hello, I am # ...


1

I'm assuming you are using SQL Server! you can try this: select( LEFT( (RIGHT(7654321, len(7654321) - 3)), len((RIGHT(7654321, len(7654321) - 3)) )- 3) ) First I removed 3 digits from left(7654321 --> 4321 ) then removed 3 digits from right(4321 --> 4). if you feel this method difficult you can try this: select ...


1

You don't say what RDBMS you are using. If SQL Server you could do SELECT (7654321/1000)%10 To divide by 1000 with integer division to get 7654 and then calculate modulo 10. Something similar should be possible in all DBMSs though the exact syntax may vary. E.g potentially something like SELECT MOD(TRUNC(7654321/1000),10) FROM DUAL;


0

you can use something like... SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1 ...as a subquery (substitute name of your id field for "id") and wrap it up like this... SELECT b.* FROM btmt b, (SELECT id, Count(fsid) As cnt FROM btmt GROUP BY 1) As z WHERE b.id = z.id AND z.cnt > 1 LIMIT 24; Hard to tell exactly what you're looking for, but that ...


2

Do I understand it correctly that you have a production database with 1mil++ rows in some tables and you want to reduce the amount of rows in your test/development database? This is pretty tricky and there is no feature which does this "in a few clicks". First of all you should analyze the relations between the tables. If you have 100 tables with a small ...


2

To see open transactions which may be locking rows, execute: SELECT * FROM sys.sysprocesses WHERE open_tran = 1 Issuing a COMMIT or ROLLBACK for those transactions would address your issue. If you don't mind dirty reads (depends on your business rule for your particular situation) you can SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED And this ...


2

Sure you can, you can append the SAMPLE clause to your selects: For example: select * from table sample(20); sample_clause The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table. BLOCK BLOCK instructs the database to attempt to perform random block ...



Top 50 recent answers are included