Search Results

Search found 27723 results on 1109 pages for 'sql puzzle'.

Page 659/1109 | < Previous Page | 655 656 657 658 659 660 661 662 663 664 665 666  | Next Page >

• How do I select a random record efficiently in MySQL?

  - by user198729

  mysql> EXPLAIN SELECT * FROM urls ORDER BY RAND() LIMIT 1; +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+ | 1 | SIMPLE | urls | ALL | NULL | NULL | NULL | NULL | 62228 | Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+-------+---------------------------------+ The above doesn't qualify as efficient,how should I do it properly?

  Read the article

• select top 50 records from sql

  - by air

  i have following database table name tbl_rec recno uid uname points ============================ 1 a abc 10 2 b bac 8 3 c cvb 12 4 d aty 13 5 f cyu 9 ------------------------- ------------------------- i have about 5000 records in this table. i want to select first 50 higher points records. i can't use limit statement as i am already using limit for paging. Thanks

  Read the article

• How do you write a recursive stored procedure

  - by Grayson Mitchell

  I simply want a stored procedure that calculates a unique id and inserts it. If it fails it just calls itself to regenerate said id. I have been looking for an example, but cant find one, and am not sure how I should get the sp to call itself, and set the appropriate output parameter. I would also appreciate someone pointing out how to test this sp also. ALTER PROCEDURE [dbo].[DataContainer_Insert] @SomeData varchar(max), @DataContainerId int out AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN TRY SELECT @UserId = MAX(UserId) From DataContainer INSERT INTO DataContainer (UserId, SomeData) VALUES (@UserId, SomeData) SELECT @DataContainerId = scope_identity() END TRY BEGIN CATCH --try again exec DataContainer_Insert @DataContainerId, @SomeData END CATCH END

  Read the article

• Transaction within a Transaction in C#

  - by Rosco

  I'm importing a flat file of invoices into a database using C#. I'm using the TransactionScope to roll back the entire operation if a problem is encountered. It is a tricky input file, in that one row does not necessary equal one record. It also includes linked records. An invoice would have a header line, line items, and then a total line. Some of the invoices will need to be skipped, but I may not know it needs to be skipped until I reach the total line. One strategy is to store the header, line items, and total line in memory, and save everything once the total line is reached. I'm pursuing that now. However, I was wondering if it could be done a different way. Creating a "nested" transaction around the invoice, inserting the header row, and line items, then updating the invoice when the total line is reached. This "nested" transaction would roll back if it is determined the invoice needs to be skipped, but the overall transaction would continue. Is this possible, practical, and how would you set this up?

  Read the article

• Store latitudes and longitudes in database for proximity/radius search using Google Maps API, .NET a

  - by poojad

  What is the approach for storing the latitudes and longitudes for multiple addresses as a one time set up. I need to find the nearby stores using Google Maps and I have to get the latitudes and longitudes of all the available stores. As the data is huge and may increase or change in future, can anyone suggest an approach taking performance and maintenance into consideration. Thank you.

  Read the article

• Query for multiple joins

  - by Shailaja

  i have 3 tables named dataset,dataelem and transformdataelem with column names as below: main.Dataset ------------ datasetID (PK) applicationID main.Dataelem ------------- dataelemID(PK) datasetID(FK) dataelemname biztermID main.Transformdataelem ---------------------- OutputdataelemID InputdataelemID My requirement is: All tables are referenced. Extract all the dataelemId rows from dataelem table where applicationID of dataset table is equal to 1044 and biztermid shud be null. Then whatever resultant dataelemIDs from the above query should be matched with outputdataelemID of Transformdataelem table and we shud get the respective input dataelemId's. Again with these matched inputdataelemID's we shud get the dataelemname's from datelem table.

  Read the article

• MySQL - Where - search string - MATCH.

  - by Jamie

  Hi guys, Quick question. I'm in a bit of a rush but if someone could quickly point me in the right direction I would be very very happy. I have a field in the db, let's call it field_a which returns a string in the format "20,50,60,80" etc. I wish to do a query which will search in this field to see if 20 exists. Could I use MySQL MATCH or is there a better way? Thank you!

  Read the article

