Search Results

Search found 32551 results on 1303 pages for 'sql authentication'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 722/1303 | < Previous Page | 718 719 720 721 722 723 724 725 726 727 728 729  | Next Page >

  • Union Distinct rows but order them by number of occurrences in mysql

    - by Baversjo
    Hi I have the following query: SELECT o.id,o.name FROM object o WHERE ( o.description LIKE '%Black%' OR o.name LIKE '%Black%' ) UNION ALL SELECT o2.id,o2.name FROM object o2 WHERE ( o2.description LIKE '%iPhone%' OR o2.name LIKE '%iPhone%' ) Which procude the following: id name 2 New Black iPhone 1 New White iPhone 2 New Black iPhone I would like to UNION DISTINCT, but I would also like the result ordered by the number of occurrences of each identical row (primary: id).

    Read the article

  • Round date to 10 minutes interval

    - by Peter Lang
    I have a DATE column that I want to round to the next-lower 10 minute interval in a query (see example below). I managed to do it by truncating the seconds and then subtracting the last digit of minutes. WITH test_data AS ( SELECT TO_DATE('2010-01-01 10:00:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:05:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:09:59', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2010-01-01 10:10:00', 'YYYY-MM-DD HH24:MI:SS') d FROM dual UNION SELECT TO_DATE('2099-01-01 10:00:33', 'YYYY-MM-DD HH24:MI:SS') d FROM dual ) -- #end of test-data SELECT d, TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data And here is the result: 01.01.2010 10:00:00    01.01.2010 10:00:00 01.01.2010 10:05:00    01.01.2010 10:00:00 01.01.2010 10:09:59    01.01.2010 10:00:00 01.01.2010 10:10:00    01.01.2010 10:10:00 01.01.2099 10:00:33    01.01.2099 10:00:00 Works as expected, but is there a better way? EDIT: I was curious about performance, so I did the following test with 500.000 rows and (not really) random dates. I am going to add the results as comments to the provided solutions. DECLARE t TIMESTAMP := SYSTIMESTAMP; BEGIN FOR i IN ( WITH test_data AS ( SELECT SYSDATE + ROWNUM / 5000 d FROM dual CONNECT BY ROWNUM <= 500000 ) SELECT TRUNC(d, 'MI') - MOD(TO_CHAR(d, 'MI'), 10) / (24 * 60) FROM test_data ) LOOP NULL; END LOOP; dbms_output.put_line( SYSTIMESTAMP - t ); END; This approach took 03.24 s.

    Read the article

  • Get the first and last posts in a thread

    - by Grampa
    I am trying to code a forum website and I want to display a list of threads. Each thread should be accompanied by info about the first post (the "head" of the thread) as well as the last. My current database structure is the following: threads table: id - int, PK, not NULL, auto-increment name - varchar(255) posts table: id - int, PK, not NULL, auto-increment thread_id - FK for threads The tables have other fields as well, but they are not relevant for the query. I am interested in querying threads and somehow JOINing with posts so that I obtain both the first and last post for each thread in a single query (with no subqueries). So far I am able to do it using multiple queries, and I have defined the first post as being: SELECT * FROM threads t LEFT JOIN posts p ON t.id = p.thread_id ORDER BY p.id LIMIT 0, 1 The last post is pretty much the same except for ORDER BY id DESC. Now, I could select multiple threads with their first or last posts, by doing: SELECT * FROM threads t LEFT JOIN posts p ON t.id = p.thread_id ORDER BY p.id GROUP BY t.id But of course I can't get both at once, since I would need to sort both ASC and DESC at the same time. What is the solution here? Is it even possible to use a single query? Is there any way I could change the structure of my tables to facilitate this? If this is not doable, then what tips could you give me to improve the query performance in this particular situation?

    Read the article

  • SQL query INSERT not working inserting values into my DB.

    - by Aiden Ryan
    Hello, I'm trying to insert registration data into a database but my php code isn't inserting the values into the DB although I'm not getting any errors either, can someone help me? this is the code i'm currently using: $connect = mysql_connect("localhost","myusername","mypassword"); mysql_select_db("application"); $queryreg = mysql_query('INSERT INTO users("username","password","email","date") VALUES("$username","$password","$email","$date")'); die ("You Have Been Registered."); I just need to add the username password email and date into the fields i have specified but it won't work, please someone help!

    Read the article

  • How to write my own global lock / unlock functions for PostgreSQL

    - by rafalmag
    I have postgresql (in perlu) function getTravelTime(integer, timestamp), which tries to select data for specified ID and timestamp. If there are no data or if data is old, it downloads them from external server (downloading time ~300ms). Multiple process use this database and this function. There is an error when two process do not find data and download them and try to do an insert to travel_time table (id and timestamp pair have to be unique). I thought about locks. Locking whole table would block all processes and allow only one to proceed. I need to lock only on id and timestamp. pg_advisory_lock seems to lock only in "current session". But my processes uses their own sessions. I tried to write my own lock/unlock functions. Am I doing it right? I use active waiting, how can I omit this? Maybe there is a way to use pg_advisory_lock() as global lock? My code: CREATE TABLE travel_time_locks ( id_key integer NOT NULL, time_key timestamp without time zone NOT NULL, UNIQUE (id_key, time_key) ); ------------ -- Function: mylock(integer, timestamp) DROP FUNCTION IF EXISTS mylock(integer, timestamp) CASCADE; -- Usage: SELECT mylock(1, '2010-03-28T19:45'); -- function tries to do a global lock similar to pg_advisory_lock(key, key) CREATE OR REPLACE FUNCTION mylock(id_input integer, time_input timestamp) RETURNS void AS $BODY$ DECLARE rows int; BEGIN LOOP BEGIN -- active waiting here !!!! :( INSERT INTO travel_time_locks (id_key, time_key) VALUES (id_input, time_input); EXCEPTION WHEN unique_violation THEN CONTINUE; END; EXIT; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 1; ------------ -- Function: myunlock(integer, timestamp) DROP FUNCTION IF EXISTS myunlock(integer, timestamp) CASCADE; -- Usage: SELECT myunlock(1, '2010-03-28T19:45'); -- function tries to do a global unlock similar to pg_advisory_unlock(key, key) CREATE OR REPLACE FUNCTION myunlock(id_input integer, time_input timestamp) RETURNS integer AS $BODY$ DECLARE BEGIN DELETE FROM ONLY travel_time_locks WHERE id_key=id_input AND time_key=time_input; RETURN 1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 1;

    Read the article

  • Accessing an Access DB from Outlook via VBA

    - by camastanta
    Hi The situation: In Outlook I get a message from a server. The content of the message needs to be put into an Access db. But, there may not exist another message with the same date. So, I need to look into a db if there is already a message with the same date and time. If there exists one, then it needs to be replaced and otherwise the message needs to be added to the database. The database contains a list of current positions from the vehicles on the road. The problem: I have problems to compare a date time with a date time in an Access DB via VBA. The query I use returns no records but there is a record in the database. This is the query I use: adoRS.Open "SELECT * FROM currentpositions WHERE ((currentpositions. [dateLT])=" & "#" & date_from_message & "#" & ")", adoConn, adOpenStatic, adLockOptimistic Second I need to now what the result is of that query. How can I determine the number of records that my query gives me? Thanks camastanta

    Read the article

  • sqlite3.OperationalError: database is locked - non-threaded application

    - by James C
    Hi, I have a Python application which throws the standard sqlite3.OperationalError: database is locked error. I have looked around the internet and could not find any solution which worked (please note that there is no multiprocesses/threading going on, and as you can see I have tried raising the timeout parameter). The sqlite file is stored on the local hard drive. The following function is one of many which accesses the sqlite database, and runs fine the first time it is called, but throws the above error the second time it is called (it is called as part of a for loop in another function): def update_index(filepath): path = get_setting('Local', 'web') stat = os.stat(filepath) modified = stat.st_mtime index_file = get_setting('Local', 'index') connection = sqlite3.connect(index_file, 30) cursor = connection.cursor() head, tail = os.path.split(filepath) cursor.execute('UPDATE hwlive SET date=? WHERE path=? AND name=?;', (modified, head, tail)) connection.commit() connection.close() Many thanks.

    Read the article

  • Strange use of the index in Mysql

    - by user309067
    explain SELECT feed_objects.* FROM feed_objects WHERE (feed_objects.feed_id IN (165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,126,125,124,122,121,120,119,118,117,116,115,114,113,111,110)) ; +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | feed_objects | ALL | by_feed_id | NULL | NULL | NULL | 188 | Using where | +----+-------------+--------------+------+---------------+------+---------+------+------+-------------+ Not used index 'by_feed_id' But when I point less than the values in the "WHERE" - everything is working right explain SELECT feed_objects.* FROM feed_objects WHERE (feed_objects.feed_id IN (165,160,159,158,157,153,152,151,150,149,148,147,129,128,127,125,124)) ; +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ | 1 | SIMPLE | feed_objects | range | by_feed_id | by_feed_id | 9 | NULL | 18 | Using where | +----+-------------+--------------+-------+---------------+------------+---------+------+------+-------------+ Used index 'by_feed_id' What is the problem?

    Read the article

  • MYSQL: How to limit inner join?

    - by Sergii Rechmp
    I need some help with my query. I have 2 tables: all: art|serie sootv: name|art|foo I need to get result like name|serie. My query is: SELECT t2.NAME, t1.serie FROM ( SELECT * FROM `all` WHERE `serie` LIKE '$serie' ) t1 INNER JOIN sootv t2 ON t1.art = t2.art; it works, but sootv table contains data like name|art|foo abc | 1 | 5 abc | 1 | 6 i get 2 same results. Its not what i need. Help me please - how i can get only one result: abc|1 Thanks.

    Read the article

  • Service Broker not working after database restore

    - by roryok
    Have a working Service Broker set up on a server, we're in the process of moving to a new server but I can't seem to get Service Broker set up on the new box. Have done the obvious (to me) things like Enabling Broker on the DB, dropping the route, services, contract, queues and even message type and re adding them, setting ALTER QUEUE with STATUS ON SELECT * FROM sys.service_queues gives me a list of the queues, including my own two, which show as activation_enabled, receive_enabled etc. Needless to say the queues aren't working. When I drop messages into them nothing goes in and nothing comes out. Any ideas? I'm sure there's something really obvious I've missed...

    Read the article

  • Nested and complicated select statement

    - by Selase
    What i want to do here is simple...display an ivestigators ID and him corresponding name... That can be easily done from the users table by selecting based on the user type. However i want to select only some type of investigators. The analogy here is investigators are assigned to an exhibit for them to investigate. One investigator can be assigned to a maximum of 3 cases only. Now during the assigning of investigators, i want to write a select statement that would retrieve only investigatorID's that have been assigned to less than or equal to 2 cases. I have included exhibit and users table that shows sample data below. Now i sort of have an idea that i will have to first of all pick out all the investigators by their ID from the users list and then filter them through the exhibit table by dropping those assigned to 3 cases and leaving just those with two cases. then afterwards i use this IDs to select the Investigators name. the big questions is how do i write the statement??

    Read the article

  • MySQL left outer join is slow

    - by Ryan Doherty
    Hi, hoping to get some help with this query, I've worked at it for a while now and can't get it any faster: SELECT date, count(id) as 'visits' FROM dates LEFT OUTER JOIN visits ON (dates.date = DATE(visits.start) and account_id = 40 ) WHERE date >= '2010-12-13' AND date <= '2011-1-13' GROUP BY date ORDER BY date ASC That query takes about 8 seconds to run. I've added indexes on dates.date, visits.start, visits.account_id and visits.start+visits.account_id and can't get it to run any faster. Table structure (only showing relevant columns in visit table): create table visits ( `id` int(11) NOT NULL AUTO_INCREMENT, `account_id` int(11) NOT NULL, `start` DATETIME NOT NULL, `end` DATETIME NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE `dates` ( `date` date NOT NULL, PRIMARY KEY (`date`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; dates table contains all days from 2010-1-1 to 2020-1-1 (~3k rows). visits table contains about 400k rows dating from 2010-6-1 to yesterday. I'm using the date table so the join will return 0 visits for days there were no visits. Results I want for reference: +------------+--------+ | date | visits | +------------+--------+ | 2010-12-13 | 301 | | 2010-12-14 | 356 | | 2010-12-15 | 423 | | 2010-12-16 | 332 | | 2010-12-17 | 346 | | 2010-12-18 | 226 | | 2010-12-19 | 213 | | 2010-12-20 | 311 | | 2010-12-21 | 273 | | 2010-12-22 | 286 | | 2010-12-23 | 241 | | 2010-12-24 | 149 | | 2010-12-25 | 102 | | 2010-12-26 | 174 | | 2010-12-27 | 258 | | 2010-12-28 | 348 | | 2010-12-29 | 392 | | 2010-12-30 | 395 | | 2010-12-31 | 278 | | 2011-01-01 | 241 | | 2011-01-02 | 295 | | 2011-01-03 | 369 | | 2011-01-04 | 438 | | 2011-01-05 | 393 | | 2011-01-06 | 368 | | 2011-01-07 | 435 | | 2011-01-08 | 313 | | 2011-01-09 | 250 | | 2011-01-10 | 345 | | 2011-01-11 | 387 | | 2011-01-12 | 0 | | 2011-01-13 | 0 | +------------+--------+ Thanks in advance for any help!

    Read the article

  • Does Table.InsertOnSubmit create a copy of the original table?

    - by Bryan
    Using InsertOnSubmit seems to have some memory overhead. I have a System.Data.Linq.Table<User> table. When I do table.InsertOnSubmit(user) and then int count = table.Count(), the memory usage of my application increases by roughly the size of the User table, but the count is the number of items before user was inserted. So I'm guess an enumeration after InsertOnSubmit will create a copy of the table. Is that true?

    Read the article

  • Does anybody have any suggestions on which of these two approaches is better for large delete?

    - by RPS
    Approach #1: DECLARE @count int SET @count = 2000 DECLARE @rowcount int SET @rowcount = @count WHILE @rowcount = @count BEGIN DELETE TOP (@count) FROM ProductOrderInfo WHERE ProductId = @product_id AND bCopied = 1 AND FileNameCRC = @localNameCrc SELECT @rowcount = @@ROWCOUNT WAITFOR DELAY '000:00:00.400' Approach #2: DECLARE @count int SET @count = 2000 DECLARE @rowcount int SET @rowcount = @count WHILE @rowcount = @count BEGIN DELETE FROM ProductOrderInfo WHERE ProductId = @product_id AND FileNameCRC IN ( SELECT TOP(@count) FileNameCRC FROM ProductOrderInfo WITH (NOLOCK) WHERE bCopied = 1 AND FileNameCRC = @localNameCrc ) SELECT @rowcount = @@ROWCOUNT WAITFOR DELAY '000:00:00.400' END

    Read the article

  • Filling a LOV in Oracle Apex based on data in another text box

    - by Martin Pugh
    I am fairly new to Oracle Apex, and have a problem. Our application currently has a method of entering data, with several text boxes and Optional List of Values. I would like to have an LOV based on information in another text box like so: select APPOINTMENT_ID PATIENT_ID from APPOINTMENT where PATIENT_ID = :P9_PAT_NUM where P9_PAT_NUM is a patient number in a text box. However, this would apparently only work if the text box has already been submitted, else it assumes the text box is null. Is there any way to get this working with an LOV, or perhaps some other method?

    Read the article

  • Sybase ASA 8.0 trigger how to determine whether to insert ,update,delete operate.

    - by guaike
    Follow is my trigger of Sybase ASA 8.0 script: Create trigger dba.test after insert,delete,update order 1 on DBA.tb_press referencing old as _old new as _new for each row begin --How to detect deleteOperate,updateOperate? if deleteOperate then insert into syncLog(tableName,keyId,version,operate) select tb_press,_old.id,1,'delete' end if; end how to determine whether to insert ,update,delete operate.

    Read the article

  • rails howto compare datetime ?

    - by fenec
    hello, i have games in my sqLite DB with the attribute starting_date( t.date :starting_date). i would like to know all the games that have alreday started so i am using this lines of code: Game.find :all,:conditions=>"starting_date <= #{Date.today}" Game.find_by_sql("SELECT * FROM "games" WHERE (created_at < 2010-05-13)") the result is nill,even though i know that i have games that have already started like this one : #<Game id: 1, team_1_id: 2, team_2_id: 1, status: 2, team_1_points: nil, team_2_points: nil, starting_date: "2010-05-05", winner: 1, sport: "football", country: nil, league: "calcio", created_at: "2010-04-07 00:09:21", updated_at: "2010-05-13 00:57:19"> what am i doing wrong here?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 718 719 720 721 722 723 724 725 726 727 728 729  | Next Page >