sql

SQL Loader script help with adding SYSDATE, USER

I'm trying to load data from a file and I want to set CREATED_DATE and UPDATED_DATE to SYSDATE and CREATE_BY and UPDATED_BY to USER Here the table that I'm working with: CREATE TABLE CATALOG (CNO NUMBER, CTITLE VARCHAR2(25), CREATED_BY VARCHAR2(10) NOT NULL, CREATED_DATE DATE NOT NULL, UPDATED_BY VARCHAR2(10) NOT NULL, UPDATED_DATE DAT...

SQL Server SSMS: how to get row numbers for a batch

In SSMS, how can I get SSMS to show row numbers for a batch? I can get row numbers for a query window, but that does not help when an error occurs in batch statement. This feature would help me a lot to find errors from my code. ...

Optimize query with two "in" clauses

I've trying to optimize a query on MySQL that takes around 15-20 seconds to run. My Data table has about 10M rows, and the query is trying to return 68,000 records which match 144 "run" fields and 35 "name" fields. Because the query is using two in clauses, my indexes don't seem to be terribly helpful. Here's the query: select * from...

How to count similar interests in MySQL

I have 2 tables, 'interests' and 'users_interests'. 'users_interests' just has userid and interestid fields. 'interests just has an id and a name. I simply need to find userid's who have more than 3 interest ID's in common. I have been told that a Self Join is involved, but I cannot seem to get this to work. Someone said something ...

Check if a list of other strings is %LIKE% another string

Is there a short way to check if a list of strings is %LIKE% a given string? For example: book animal a Would all satisfy this condition for "A Book about Animals"? I know that I could write out Title Like '%book%' AND '%animal%' LIKE Title, etc. but that seems unwieldy. I'm using this for an autocomplete, where I return results whe...

SQL ROW_NUMBER() over performance problem

I have this SQL that works fine. Want the my filter to return the LATEST unique SessionGuids with the highest UserSessionSequenceID. Problem is performance sucks - even though I have good indexes. How can I rewrite this - to omit the ROW_NUMBER line? SELECT TOP(@resultCount) * FROM ( SELECT [UserSessionSequenceID] ...

Create field with default as epoch time

I need to create a timestamp field for a table who's rows need to expire after a certain amount of time. If I use the following: `timestamp` TIMESTAMP DEFAULT NOW(), It shows the time in a human readable format, it would be a lot easier if I could have it in epoch time so I can calculate with seconds. Is there a way I can create a fie...

help with SQL query?

Hi Guys, I am not sure if I should do this in the code or do it in the query, but I will ask it here as I am interested in a sql solution for myself. Say if I have the following table, and I need to get all the rows whose ParentSID is 1. But if any of these rows return have Null as FID then I also need to go fetch all rows whose Parent...

Records with extra properties: sparse table or EAV?

I have a model that already has a couple dozen of columns that will be filled most of the time. Now I need to add fields that might be different each time. what's the best approach? I don't like the EAV pattern. I don't like the idea of having a sparse table either, especially considering how these extra properties could be very differe...

Store geocode info from google maps into mysql

I am pulling an address from a database and plotting it in a map. However, if the marker is dragged to a new location, I need the NEW geocode at "dragend" to be stored in the database. any ideas on the simplest way to accomplish this? ...

MySQL query to return rows that are equal to or greater than a certain date, where the date is separated in year, month and day columns

A MySQL table EMPLOYEE has columns (beginyear, beginmonth, beginday, empid) all of type int. What's a correct query to return all rows that are equal to or greater than the date 2009/8/13? That's year, month, day. A query such as this is incorrect because it wouldn't return rows that contained dates such as 2009/9/1 (filtered out by b...

displaying sql data as 1 match

I hope I can explain this clearly: I have two tables with the following structures: TABLE A{id, source, url} TABLE A2{matchid,matchsource,matchurl,country,lang} and I have a join on them: TABLE B{id,source,url,matchid,matchsource,matchurl,country,lang} Table B is a join of A with A2 where for each record in A there are matching rec...

Difference between performance of the two sql queries?

I have a field in my table having text data type. Is there a difference in performance for the following two sql queries: select * from tablename where fieldname="xyz%"; select * from tablename where fieldname="%zyx"; If we were to implement the execution of these queries, this is what I think we would need to do: We have to match...

SQL GROUP BY and NULL values: How can I group my results by only non-null values?

My query: SELECT *, contacts.createdAt AS contactcreatedAt, contacts.updatedAt AS contactupdatedAt, bidresponses.itemid AS bidresponseitemid, bidresponses.personid AS bidresponsepersonid, SUM(tagsitems.quantity) AS totalquantity FROM items LEFT OUTER JOIN tagsitems ON items.id = tagsitems.itemid LEFT OUTER JOIN items...

SQL LEFT JOIN with COUNT(*) problem

I have the following query: SELECT products_categories.categoryID, name, COUNT(*) AS itemCount FROM products_categories LEFT JOIN products_to_categories ON products_to_categories.categoryID = products_categories.categoryID GROUP BY products_categories.categoryID But still there's a problem: categories with no products in them return i...

SQL many to many select

category_product --------------- id_category id_product product --------------- id_product id_manufacturer manufacturer --------------- id_manufacturer name How would I create an SQL query so that it selects all the names from manufacturer when id_category is equal to something? ...

sql query problem

what is wrong with this query? each one of them works separately but they're not working after i write the select * select * from (SELECT COUNT(issuer_id) AS INSU_cnt, min_desc AS INSURANCE FROM issuer INNER JOIN code ON economy_sect = cod_id WHERE (min_desc = 't') GROUP BY min_desc) UNION (SELECT COUNT(issuer_id) ...

Mysql: Count records (including zero) per month

Hi, I am trying to count the records in my table and group them per date. My current query looks something like the following: SELECT count(*), MONTH(time) as month, YEAR(time) as year FROM myTable GROUP BY month, year ORDER BY year, month This works, except that I would also like to get a count for months where n...

How to retrieve a column after doing an UPDATE command in php and mysql

I'm new to php and sql. I have a table with three columns. One 256 bit hash number and two ints. I want to search for the row that matches my hash and then retrieve one int and increment the other. So, I thought I'd kill two birds with one stone by using first the UPDATE command. $query = sprintf("UPDATE %s SET activationcount = (activ...

Getting last and next db-record by date in MySQL-Table?

Hey guys, I've got a certain question related to a blog, which I am developing in OOP (PHP) right now. All blogposts are stored in a MySQL-table. In the app you can read a specific post by giving the id of the post via GET-parameter. So like http://example.com/?id=2. Under the blogpost I want to show to navigation links like "previous"...