• select distinct col1 with min(col2) and max(col3) from table

  - by user338804

  My table looks like this with duplicates in col1 col1, col2, col3, col4 1, 1, 0, a 1, 2, 1, a 1, 3, 1, a 2, 4, 1, b 3, 5, 0, c I want to select distinct col1 with max (col3) and min(col2); so result set will be: col1, col2, col3, col4 1, 2, 1, a 2, 4, 1, b 3, 5, 0, c I have a solution but looking for best ideas?

  Read the article

• Effects of order of columns, while defining an index on them, on the performance of the queries

  - by Hossein

  Hi, Does order of the columns in an Index definition for a table in a database has any effect on the performance? for e.g. are these two queries different ? CREATE INDEX xxx ON tablex(col1,col2) CREATE INDEX xxx ON tablex(col2,col1) what about the in case that I use a BTREE index? I am using Mysql. thanks

  Read the article

• Autoincrement Primary key in Oracle database

  - by GigaPr

  Hi i would like to achieve something like CREATE TABLE RollingStock ( Id NUMBER IDENTITY(1,1), Name Varchar2(80) NOT NULL ); in Oracle how do i do it ? Thanks in advance

  Read the article

• PHP MSSQL : How to display output when query return no row

  - by vamps

  i have a problem with my PHP-MSSQL query. i have a join table that need to give a result something be like this: Department Group A Group B Total A+B WORKHOUR A OTHOUR A WORKHOUR B OTHOUR B WORKHOUR OTHOUR HR 10 15 25 0 35 15 IT 5 5 5 5 Admin 12 12 12 12 the query will count how many employee as per given date (admin will enter data and once submitted, the query will give the above result). The problem is, the final output is a mess when there's no row to be displayed. the column is shifted to the right. i.e: only Group A in IT only Group B in Admin Department Group A Group B Total A+B WORKHOUR A OTHOUR A WORKHOUR B OTHOUR B WORKHOUR OTHOUR HR 10 15 25 0 35 15 IT 5 5 5 5 Admin 12 12 12 12 my question is, how to prevent this to happen? i've tried everything with While.... if else.. but the result is still the same. how to display output "0" if no rows to return? echo "0"; this is my QUERY: select DD.DPT_ID,DPT.DEPARTMENT_NAME,TU.EMP_GROUP, sum(DD.WORK_HOUR) AS WORK_HOUR, sum(DD.OT_HOUR) AS OT_HOUR FROM DEPARTMENT_DETAIL DD left join DEPARTMENT DPT ON (DD.DEPT_ID=DPT.DEPT_ID) LEFT JOIN TBL_USERS TU ON (TU.EMP_ID=DD.EMP_ID) WHERE DD_DATE>='2012-01-01' AND DD_DATE<='2012-01-31' AND TU.EMP_GROUP!=2 GROUP BY DD.DEPT_ID, DPT.DEPARTMENT_NAME,TU.EMP_GROUP ORDER BY DPT.DEPARTMENT_NAME this is one of the logic that i've used, but doesn't return the result that i want:: while($row = mssql_fetch_array($displayResult)) { if ((!$row["WORK_HOUR"])&&(!$row["OT_HOUR"])) { echo "<td >"; echo "empty"; echo "&nbsp;</td>"; echo "<td >"; echo "empty"; echo "&nbsp;</td>"; } else { echo "<td>"; echo $row["WORK_HOUR"]; echo "&nbsp;</td>"; echo "<td>"; echo $row["OT_HOUR"]; echo "&nbsp;</td>"; } } please help. i've been doing this for 2 days. @__@

  Read the article

• how do I integrate the aspnet_users table (asp.net membership) into my existing database

  - by ooo

  i have a database that already has a users table COLUMNS: userID - int loginName - string First - string Last - string i just installed the asp.net membership table. Right now all of my tables are joined into my users table foreign keyed into the "userId" field How do i integrate asp.net_users table into my schema? here are the ideas i thought of: Add a membership_id field to my users table and on new inserts, include that new field in my users table. This seems like the cleanest way as i dont need to break any existing relationships. break all existing relationship and move all of the fields in my user table into the asp.net_users table. This seems like a pain but ultimately will lead to the most simple, normalized solution any thoughts?

  Read the article

• Do I need to drop index on temp table?

  - by Phil

  Hi, Fairly simple question, but I don't see it anywhere else on SO: Do indexes (indices?) on a temporary table get automatically deleted with the temporary table? I'd imagine they do but I don't really know how to check to make sure. Thanks, Phil

  Read the article

