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
23 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
13 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 ...
0
votes
0answers
40 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 ...
1
vote
1answer
29 views

Wingdings font in SSMS's column

I do want to create a column which should has check marks and cross marks. Here is the part of my query: iif(order_details.order_status = 'Y',char(252),char(251)) as [completed?] After I execute ...
0
votes
1answer
44 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
140 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
51 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
2answers
82 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
65 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
30 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
40 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
34 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
49 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 ...
0
votes
1answer
34 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
33 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
19 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
52 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
30 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
27 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
37 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
106 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 ...
-2
votes
0answers
40 views

#1292 - Incorrect date value: '0000-00-00' [closed]

I refereed many question which having the title same as mine but has different approach and different issue so this question is not duplicate. I have a table in which column fm_sctrdate is date type ...
1
vote
1answer
62 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
141 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 ...
0
votes
2answers
26 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 ...
3
votes
3answers
63 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
51 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
45 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 | ...
2
votes
2answers
51 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
20 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
33 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
102 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
24 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
43 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 ...
0
votes
1answer
51 views

Problems with queries [closed]

I have problems with queries in MySQL. Problem 1: Query: SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA="mysql" and TABLE_NAME="servers" MySQL says: #1054 - Unknown column 'mysql' ...
1
vote
0answers
12 views

Efficiently get records with latest timestamp per group [duplicate]

I have a table which roughly looks like this uuid uuid, id id, data text, last_updated timestamp Each row has an associated unique uuid. However, the key of interest is the id (indexed). ...
0
votes
2answers
32 views

text positioning for select list

I have following issue, I have hierarchy table where every record has level, code and description and I need to have description to look as it is was one column. Example code CREATE TABLE #tmp ...
1
vote
2answers
61 views

Get the Text of the currently running SQL?

This is something I am just interested in knowing if it is possible. Could I write an SQL query that could return the text of itself as a variable? Where I might use this would be on things like ...
4
votes
2answers
72 views

Select tables on column entry in schema (with many tables)

I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I ...
3
votes
2answers
75 views

Enforce uniqueness across optional attributes for an entity

I have an entity that has multiple attributes, and these attributes are themselves entities. No two entities may have the same attributes, so there needs to be a uniqueness constraint across them. ...
1
vote
0answers
18 views

Database Design for Sports Stats

I am designing tables for tracking statistics for multiple sports like basketball and football. So my question now is - would it make more sense to design a table when both data for basketball and ...
0
votes
1answer
30 views

How to, efficiently, search through many sql dump files of really big size

We have an ads website that the uncompressed(plain .sql file) size of the database is about 30gb. Until now when a user was deleting his classified the classified was completely getting wiped out from ...
1
vote
1answer
23 views

How to handle id's referring to multiple tables in an SQL database

I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in ...
11
votes
2answers
297 views

How to write a query in SQL Server to find nearest values

Let's say that I have the following integer values in a table 32 11 15 123 55 54 23 43 44 44 56 23 OK, the list can go on; it doesn't matter. Now I want to query this table and I want to return a ...
0
votes
2answers
35 views

Prevent duplicate record counts in one to many query

I'm trying to get a count of ethnicity for a teachers students. My first table contains the student number and ethnicity. The second table contains teacher id's, student numbers, course numbers, and ...
0
votes
1answer
46 views

SQL Continuous Table Updating: Best Practices?

I have a highly "vertical" database, and I've written a dynamic pivot script to build a new time-indexed table that I can actually query on human time scales. Pivoting the table takes a few minutes ...
0
votes
0answers
43 views

automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS

I have an ETL process that is entirely written using T-SQL scripts. The script grabs an Excel spreadsheet and imports it using openrowset. Once I get the transformed data I copy with headers and paste ...
1
vote
0answers
27 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
2
votes
1answer
42 views

Prevent duplicate entries

I have log in form with Username and Password. $con=mysqli_connect(LOGINDETAILS); /*if (!$con) { die("Can not connect: " . mysql_error()); } */ $Username = $_POST['Username']; $pswd = ...
2
votes
1answer
47 views

REGEXP does not show the right result?

I have in the name column: 3 boxes of frogs 7 boxes of frogs 48 boxes of frogs When I execute: SELECT name FROM items WHERE name REGEXP "[^1-5] boxes" ...the result is: 7 boxes of frogs 48 ...