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

learn more… | top users | synonyms (2)

1
vote
2answers
41 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
1answer
49 views

Raise Multiple Exceptions Postgres

I am using Postgres 9.5, and I am trying to create exception handling in a stored procedure/function. I can't quite figure out how to raise the error should any of the 'INSERT' statements fail. If ...
5
votes
4answers
343 views

Summing data based on distinct dates

I am trying to get an accurate count of total days a tool is out for rental. Here is a data sample: CREATE Table #tmpToolRentalDays ( ToolId BIGINT, StartDate DATETIME, ...
0
votes
2answers
126 views

Retrieve data from two tables where table 2 has multiple entries for one value

I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures: Table 1 | Table 2 ------------------+--------------------------...
0
votes
1answer
47 views

Searching a text with space in mySql match against Query

I want to search a text in mysql DB with [space] (Ascii 32) as a part of it. But I am not getting the correct result. My MySql Query is: SELECT count(id) as total , MIN(product_price) as ...
-3
votes
2answers
50 views

Select in select [closed]

I am doing some test on the internet as preparation for my test tomorrow. I would like to know what I am doing wrong here. Thanks a lot!
3
votes
2answers
175 views

select distinct one column with all instances of another column in a second column [duplicate]

If I have a table with the following information: ParentID | ChildID ---------+-------- 6 | 6 6 | 7 6 | 8 6 | 9 6 | 10 I need to ...
0
votes
2answers
22 views

Unique pair checking query improving in pivot table

First of all, I have a solution, but I do not know that query can be transformed to uses join or to more effective query. I just want to improve my skill. I have seen an example online. So I have a ...
4
votes
1answer
68 views

Is it possible to create a dynamic join?

I'm using PostgreSQL if it matters. Assuming these two tables: id | run ----+------- 1 | run_1 2 | run_2 3 | run_3 and sample | elapsed | run -------+---------+------- samp1 | 1:09 |...
0
votes
1answer
61 views

Building MySQL InnoDB clustered indexes

