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

learn more… | top users | synonyms (2)

2
votes
3answers
8k views

How to select the first 10 records for each table in the same database using sql

I have 50+ tables in a database. I don't want to use the following SQL on each table in a database. select * from {table_name} So, is it possible to display the first 10 records for each table ...
3
votes
2answers
2k views

What is the difference between using varchar(100) and varchar(255)?

I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.
1
vote
2answers
234 views

Suggestion for storing a lot of simple data

I'm looking to crawl a lot of webpages (500,000,000,000) records and be able to store the linking structure for a later date. The way I planned on laying out the database was as follows: 2 Tables ...
3
votes
1answer
68 views

Special ordering

I have a hierarchical query but I don't manage to order it as I want. I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly. This a ...
7
votes
2answers
1k views

Is there any difference between putting a column alias at the start or the end of the column definition?

I've always seen and written my column aliases as SELECT 1 as ColumnName but today came across a query that used SELECT ColumnName = 1 Is there any difference in how these two queries get ...
0
votes
1answer
526 views

Connecting to an SQL Azure Database from SSMS - IP Restriction

I am trying to connect to a new SQL Azure Database via SQL Server Management Studio. The client box running SSMS is hosted on a production DB so we restrict access on 1433 to just a handful of IPs ...
5
votes
3answers
164 views

Filtering data that could have more than one category

I have a table like the following: Annotation ( document, term, category ) Where document and term are some ID, while category is an integer. The couple document - term is not unique, ...
3
votes
2answers
2k views

Problem with order by in MySQL subquery

I'm running a query that runs in about 2 seconds when the subquery does not include the order by date_sold clause. However, when I add the order by clause, the query runs forever. I'm confused, since ...
0
votes
2answers
94 views

How to organize a single query to several sql servers?

I need to make an inner join sql query to several SQL Servers (one is 2000 and another is 2008 R2). The query is like the following: SELECT k.ID, k.NAME, k.BIRTH, l.PROFILEID FROM ...
0
votes
1answer
77 views

Faster joining on 'most recent' related rows

I have a bug tracking application. The bug table stores very little, because every state change to a bug happens by adding a comment. For example, the current state of a bug is determined by the state ...
11
votes
2answers
3k views

Select longest continuous sequence

I am trying to construct a query in PostgreSQL 9.0 that gets the longest sequence of continuous rows for a specifc field. Consider the following table: lap_id (Serial), lap_no (int), car_type ...
2
votes
1answer
1k views

How and what is the most efficient way to join two tables, retaining a particular field from both?

I have two tables, with the following columns: Table 1 ID Component Data1 Data2 Table 2 ID Component Data3 Data4 If I have rows in these tables, with items Component1, Component2, Component3, ...
3
votes
1answer
79 views

Locating row in index page

In clustered index, we have root, intermediate and leaf level pages. Every page has few records where indicating range of pages from level below. How SQL check whether some index key belong to ...
2
votes
2answers
155 views

Query to find Maximum friends not giving correct resuts [closed]

Table Schema for the Likes table: id id1 1 2 1 3 2 1 I count the total number of Likes for each student with this: select id,count(*) from friends group by id I have to find the students with ...
1
vote
1answer
255 views

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is ...
0
votes
0answers
544 views

Understanding the time difference between data grid initial display and export/full display using Toad for Oracle

