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

learn more… | top users | synonyms (2)

2
votes
1answer
3k views

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I ...
1
vote
1answer
11 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
1
vote
2answers
167 views

Which data type should I use to store a 11 digit number [closed]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
-1
votes
2answers
101 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 ...
3
votes
3answers
7k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
0
votes
0answers
14 views

Postgres - Regroup rows with at least one common attribute

I have the following table : Letter | Figure -------|------- A | 1 B | 1 B | 2 C | 2 C | 3 D | 3 E | 4 I would like to group rows that share ...
2
votes
1answer
243 views

Capitalize only the first letter of each word of each sentence in SQL Server

I do want to capitalize only the first letter of each word of each sentence from a SQL column. i.e: if the sentance is 'I like movies' then I need the output 'I Like Movies' Query: ...
3
votes
1answer
242 views

SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows. There are two tables TRNSCTN_TAB: CREATE TABLE TRNSCTN_TAB( `transactionId` int , `UniqueId` int , `transactionTimeStamp` ...
2
votes
1answer
49 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 ...
-3
votes
0answers
24 views

if increase table in a database (20 to 20000) on there database performance is effected or not in sql [on hold]

in that time in my database having only 20 table but now our business model change and there need 2000 table in future. so i cant understand that database performance is effected or not.if effected ...
0
votes
0answers
12 views

Query Across Multiple MySQL Databases

The majority of our clients use the same MySQL schema and a number (subject to the usual conditions) are willing to give us a copy of their data for research. I have written a utility which merges the ...
0
votes
0answers
9 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 (cause I also what to drop the column). In my script I have used ...
0
votes
0answers
54 views

Order by 2 columns with different order behavior [on hold]

I didn't know how to describe the question in the title the best way, but here's my problem, consider this data: ID grade cost 1 1 0.45 2 1 0.3145 3 2 0.054 4 2 0.4165 5 ...
-3
votes
0answers
39 views

What functions should I use to display data like that?

What functions in SQL Server 2012 should I use to achieve the result as shown below?
0
votes
0answers
26 views

How to run a execution plan and how to make use of it [on hold]

I am new to the SQL. Can you please explain about EXECUTION PLAN and EXPLAIN PLAN in TOAD with some examples?
0
votes
0answers
12 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 ...
0
votes
1answer
24 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 ...
0
votes
0answers
18 views

Group st_size by account_id - PostgreSQL 9.2

I've got the following query: CREATE TABLE junk.results AS WITH accounts AS ( SELECT id FROM public.ja_clients WHERE NOT ( (last_sub_pay > EXTRACT('epoch' FROM now() - ...
1
vote
1answer
603 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
4
votes
4answers
315 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
279 views

Select all from table using procedure oracle

I am trying to execute the select command in a procedure.Initially i retrived all of the columns of the table in the procedure using cursor. The real problem comes when I am trying to access the ...
3
votes
1answer
109 views

Export/import data from Excel into a table with queries

I'm a newbie interested in learning and implementing things I learn. I've been working on SQL Server 2014 these days and I'm loving it. I've come across some scenario's where I would have an Excel ...
-3
votes
2answers
41 views

Select in select [on hold]

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!
4
votes
2answers
15k views

SQL query that concatenates values from duplicate rows in a single table

Let's say I have a table of employees, like this: Name Sex Role Bob M Developer Joe M QA Now, I have a problem with duplicated rows in this table. I will fix it ...
0
votes
0answers
13 views

Extract an image from an SQL database using PHP [migrated]

I am trying to extract an image from my database using this code: HTML : ><img src="imagePrevie.php?ID=<?php echo $lastImage2['id'] ; ?>"><?php $lastImage2 return the true value ...
-1
votes
0answers
16 views

oracle join from multiple tables [on hold]

I have to extract data(Table1.Name,Table1.ID,Table2.desc,Table4.desc) from four tables. ->Table1(Name,ID,Group_Id) ->Table2(Group_Id,desc) ->Table3(Group_Id,perm_id) ->Table4(perm_id,desc) In ...
3
votes
2answers
154 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 ...
2
votes
1answer
52 views

Select where value in row is different and update

I have a large table that includes rows like the following: Query: select * from cartographic_text_vm where fid = 'osgb5000005104723440' order by ckpt; Results: -[ RECORD 1 ...
4
votes
2answers
134 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 ...
4
votes
1answer
372 views

How can I prevent having inconsistent data under isolation level serializable in Oracle SQL?

I have the following table: InStores +--------+---------+-----+ | ProdID | StoreID | qty | +--------+---------+-----+ | p1 | s1 | 25 | | p1 | s2 | 70 | +--------+---------+-----+ ...
1
vote
1answer
178 views

No rows in sys.dm_exec_procedure_stats for a particluar stored procedure

I am running DMV queries to get stats for my stored procedure, but I am not finding any rows against this SP under sys.dm_exec_procedure_stats. Please suggest what could be various reason for this ...
-1
votes
0answers
17 views

how can I show all elements [duplicate]

Hey guys I have a probleme at showing all elements informations .. I have a formulaire where i add elements and quantities, then i calculate the total price orderer by the Id. the probleme is that ...
4
votes
1answer
55 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
2answers
20 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 ...
0
votes
0answers
12 views

$ sign showing in fields [on hold]

I am working on my SQL for university, I have managed to save data from PHP to SQL but on retrieving the data there is $ sign in some fields and what I did not understand how come out of 2 same fields ...
1
vote
1answer
204 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows: [ID] [UserID] [Time] (datetime field) [EventType] ("login" or "logout") There ...
2
votes
1answer
108 views

Storing Friendships: Should I use a multivalued column to retain Friend Identifiers?

Say I have a table UserFriendlist that has the columns: Id (set as Primary Key) UserId FriendListIds And let us suppose that it holds the following values: ...
3
votes
2answers
34 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 | ...
1
vote
1answer
43 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 ...
0
votes
1answer
286 views

How to use explode mysql function in where condition with cakephp

I am using cakephp 2.6. I have payment_method fields in mysql table which store the values like 54,52,59,57 . Now my question is that how can i check if new posted payment method id is available in ...
1
vote
1answer
262 views

Get the old version of stored procedure code in sql server

We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored ...
1
vote
1answer
27 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
0answers
63 views

select * from table where column contains “string”?

I'm trying to pull rows of data that contain a word or string from a column. Example: Col1 dog cat mouse cat dog mouse mouse dog rabbit So, I want it to pull * of data that contain the word ...
5
votes
2answers
67 views

Postgres Update Limit

I'm curious if Postgres has anything in place where I could limit the amount of rows kept for a ID. For example, say I have a users table and login_coordinates table. Every time the user logs in, ...
2
votes
1answer
4k views

Extract MySQL information from a compressed tar.gz file

I have a back up-of a MySQL database which is contained in a .tar.gz file - I need to restore it. I've never had to do this before. The only way I can access the database is through phpmyadmin. There ...
0
votes
1answer
111 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
0answers
29 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 ...
0
votes
1answer
42 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 ...
1
vote
2answers
39 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 ...
0
votes
1answer
137 views

How to group records in mysql query

I have a table (user_access) with the following columns in mysql database id int user int access_time datetime I am trying to run a query that gives me the number of times users accesses ...