• Insert or update when product is present using mysql

  - by Aryan

  main_product table productid outward shopid 333 2 44//present 343 4 44//present 353 5 44//present 363 1 44//present 373 2 44//not present min_product table productid outward shopid 333 1 44 343 1 44 353 1 44 363 1 44 SELECT DISTINCT (A.productid),A.outward, B.productid,B.outward FROM main_product A INNER JOIN min_product B on B.productid=A.product_id where A.shopid='44' and B.shopid='44' my question how can i INSERT OR UPDATE in one query using mysql by checking if it is present or not if present update else insert something like this? INSERT INTO mytable (key, value) VALUES ($newkey, $newvalue) ON DUPLICATE KEY UPDATE SET value = $newvalue

  Read the article

• how to revert non-corrupted mssql database with stopat

  - by Yisman

  i have a good, working valid non-corrupted database in mssql that i want to revert to a point in time how is that done? the standard RESTORE command requires a full backup as a starting point, and then log backups thereafter. i cant understand why this must be done from a backup. if my db is good and the logs are OK, why cant i just revert with a STOPAT from the live logs in the db? one dba suggested that whenever i want to restore i should THEN make a log backup and then RESTORE with STOPAT. i believe it would work but sounds a little backwards any better ideas? thank you very much

  Read the article

• Joining 3 tables - doing joins how to

  - by Kieran

  This is my current query - its not getting the required result. I want it do display all of the "resources" even if they dont have a connection. SELECT * FROM (`user_permissions`) JOIN `user_groups` ON `user_groups`.`id` = `user_permissions`.`role` JOIN `user_resources` ON `user_resources`.`id` = `user_permissions`.`resource` WHERE `role` = '4' When I try left join or right join it still returns the same result. The result I get is: id | role | resource | name 5 | 4 | 2 | Changelog I want id | role | resource | name 5 | 4 | 2 | Changelog null | null | null | Resource2 null | null | null | Resource3 Is this possible?

  Read the article

• Specify fields in a recursive find with cakephp

  - by Razor Storm

  Suppose I have a table Recipe that hasmany ingredients. I do a recursive find to grab recipes with their associated ingredients: $this->Recipe->find('all', array('fields' => array('id','title','description'))); Here I can use the 'fields' attribute to specify that I only want it to return id, title, and description. However, despite this, cakephp still returns ALL columns from the ingredients table. How do I tell cakephp that I only want ingredient table's id and name fields? btw ingredient model is "Ingredient" and the table is ingredients, and the aggregation table is recipes_ingredients.

  Read the article

• join codition in sqlserver

  - by Pallavi

  after applying join condition on two tables i want records which is maximum among records of left table my query SELECT a1.*, t.*, ( a1.trnratefrom - t.trnratefrom )AS minrate, ( a1.trnrateto - t.trnrateto ) AS maxrate FROM (SELECT a.srno, trndate, b.trnsrno, Upper(Rtrim(Ltrim(b.trnstate))) AS trnstate, Upper(Rtrim(Ltrim(b.trnarea))) AS trnarea, Upper(Rtrim(Ltrim(b.trnquality))) AS trnquality, Upper(Rtrim(Ltrim(b.trnlength))) AS trnlength, Upper(Rtrim(Ltrim(b.trnunit))) AS trnunit, b.trnratefrom, b.trnrateto, a.remark, entdate FROM mstprodrates a INNER JOIN trnprodrates b ON a.srno = b.srno)a1 INNER JOIN (SELECT c.srno, trndate, d.trnsrno, Upper(Rtrim(Ltrim(d.trnstate))) AS trnstate, Upper(Rtrim(Ltrim(d.trnarea))) AS trnarea, Upper(Rtrim(Ltrim(d.trnquality))) AS trnquality, Upper(Rtrim(Ltrim(d.trnlength))) AS trnlength, Upper(Rtrim(Ltrim(d.trnunit))) AS trnunit, d.trnratefrom, d.trnrateto, c.remark, entdate FROM mstprodrates c INNER JOIN trnprodrates d ON c.srno = d.srno) AS t ON a1.trnstate = t.trnstate AND a1.trnquality = t.trnquality AND a1.trnunit = t.trnunit AND a1.trnlength = t.trnlength AND a1.trnarea = t.trnarea AND a1.remark = t.remark WHERE t.srno = (SELECT MAX(srno) FROM a1 WHERE srno < a1.srno)

  Read the article