I am trying to run a query on a local Oracle DB and output the results to a .CSV file. The query runs in a decent amount of time (usually less than 5 minutes), but returns tens of thousands (possibly ...
1
vote
1answer
3k views

Strange behaviour of a query with order by and rownum on Oracle's Exadata server

I am executing the following query on two different database servers: SELECT start_date, end_date, resort_id FROM employee_activity WHERE employee_id = 27 AND start_date < ...
4
votes
1answer
962 views

Query records that chain together based on date gaps

I have a table in SQL Server 2012 with the below simplified schema and I need to define a query that grabs the most recent record and then returns the chain of records that have less than 30 days of a ...
5
votes
3answers
33k views

Selecting data from multiple rows into a single row

I have a table that has columns like following. Batch number is a database design thing like row number demand_id batch_number debit status customer 34 1 ...
0
votes
2answers
3k views

Does MYSQL join before LIMIT?

Imagine that I want to populate the last 10 questions of stackoverflow. I have a table that keep the questions list, a table to keep the question info, a table to list question's asker. Now imagine ...
4
votes
2answers
1k views

How to sort category by path and sort order?

first of all I am sory for my english. My category structure is shown below. My purpose is to get category structure with single query. I sort my table by path and I get category tree. sort_order : ...
0
votes
1answer
47 views

Need Some SQL help [closed]

I need to pass the following SQL statement: UPDATE table_products SET shipping = "a:5:{s:16:"min_items_in_box"; i:0;s:16:"max_items_in_box"; ...
2
votes
2answers
2k views

how does SQL Server 2008 handle joins internally?

for example, I have tables: User and Order User Table: UserId, Name, Address, Phone, Age Order Table: OrderId, UserId, Date, Item, Quantity SQL: select User.Name, Order.Date From Order JOIN User ...
1
vote
1answer
66 views

Correct data model for users with clients

I have an application where users are granted access. Part of the system allows users to have their own 'clients'. I have a users table ******************************** users ...
1
vote
1answer
224 views

Multiple primary keys

I am new to database design and have been looking at some example data models here. One thing I don't understand is if you have a table like client_addresses from the link above: ...
1
vote
3answers
129 views

Live monitoring SQL statements on SQL Server

How can I monitor running SQL Statements for my SQL Server, is it possible using only SQL Server without 3rd party tools? As an alternative can anybody recommend any free tool which can monitor ...
1
vote
1answer
935 views

Query is not using indexes on third table in left join

My query is not using indexes on third table(pci promotion_coupon_images) . i was tried with using index and force index in query but there is no change in result. any body please suggest me how ...
8
votes
3answers
3k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
3
votes
2answers
13k views

ORDER BY clause is allowed over column that is not in SELECT list?

In theory, every statement in SQL Server create virtual table passed to next statement. In other words, when FROM and Where clause are finished, next step is SELECt columns from virtual group created ...
0
votes
1answer
376 views

Create a VIEW that spans 3 tables

Sorry if the title is not clear - feel free to improve. Here are 3 tables (not all data is relevant to the question): CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
1answer
50 views

simple query is not using indexes

My query is not using indexes. it was taking around 5 seconds. output is 500 rows only. it is a simple query Please find the explain plan and table structure here. Explain Plan mysql> explain ...
4
votes
2answers
919 views

Restore SQL Server database from .sql files

So we had an encrypted database that puked and killed our whole SQL Server setup. Sucks about our data, but we were smart enough to have our data structures / stored procedures / functions in Git The ...
2
votes
1answer
146 views

index doesn't work properly

I have this query SELECT user_id, MAX( highscore ) AS highscore, stage, COUNT( * ) AS count FROM single GROUP BY user_id, stage table engine is innodb, plus I haven't use any indexes except one key ...
0
votes
1answer
469 views

Postgresql: Return Unique Rows

I'm using postgresql and I have a large database. A simplified version of the datastructure for my table looks like this: id (primary unique key), data, userID 1, foo, 4 2, foobar, 4 3, lala, 5 4, ...
2
votes
2answers
661 views

Query design for tracking order status

I have the following tables in MySQL `orders` ( `orderID` int(10) unsigned NOT NULL auto_increment, `agentID` int(10) unsigned NOT NULL, `order_date` datetime NOT NULL, `senderID` int(10) ...
1
vote
1answer
317 views

MySQL query to search string in Master-Child tables and return all rows with Master-Child relation

Need MySQL query Help. Please find schema below: Table1 PK_id Table1_Title Table1_Description 1 Test1 This is Test1 2 Test2 This is Test2 Table2 FK_id ...
1
vote
2answers
3k views

Oracle insert max from two date

I want to create an insert statement in a PL/SQL Script that inserts the larger of two different values. Conceptually, what I want to do is something like Insert into MYTABLE (DATE_INSERT) VALUE ...
0
votes
0answers
145 views

Performance Optimising MySQL query

I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5) I was wondering if there is any way to enhance this query so ...
1
vote
1answer
233 views

Mysql query - find out 1000th row from a table for each user

Lets say I have a table where we log activities of user, I need to find out created_at of the 1000th activity of each user.. Following query works fine for one user.. select `created_at` from ...
0
votes
1answer
2k views

Does TRUNCATE TABLE affects isolation level in Sql Server?

As I am working on the project which has SQL SERVER as a database....I'm maintaining isolation level in my project where I enabled snapshot isolation level for my project.... As I have to make some ...
1
vote
1answer
138 views

Does a transaction cover the subroutines?

When I begin transaction and exec a stored procedure, does that transaction cover the changes made by the stored procedure I executed? Would a rollback transaction cancel the changes made by the ...
5
votes
1answer
427 views

Logical reads amount difference with almost identical indexes

I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server. The mentioned query is quite simple, ...
1
vote
3answers
119 views

Getting the exact edited data from a column in SQL Server

I have two Tables: Articles(artID, artContents, artPublishDate, artCategoryID, publisherID). ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, ...
1
vote
1answer
2k views

Installing Full Text Search on SQL Server 2008 R2 Express

I can't for the life of me install full text search on my SQL Server 2008 R2 instance. Every installer I have tried shows me this: I've tried: SQLEXPRADV_x64_ENU.exe SQLEXPRWT_x64_ENU.exe ...
0
votes
1answer
219 views

Changing data type from DATE to CHAR(10) with a query

I want to alter the date type in a database that has 40 tables from DATE type to CHAR(10) type, without opening the tables and manipulating them in Management Studio. i.e. only with a script/query. I ...
0
votes
1answer
566 views

Permission Denied Copy [closed]

I am a beginner learning how to load data and I can't figure out why I can't load this data into postgres. I am using postgres 8.4 on MAC OS. copy tname from '/Users/Shared/folder/file.cxv' ...
2
votes
1answer
97 views

Synchronize Queries Retrieving Sessions And Locks From DMVs

I have a SQL script that I run when I want to see what is happening in the database. The script has many queries that return information from DMVs, but the two I use most are "Requests" ...
4
votes
2answers
4k views

SQL as a Storage Definition Language (SDL)

In my Databases lecture, the teacher mentioned that SQL is not just a data definition language (DDL), data manipulation language (DML) and view definition language (VDL), but also a storage definition ...
4
votes
1answer
4k views

Indexing strategy when using the between operator SQL Server 2008

I have a large table ~25 million rows with the structure CREATE TABLE [dbo].[rx]( [pat_id] [int] NOT NULL, [fill_Date] [date] NOT NULL, [script_End_Date] AS ...
0
votes
1answer
134 views

Query taking long time

My query is taking around 540 seconds to get result. Please help me to rewrite it. Query: SELECT pd.domain, fd.passkeyid FROM portfolio.domains AS pd, fabulousdomains.domains AS fd WHERE ...