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

learn more… | top users | synonyms (2)

0
votes
0answers
18 views

Shrink Database in SQL Server [duplicate]

I have 700 GB database and I want to shrink it. What are the risks of this operation and my data will be deleted during this operation ?
4
votes
1answer
64 views

NOT IN (together with join) query

I have two tables : Part, OrderLine: Part (PartNum PK, PartDesc, Price) OrderLine (OrderNum PK, PartNum PK, QtyOrdered) And I want to find out the parts that were not ordered. I think I have a ...
2
votes
1answer
72 views

fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

As i am from mathematics background and require to do some huge permutations to start in my work kind help. If i have a table by name sample1 with n columns what is fastest way to get all the ...
0
votes
2answers
49 views

SQL server 2012 service pack installation

which service pack I should apply first after installing sql server 2012 enterprise edition either sp1 or sp2 ? Thanks in advance.
3
votes
0answers
28 views

Understanding row estimation for timestamp in postgresql

PostgreSQL 9.4 I have a table (call it tbl) that's got the column registration_date timestamp. Now, for simplicity I set the following statistic parameter: ALTER TABLE tbl ALTER COLUMN ...
2
votes
1answer
35 views

help in correcting the syntax error of the below create table with as with multiple select

Please help to correct the syntax of the below. I am from a math background trying to use Oracle. The command helps me permute the columns and put them in one table. create table newsample AS ( ...
7
votes
2answers
83 views

DBCC CHECKIDENT ('table name', RESEED, 20000) not working

When I run the script DBCC CHECKIDENT ('table name', RESEED, 20000) the output of the message is Checking identity information: current identity value '200000', current column value ...
8
votes
3answers
142 views

How to avoid a cyclic dependency (circular reference) between 3 tables?

I have 3 tables: People Post Likes When I design the ER model it has a cyclic dependency: 1:N People --------< Post 1:N Post ----------< Likes 1:N People ...
2
votes
1answer
53 views

Inner Joins between Subsets of the Same Table

All, I am trying to figure out the syntax for doing joins between subsets of the same table. I have: Employee ( EmpId PK , EmpFirst, EmpLast, EmpMid, DateHired, SSN, DateBirth, Gender, PhoneNum, ...
0
votes
1answer
34 views

Why the result is ranked by both amount and date instead of amount only? [duplicate]

Declare @ranking_term char set @ranking_term ='Amount' select Dense_rank() over (order by case(@ranking_term) when 'Amount' then 1 else 0 end, amount, case(@ranking_term) when ...
-4
votes
1answer
45 views

Why all ranking is 1 for this query [closed]

Declare @ranking_term char set @ranking_term ='Amount' select case (@ranking_term)when'Amount' then ( select Dense_rank() over (order by Amount desc) ) else( select ...
3
votes
2answers
73 views

PostgreSQL requires “GROUP BY” when aliasing a table?

Here's something strange: there is a large table named "products" which is partitioned with inheritance such that there are two children: products_active and products_inactive, and the constraint is ...
3
votes
1answer
73 views

Select another row if NULL

In a MySQL database I have these tables: Table 1: News news_id int (PK) category int (FK) rating tinyint Table 2: News Detail news_detail_id int (PK) news_id int (FK) language_code char ...
6
votes
2answers
62 views

generate_series for multiple record types in postgresql

I have two tables that I want to query: pest_counts and pests which look like: pests id | name ---+-------------- 1 | Thrip 2 | Fungus Gnats pest_counts id | pest_id | date | count ...
0
votes
1answer
33 views

trimming leading characters

I'm in oracle and I have a table 'parts' in which have a field 'Description', which contains description for a part. my query is: SELECT DESCRIPTION FROM PART WHERE PAR_ID=123 AND DESCRIPTION ...
0
votes
1answer
72 views

How to optimise multiple joins with OR?

I need to extract some statistical information form drupal database tables. The problem with drupal is that it splits it's data for entities across multiple tables and getting simple information from ...
-1
votes
2answers
52 views

Can we do a Join on SQL tables on M:N relationship?

hope this is not too dumb/simple. I have been practicing joins recently and I have never seen a join done between tables T1, T2 that are in an M:N (Many-Many) relationship. Instead, joins are made ...
0
votes
0answers
14 views

T-SQL script to analyse recent changes in SQL Server configuration?

Last week suddenly one of our VMs running SQL Server slowed down considerably and its performance dropped to the level where some of us cannot work on it any more. Just to give you an idea about the ...
5
votes
2answers
66 views

Same record shows up multiple times in ordered paginated query

Some records show up on multiple result "pages" with the following query: SELECT `description`, `wallpaper` FROM `bbr_bar` WHERE `country_id` = '2510769' ORDER BY `online` DESC ...
1
vote
1answer
60 views

How can i improve my count(distinct id) query in mysql laravel 4.2

I am using three join and fetching more than 40lacs of records for now it is 15 lacs . Below is my query, can you please take a look? select count(distinct enrollments.userid) as aggregate from ...
0
votes
0answers
66 views

Find all possible pairwise 2 rows such that there intersection is only one element I have also a general question help on the same

Kind help to find the intersection of all pairs of rows of table and display only those pairs whose intersection is only at one element. Here I have a table say with 4 varchar elements for a sample ...
0
votes
2answers
33 views

Avoid user login Directly to AlwaysOn High Availability Primary

I have setup an AlwaysOn High Availability in Sql Server 2014. my IP addresses are according to this : 192.168.1.189 -- Listener 192.168.1.180 -- Replica 192.168.1.181 -- Primary Right now users ...
1
vote
1answer
33 views

Strange upper() behaviour for dateranges in PostgreSQL

I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query: select upper(('[2005-12-01,2005-12-04]')::daterange) It returns 2005-12-05 instead of 2005-12-04. The ...
6
votes
2answers
677 views

Is ELSE 0 implied in my COUNT CASE WHEN statement?

What's the difference between COUNT(CASE WHEN [Column A] = ____ THEN 1 END and COUNT(CASE WHEN [Column A] = ____ THEN 1 ELSE 0 END? I've been using the former and I haven't seen the difference thus ...
3
votes
1answer
24 views

SQL Server Changing logical name

I have some db's created with an incorrect logical name for both rows and log. how can I change the logical names to match the db name with a script? (and does not want to change physical file names) ...
0
votes
0answers
28 views

Updating a table which contains an Image datatype column

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF. I have a requirement to update one ...
0
votes
1answer
48 views

SQL Dependent Percentage

Sample data Excepted output Math Operation Sum all Period time for each EmpName as Total Employee Time Sum Similar Tasks For EmpName, divide it by Total Employee Time and multiply by 100% ...
4
votes
1answer
33 views

Update foreign key from csv file

I am trying to set up a simple database for the first time using MySQL Workbench. I'd like to update the foreign keys of the child table based on the values of parent_child.csv. Here are the current ...
1
vote
0answers
37 views

SQL Server licensing in a virtualised environment [closed]

My company has 6 instances of SQL Server 2012 Standard. These instances all run on VMware virtual machines. The licenses for SQL Server came packaged with WonderWare products (WonderWare Historian for ...
2
votes
1answer
31 views

Unresolvable Ambiguity problem on Join Tables

I have searched for all answers, but nothing works. Somehow this query gives me the ambiguity error or 'ID': SELECT wpp.ID AS theid, s.ID AS supplierid, post_title, wp_terms.name AS category FROM ...
1
vote
3answers
87 views

why in explain in mysql row count is very high

i am using mysql, here is query- mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59'; +----------+ | count(*) | ...
3
votes
1answer
87 views

SQL Server: How to add all dates of the month?

I need help. I am trying to add all dates for the month. I have this information: Month | Year ============= 1 | 2012 2 | 2012 3 | 2012 4 | 2012 I need to add all dates of the month ...
2
votes
1answer
47 views

Adding foreign key that references a column which is not Primary or Unique key

I got into trouble when I want to limit values of Table A's column to be taken only from a set of optional values in Table B's relevant column. Problem is table B's relevant column isn't a foreign ...
1
vote
0answers
69 views

Improve lookup of 1.5 million rows

I have this table with data but we need to get all the (unique) rdx and rdy values: CREATE TABLE public.positions ( datetime timestamp without time zone NOT NULL, unitid bigint NOT NULL, rdx ...
0
votes
1answer
76 views

MySQLSyntaxErrorException: You have an error in your SQL syntax;

This is the issue I am trying to fix since hours, almost a day. I am unable to get an understanding of what might be wrong. I have a SQL table notification I am trying to persist a java object ...
-8
votes
1answer
76 views

FULL OUTER JOIN much faster than UNION ALL in SSMS, but slower in Excel [closed]

I am trying to optimise a view pulled from SQL Server 2008 to Excel, and I've managed to drastically improve the performance on SSMS. Using FULL OUTER JOIN instead of UNION ALL, I've managed to ...
1
vote
2answers
45 views

Sql tables design

I have a table with two columns: Brand(varchar) | optional_os_type(varchar) for example: +--------------+ | Lenovo | 1 | | Dell | 2 | +--------------+ Whereas 1 = win8.1 2 = win10 Currently ...
1
vote
3answers
68 views

How to create a non-sa sql server login / access without control server permission

I have an ms sql server, and I need to give access to other users to run basic queries (just select on 1 database). I have created a login for a user, and have amended their permissions, but once I ...
4
votes
1answer
114 views

Hash Join vs Hash Semi Join

PostgreSQL 9.2 I'm trying to understand the difference between Hash Semi Join and just Hash Join. Here're two queries: I EXPLAIN ANALYZE SELECT * FROM orders WHERE customerid IN (SELECT customerid ...
1
vote
2answers
104 views

Are these database indexes correct for my search

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/ I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows ...
1
vote
0answers
59 views

SQL query causes server gone away

I'm developing a database system which stores and caluclates engine performance data. I have been created a new view which selects data from several tables and calculates some values. Now I'm running ...
0
votes
2answers
60 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
0
votes
1answer
40 views

Update row contents on some condition

For example, let's say I'm building a database for an auction system, in which various users can partake in both selling and bidding on different items, I'd want for an "Auction" instance to also ...
1
vote
1answer
48 views

Oracle: using “as of” clause with table aliases?

I can run this flashback query with no problem: select x from a as of timestamp sysdate; But if I use a table alias I get an error. select foo.x from a foo as of timestamp sysdate; ORA-00933: SQL ...
0
votes
0answers
31 views

Display Complete Month With or Without TIMESTAMP field

Please edit the title if you have a better, I didn't find other words to describe this. Environment Fields: |id|time|text| Situation I have to make a report with the total of records per day, ...
1
vote
1answer
39 views

Restoring an Oracle database from files

I am running into a problem now, i have an oracle database that is stored inside a server and now because of the sudden blackout the c drive is messed up. The drive is partitioned, windows on c and ...
2
votes
1answer
62 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
0
votes
3answers
72 views

How to update one column with the value of another if one of their columns match. SQL

I've found a lot of similar questions, but not exactly what I need.I want to make a SQL command to find all duplicate email rows and update the notes of them with 'do not email' if it is written in ...
2
votes
1answer
98 views

SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0

I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job. Now it fails intermittently with the ...
1
vote
1answer
67 views

How to get max value of union query?

My database schema is as follows Department (dept_id, dept_name) Student (student_id, student_name, major, level, age) Professor (prof_id, prof_name, dept_id) Class (name, meets_at, ...