• mysql: inserting data and autoincrement

  - by every_answer_gets_a_point

  i am converting from access to mysql i have a table in access where one of the columns is an autonumber when i transfer the data into the mysql database (where i also have a column that is auto_increment), should i be transfering the auto_increment data into the auto_increment column, or will it auto_increment itself? how do i ensure that if i do not transfer the autoincrement data from access, that it auto_increments properly?

  Read the article

• how can add an extra select in this query?

  - by BulgedSnowy

  i've three tables related. images: id | filename | filesize | ... nodes: image_id | tag_id tags: id | name And i'm using this query to search images containing x tags SELECT images.* FROM images INNER JOIN nodes ON images.id = nodes.image_id WHERE tag_id IN (SELECT tags.id FROM tags WHERE tags.tag IN ("tag1","tag2")) GROUP BY images.id HAVING COUNT(*)= 2 The problem is that i need to retrieve also all images contained by the retrieved image, and i need this in the same query. This the actual query wich search retrieve all tags contained by the image: SELECT tag FROM nodes JOIN tags ON nodes.tag_id = tags.id WHERE image_id = images.id and nodes.private = images.private ORDER BY tag How can i mix this two to have only one query?

  Read the article

• i want to solve my sql query

  - by Dharmendra

  i have one table bbc(country,region,area,population,gdp) and i want to list the regions with total population of at least 100 million. be careful it is very tuff query

  Read the article

• MYSQL - Selecting how many "Days Old" a field is.

  - by Joe

  How can I select how many days old a field is based on it's "DateAdded" field? eg. SELECT DAYSOLD(tbl_stuff.DateAdded) as DaysOld FROM tbl_stuff

  Read the article

• How do I do a WHERE on the COUNT(name) produced by a GROUP BY clause?

  - by ssg31415926

  WHERE gets processed before GROUP BY in the SELECT statement. How can I use WHERE on the result of a COUNT(name)? What I want is this: SELECT topic, COUNT(name) AS counter FROM blah GROUP BY name WHERE counter <> 1

  Read the article

• Running an application from an USB device...

  - by Workshop Alex

  I'm working on a proof-of-concept application, containing a WCF service with console host and client, both on a single USB device. On the same device I will also have the client application which will connect to this service. The service uses the entity framework to connect to the database, which in this POC will just return a list of names. If it works, it will be used for a larger project. Creating the client and service was easy and this works well from USB. But getting the service to connect to the database isn't. I've found this site, suggesting that I should modify machine.config but that stops the XCopy deployment. This project cannot change any setting of the PC, so this suggestion is bad. I cannot create a deployment setup either. The whole thing just needs to run from USB disk. So, how do I get it to run? (The service just selects a list of names from the database, which it returns to the client. If this POC works, it will do far more complex things!)

  Read the article

• While Loop in TSQL with Sum totals

  - by RPS

  I have the following TSQL Statement, I am trying to figure out how I can keep getting the results (100 rows at a time), store them in a variable (as I will have to add the totals after each select) and continue to select in a while loop until no more records are found and then return the variable totals to the calling function. SELECT [OrderUser].OrderUserId, ISNULL(SUM(total.FileSize), 0), ISNULL(SUM(total.CompressedFileSize), 0) FROM ( SELECT DISTINCT TOP(100) ProductSize.OrderUserId, ProductSize.FileInfoId, CAST(ProductSize.FileSize AS BIGINT) AS FileSize, CAST(ProductSize.CompressedFileSize AS BIGINT) AS CompressedFileSize FROM ProductSize WITH (NOLOCK) INNER JOIN [Version] ON ProductSize.VersionId = [Version].VersionId ) AS total RIGHT OUTER JOIN [OrderUser] WITH (NOLOCK) ON total.OrderUserId = [OrderUser].OrderUserId WHERE NOT ([OrderUser].isCustomer = 1 AND [OrderUser].isEndOrderUser = 0 OR [OrderUser].isLocation = 1) AND [OrderUser].OrderUserId = 1 GROUP BY [OrderUser].OrderUserId

  Read the article

< Previous Page | 655 656 657 658 659 660 661 662 663 664 665 666  | Next Page >