sql

SQL Unique results

My query is: Table name: employee id(pk) name salary 1 prasad 1000 2 prasad 2000 3 gopi 3000 4 gopi 3500 5 seenu 2500 6 nag 1000 I want get only seenu and nag records. Above table column name is not unique. ...

C# export to excel from sql server

In my C# windows application, I am exporting sql server data to excel on remote drive. But it is too slow. However, if I export data to excel in the local drive, it is fast. How can I increase the time if I want to export data to remote drive? Thanks in advance... ...

Oracle SQL - Column with unix timestamp, need dd-mm-yyyy timestamp

Hi, is there any way in Oracle, to get only the dd-mm-yyyy part from an unix timestamp in oracle? Like: select to_char(my_timestamp, 'ddmmyyyy') as my_new_timestamp from table ...

Sql Query - Selecting rows where user can be both friend and user

Hey Sorry the title is not very clear. This is a follow up to my earlier question where one of the members helped me with a query. I have a following friends Table Friend friend_id - primary key user_id user_id_friend status The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Joh...

MYSQL join, return first matching row only from where join condition using OR

Hello everyone, I'm having a problem with a particular MySQL query. I have table1, and table2, table2 is joined onto table1. Now the problem is that I am joining table2 to table1 with a condition that looks like: SELECT table1.*, table2.* JOIN table2 ON ( table2.table1_id = table1.id AND ( table2.lang = 'fr' ...

How to call sql procedure from an html button?

Hey guys, my doubt is pretty simple to understand. I have a sql procedure that generates a util file as its output depending on the input parameters that are passed to it. I have to pass these parameters from an html page from 3 text boxes.There will also be a button ,on clicking which, the procedure will be called and the util will be...

MySQL, return only rows where there are duplicates among two columns.

I have a table in MySQL of contact information ; first name, last name, address, etc. I would like to run a query on this table that will return only rows with first and last name combinations which appear in the table more than once. I do not want to group the "duplicates" (which may only be duplicates of the first and last name, ...

Need help with SQL table structure transformation

I need to perform update/insert simultaneously changing structure of incoming data. Think about Shops that have defined work time for each day of the week. Hopefully, this might explain better what I'm trying to achieve: worktimeOrigin table: columns: shop_id day val data: shop_id | day | v...

Auto generate varchar Ticket number via DB...

Hello, I'm looking for suggestion on how to get the DB to auto generate Ticket numbers (preferably via the SQL DB) for a varchar column. I have the following tables in the DB: Activities & Cases and would prefer the format to be "Act000001" or "Cse000001". This would be something similar to the identity column property. Any suggestio...

mysql - speedup regex

I have a table: +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | idurl | int(11) | NO | PRI | NULL | auto_increment | | idsite | int(10) unsigned | NO | MUL |...

Sql Query - Limiting query results

I am quite certain we cannot use the LIMIT clause for what I want to do - so wanted to find if there are any other ways we can accomplish this. I have a table which captures which user visited which store. Every time a user visits a store, a row is inserted into this table. Some of the fields are shopping_id (primary key) store_id us...

Tidiest way to filter out rows where all columns = a value

I have a query with loads of columns. I want to select rows where not all the columns are equal to 0. select * from table where not ( column1 = 0 and column2 = 0 and column3 = 0 and ... column45 = 0) Is this really the tidiest way to do it? Supposing I then need to change it to ignore when all columns are 1, or negative.. It...

SQL Joins on varchar fields timing out

Hi, I have a join which deletes rows that match another table but the joining fields have to be a large varchar (250 chars). I know this isn't ideal but I can't think of a better way. Here's my query: DELETE P FROM dbo.FeedPhotos AS P INNER JOIN dbo.ListingPhotos AS P1 ON P.photo = P1.feedImage INNER JOIN dbo.Listings AS L ON P.account...

Get percentiles of data-set with group by month

Hello, I have a SQL table with a whole load of records that look like this: | Date | Score | + -----------+-------+ | 01/01/2010 | 4 | | 02/01/2010 | 6 | | 03/01/2010 | 10 | ... | 16/03/2010 | 2 | I'm plotting this on a chart, so I get a nice line across the graph indicating score-over-time. Lovely. Now, what...

How can I know if an SQLexception was thrown because of foreign key violation?

I want to tell the user that a record was not deleted because it has child data, but how can I be sure that the exception was thrown because of a foreign key violation? I see that there a sqlexception class that is used for all sql exception. ...

Table Design For SystemSettings, Best Model

Someone suggested moving a table full of settings, where each column is a setting name(or type) and the rows are the customers & their respective settings for each setting. ID | IsAdmin | ImagePath ------------------------------ 12 | 1          | \path\to\images 34 | 0          | \path\to\images The downside to this is every time we wa...

Teradata equivalent of persisted computed column (in SQL Server)

We have a few tables with persisted computed columns in SQL Server. Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations? The particular computed columns I am looking at conform some account numbers by removing leading zeros - an index is also created on this conformed account number...

Semi-complex aggregate select statement confusion

Alright, this problem is a little complicated, so bear with me. I have a table full of data. One of the table columns is an EntryDate. There can be multiple entries per day. However, I want to select all rows that are the latest entry on their respective days, and I want to select all the columns of said table. One of the columns is a...

Comparing 2 columns in the same table with the "Like" function

I'm trying to come up with a way to query the values in two different columns in the same table where the result set will indicate instances where the value of columnB doesn't contain the value of columnA. For example, my "Nodes" table contains columns "NodeName" and "DNS". The values should look similar to the following: NodeName D...

How to escape simple SQL queries in C# for SqlServer

I use an API that expects a SQL string. I take a user input, escape it and pass it along to the API. The user input is quiet simple. It asks for column values. Like so: string name = userInput.Value; Then I construct a SQL query: string sql = string.Format("SELECT * FROM SOME_TABLE WHERE Name = '{0}'", nam...