Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

1
vote
1answer
126 views

Deleting duplicate rows with condition [duplicate]

I have posted a similar question before, but this one is a little modified. I have a table with 4 columns as shown below. ITEMCHARGE CPT4 EFF_DATE CPT4MOD 326.8 29075 ...
0
votes
1answer
344 views

Oracle SQL Statement alter index and add partition

I have an index in which I have to remove one column and reindex back for rebuild: ALTER INDEX <index_name> REBUILD; Is it possible to add partition when I rebuild an index. Partition will ...
0
votes
1answer
49 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( s1_....
1
vote
2answers
440 views

Deleting duplicate rows with condition(Using SQL and LINQ)

I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. Deletion Condition: When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, ...
1
vote
1answer
656 views

How to Select the First (or last) entry for a specific day

I have a table "AttendanceRecords" with entries like this EntryID UUID PRIMARY KEY, StafID UUID REFERENCES staffmembers(ID) NOT NULL, ArrivalTime TIMESTAMP NOT NULL, DepartureTime TIMESTAMP For one ...
0
votes
1answer
45 views

How can I aggregate multiple results from another table using something like BOOL_AND?

I'm using PostgreSQL as my database. CREATE TABLE users ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE groups ( id ...
0
votes
2answers
1k views

insert inserted id to another table

Here's the scenario: create table a ( id serial primary key, val text ); create table b ( id serial primary key, a_id integer references a(id) ); create rule a_inserted as on insert to a do ...
6
votes
2answers
552 views

Why doesn't a delete increase unused space in a table?

If I populate the following table: IF OBJECT_ID ('dbo.CleanTableTest', 'U') IS NOT NULL DROP TABLE dbo.CleanTableTest; GO CREATE TABLE dbo.CleanTableTest (FileName nvarchar(4000), ...
10
votes
2answers
15k views

Outer Apply vs Left Join Performance

I am Using SQL SERVER 2008 R2 I just came across APPLY in SQL and loved how it solves query problems for so many cases, Many of the tables I was using 2 left join to get the result, I was able to ...
0
votes
1answer
241 views

Turning multiple fields in column to row

I have an issue that has really stumped me in SQL Server. I have recently had the need to insert a row in to a table, made up from a number of columns. I want to make the one row up from something ...
2
votes
4answers
492 views

Help understanding SQL Server 2005 execution plan

One of my stored procedure has long execution time (average around 4 to 7 minutes). Now I trying to tweak it an make it run faster. I am looking at execution plan and two things I see that using ...
7
votes
1answer
133 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
16
votes
1answer
5k views

How to get the MAX row

In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability. ...
4
votes
1answer
15k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
-2
votes
4answers
4k views

Is there a SELECT statement to subtract rows of the same column?

Column 1 is the mem_id, Column 2 is the Transaction type C and D, and Column 3 is the Amount Owed. So I want to return Column 3 "Amount Owed", but it has to be C-D in Column 2 for each member. thanks, ...
1
vote
0answers
117 views

PostgreSQL query slow when using tsvector and ORDER BY despite having index

Running on a large database (around 1.5G) The following query takes about 70 seconds to run, SELECT x FROM DBText x WHERE x.status = 2 AND x.publishedTime < 4574590406397 AND x.service = '...
2
votes
1answer
1k views

Get products of category plus all child categories till leaf nodes

We have a hierarchy which looks this: Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level. I am trying to get ALL products below a ...
0
votes
2answers
863 views

Suspended query causing high CPU usage

Environment detail: SQL Server 2008 R2. I have applications running on JBoss that access the DB using JDBC driver. Issue: My online DB server was experiencing high CPU usage. Using the ...
0
votes
1answer
110 views

How to delete row from table later than date?

I have a simple table: MyTable: -Id int primary key -Name varchar(MAX) -Deleted bit Now i want delete rows created later that 15.12.2014. But how can you see i don't have a any date columns in my ...
0
votes
1answer
593 views

How to find out who is active and locking the oracle database?

At work, we are sharing oracle credentials among many developers. Often time team runs heavy adhoc queries on the database and it is causing lot of trouble to oracle server. We are working to fix ...
3
votes
1answer
365 views

Recursive CTE to find unique slug

I have an article table where I want the slug to be unique. CREATE TABLE article ( title char(50) NOT NULL, slug char(50) NOT NULL ); When the user enters a title e.g. News on Apple, I want ...
2
votes
1answer
2k views

storing query results in a variable in mysql and using it in another query

I have a following query which runs perfectly fine: SELECT count(*) as total,l.state as state FROM db1.table1 cd inner join db2.table2 l ON ...
0
votes
1answer
295 views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...
0
votes
0answers
95 views

Query plan reads vs statistics io reads

I have a very strange problem. When I run a query with SET STATISTICS IO ON, it shows that I had a total of 183 reads. But when I look at the query plan dmv it shows I did 555,132 reads. I cleared the ...
1
vote
0answers
64 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
3
votes
3answers
4k views

SQL Server dates - can I get date and hour?

In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?
2
votes
0answers
43 views

Can I convert SQL Server 2008 full db to SQL Server 2005?

Recently I converted my SQL Server 2005 database to SQL Server 2008. Now I can't attach that db to SQL Server 2005. I do not have any backup of that db file as well. I have tried to generate scripts ...
2
votes
1answer
230 views

SELECT query with DISTINCT on a table-structure for graphs is very slow

I have 2 tables where nodes contains about 60m rows and edges about 500m rows (both growing fast). These two tables represent a directed graph. CREATE TABLE edges ( node_from bigint NOT NULL, ...
0
votes
1answer
168 views

Need help in putting the single quote and constant timestamp in plsql

Please help me in appending the Single quotes and constant timestamp to my query output. Given below is the expected output from my param file. Note: Query is executed through sqlplus Expected ...
0
votes
1answer
41 views

join on just the next closest date?

I want to get a table of [file], [folder], [DateAdded], [DateRemoved] I have a log table of changes that has [file], [ChangeDate], [OldFolder], [NewFolder] Files have moved in and out of ...
0
votes
0answers
219 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
0
votes
1answer
642 views

Select Count on Big table take 15 minutes

I try to run select Count on table with like 16M rows, with MySql 5.5 and workbench 6.2. The query running almost 1000sec . Is it normal? The query : SELECT COUNT(1) FROM My_Table when I run ...
0
votes
1answer
431 views

DELETE from OPENQUERY alias not working

I'm trying to use the following code to execute a DELETE statement on a linked server. I'm trying to delete all records from the remote server where there is a match on a certain ID field in both the ...
1
vote
1answer
201 views

SQL Server Chain Permission

We have the 2 databases. Reporting HR There are some users who want to extract data from HR database. But we don't want to give direct access to that HR database. So, we created the special ...
1
vote
2answers
1k views

Filling in date holes in grouped-by-date SQL data

The actual problem involves far more data and joins, but I've created a small sample to demonstrate the issue: -- create example table DROP TABLE dbo.EventRecords GO CREATE TABLE dbo.EventRecords ...
0
votes
1answer
65 views

can I use many-to-many relation with weak entity

I have an entity, for example employee(key employee ID), and every employee can have multiple responsibilities. Every responsibility can be tasked to multiple employees. I have 3 options: 1) Create ...
3
votes
1answer
102 views

Suspect (Torn) Pages recorded in TEMPDB? (SQL Server 2005 SP3)

I have a virtualised SQL Server 2005 (SP3) server that reported the following msg: Database_Name, Database_Id, File_Id, Event_Type, Event_Type_Desc, Error_Count, Last_Update_Date tempdb, 2, 1, 3, ...
4
votes
1answer
57 views

the <set clause>'s <multiple column assignment>

I just know a little sql basics and need to write a plain sql syntax highlighter/checker So I'm fighting through the standard... I stumbled over somthing I'm not sure I understand it right, as I never ...
0
votes
3answers
253 views

Prevent auto commit of DROP without SET AUTOCOMMIT OFF

I dropped my table and I lost my data and I can't roll it back because DROP statement automatically commits. How can I prevent auto commit action of DROP without using SET AUTOCOMMIT OFF?
0
votes
0answers
2k views

How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

I have three fields in a table named table_family. One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son. Other field is "age" and it contains the ages of all the ...
-1
votes
1answer
31 views

Sorting a table based on nullable Position field that has to match the position on the result set

I'm using SQL Server 2012 I have a simple table that has a Nullable (int) Position and a (datetime) Created field. I would like to do a select * from this table order by created desc but ...if a ...
0
votes
1answer
4k views

Trigger syntax on phpmyadmin

I can't make this work. I want to create a trigger that deletes the child foreign keys before I delete the parent primary key. This is the code that I am using on phpmyadmin: delimiter // CREATE ...
1
vote
0answers
186 views

I can't divide 2 count(*) values without returning zero

The statement I'm using is for a KPI in Maximo and I currently have this statement: select (select count(*) from incident where incident.slanum = 'USERADMIN' and incident.actualfinish > incident....
1
vote
1answer
179 views

Delete back ups more than 2 weeks in sql server

I am not a DBA, I need to delete the USER_DATABASE backups older than 2 weeks. Is this below stmt work for this scenario? EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASE', @Directory = 'C:\...
1
vote
1answer
70 views

Create a log file for check which time import has been done and to split the import files

How to create a log table for when does the last import has been done and also for Query for split the records in to 1000 while importing. to avoid the bulk insert For example : If the data ...
2
votes
2answers
44 views

Get all (non repeated) distribuidores with their paises given a producto_solicitud

I have this schema: And as title says I need to get all records from fabricante_distribuidor by given producto_solicitud.id value. I'm doing in this way: SELECT nomencladores....
4
votes
1answer
2k views

Alter table and update in sql server transactions

I'm trying to do an alter table to add a new column on a table, then I would like to do some update on the existing rows in the table to add a value to the recently new added column and finally do a ...
-1
votes
3answers
444 views

Should tables with very large records quantities be split into multiple tables

I'm working on an application (asp.net) that deals with a very high record count -- let's estimate about 75 million events per year coming in fairly evenly throughout the year, and each of these ...
0
votes
1answer
76 views

GROUP BY Returning Different Totals [closed]

I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query ...
0
votes
1answer
98 views

Change execution plan in Oracle

I have a large table called Table1 and another table Table2. A query is scehduled every day which inserts data into Table2 by selecting rows from Table2: INSERT INTO Table2(Cola,Colb,Colc..)SELECT ...