Assuming that you have a table like CREATE TABLE `t1` ( `search_id` int(11) NOT NULL, `provider_id` bigint(20) unsigned NOT NULL, `status` text NOT NULL, `imported_at` int(11) NOT ...
1
vote
1answer
51 views

MySQL syntax for order by not accepted in SQL Server

I'm working through the SQL snippets for nested sets in this article by Mike Hillyer. The article is using MySQL, but I'm using SQL Server. I get the following error reported when attempting to ...
3
votes
2answers
42 views

Count rows with max per group and additional condition

I have the following table: horse_main_id | race_id | horse_name | rating100 | race_result ---------------+---------+-------------------+-----------+------------- 23 | 159 | ...
5
votes
2answers
147 views

Why a SQL Server Table takes 1GB more after adding 55MB of data?

I've found that one customer database size exploded between yesterday and today. After investigation I've found a design problem in a table schema but after doing some math I know that 55MB of data ...
1
vote
2answers
41 views

Read TSQL variable from Temp Table

I am hacking around trying to write a script to restore a backup from a known folder location. The location will only ever contain one backup file. I have come up with this so far, but I don't know ...
1
vote
1answer
24 views

Ranking based off conditional field

I am looking to select only 1 row based off the IMMS_ID field. Which row I select is based off this if statement (excuse the syntax) if (status group based off IMMS_ID) status='DI' select the DI row ...
0
votes
0answers
14 views

What is the 'auth_string' in 'GRANT' 'ON' SQL command in MySQL/MariaDB?

In the typical command to grant privileges: GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername"@"hostname" IDENTIFIED BY "password"; What is the "password" part? The documentation says it'...
0
votes
1answer
54 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
0
votes
1answer
57 views

How to do data migration between different schemas?

My company is in the process of migrating a customer's data from their third party system into our own system. We currently use a java application that we have developed in house that selects data ...
3
votes
2answers
165 views

Copy large table to archive table SQL Server 2012

I have a table with logs of my application that have 161.626.718 rows (and growing) and it's very slow to make a insert on it. My idea is to keep only 6 months of log in the table and all older ...
2
votes
1answer
60 views

Calculate path to node in a tree

The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the ...
0
votes
1answer
88 views

What is difference between JOIN and simple query

I have two tables in database one is Orders and another one is customers Orders Table Contains : OrderID , CustomerID and OrderDate Customers Table Contains: CustomerID , CustomerName , ContactName ...
0
votes
1answer
66 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
1
vote
2answers
36 views

Scalable way to track who's viewed a post? [closed]

I'm building an app that needs to check who, out of millions of users, has not received a post. If a post hasn't been seen by many people, this task will be easy. But if a post has been seen by nearly ...
2
votes
1answer
47 views

RedGate SQL Prompt - Cursor Snippet mistake

Using RedGate's SQL Prompt & loving it. But I used the "curff" snippet for the first time today ("Fast-forward read-only cursor"), and it produced the following TSQL (which I've filled in to make ...
0
votes
2answers
42 views

sql - Pulling specific digits from place values

I'm wondering if it's possible to pull the digits at specific place values in a number with SQL. For example, the number 7654321 is stored in the database. I want to pull out the number at the one ...
2
votes
1answer
60 views

Bulk Insert into view and find missing values

I have 2 tables, Categories, and SubCategories: CREATE TABLE [dbo].[tbCategories] ( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [CategoryName] [varchar](250) NULL, ) CREATE TABLE [dbo].[...
0
votes
1answer
35 views

Row of database is unreacheble

Here is the problem: I have a table called "flags" with 54 columns: PVKEY PEOPLEKEY ADDRESSKEY FLAGTYPE FLG1 FLG2 FLG3 FLG4 FLG5 FLG6 FLG7 FLG8 FLG9 FLG10 FLG11 ...
0
votes
1answer
36 views

Get A Count From Table

I want to see all fsid limit 25 from my table -- I tried this query Select * from btmt WHERE Count(fsid) > 1 limit 24 But this throws an error of Aggregates are not allowed in WHERE ...
1
vote
0answers
22 views

Views dependent on other views [duplicate]

Is it bad practice to have views dependent on other views? If one dependency doesn't affect performance, at what level of dependency will it start showing negative effects? In the picture is an ...
0
votes
2answers
55 views

Is is posible to sample a database

Is it possible to sample any SQL database? By sampling I means the database table should remain and retaining the foreign key constraints. But the actual rows from the table should be reduced.If I ...
1
vote
1answer
33 views

Reset schema oracle

I'm running systemtest that updates a oracle schema. I need to reset the whole scheme between runs. Is there an easy way to do this in oracle 12. I have looked at flashback but it doesn't seem to be ...
0
votes
0answers
34 views

Error: 18456, Severity: 14, State: 11. Login-based server access validation failed with an infrastructure error

We are getting the above error on a SQL 2012 server running Windows Server 2008R2 when connecting using an application and a service account. The server also has Config Manager database and GAP policy ...
1
vote
1answer
59 views

SSIS: To run the package outside of SQL Server Data Tools you must install expression task of integration services or higher

Me and my colleague using the same version of .Net framework (4.5) and installed SSDT in visual studio 2013. He prepared a package (not so complex) and can able to execute the same from SSDT without ...
0
votes
3answers
108 views

Best name for link table [closed]

I was wondering what the best name for a linked table in this situation (or any) would be. I've searched for this, but could not find any concrete examples. For example, I have the following tables (...
1
vote
1answer
80 views

Hoursworked/day computation via Bio Logs

This is my checkinout table: USERID CHECKTIME CHECKTYPE ------ ------------------- --------- 404 2016-02-12 07:24:03 I 404 2016-02-12 17:01:34 O 404 2016-02-15 07:24:21 I ...
4
votes
4answers
159 views

Weird WHERE Clause Behavior. Why does this return a row?

In SQL Server 2008, why does this return the row, even when I add a whitespace, or two, or more, to the end of the where clause? shouldn't zero records be found in the following example? WITH SRC AS (...
-1
votes
2answers
32 views

Tryng to use and or queries

In MS Access, I have a table with six columns. A name and (let's say pets) so the columns are marked cat, dog, fish, rabbit and hamster. I want to do a query that I can check cat and it will show all ...
4
votes
3answers
119 views

How to format a phone number in a SELECT statement?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if ...
1
vote
1answer
103 views

SQL Server specific file group

I have a database with three file groups, Primary, FG_ExtendedTables, and FG_ReportingTables. I need to create another database (production db let's say), and restore only specific file groups and ...
0
votes
3answers
49 views

select two record containing dates in it with two input dates

Simply I have Two Input box Start_date End_date This is my database table | id | category | start_date | end_date | | 1 | gold | 2016-05-24 | 2016-05-31 | | 2 | silver | 2016-05-...
2
votes
2answers
64 views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set ...
1
vote
0answers
29 views

Select where table and column name are from another table

I know I need dynamic sql, but haven't gotten this to work: I want to loop through a table and for each row, return the value of the field that is located by two columns for that row. Master ...
1
vote
1answer
34 views

Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I have query in which returns averaged values by site and month. What I want to know is the min and max of those monthly averages by site and (the difficult part) the month when each occurred. Here ...
-1
votes
1answer
124 views

Comma Separated values in sql server [closed]

I have three tables with relationship. TableDetail ID(PK), TableNo BookingDet ID(PK), BookID(FK BookingMstr.BookID), TblID(FK TableDetail.ID) BookingMstr BookID(PK), Date I want to get table ...
0
votes
0answers
25 views

improve performance with schema design, a lot join

I'm making a custom cms system for my client, with postgresql, nodejs ... have problem with slow performance issue, when doing select data . (and it is still on localhost/mac nginx and just few data, ...
0
votes
1answer
59 views

Open .mdf and .ldf files in Microsoft SQL Server without attach [closed]

I want to open .mdf and .ldf files in Microsoft SQL Server without attach. Are the following steps correct? Add new database with MYDBNAME name. Copy the files MYDBNAME.mdf / MYDBNAME_log.ldf to ...