Select Records That Only Occur One Time
SELECT records that only occur one time.
I have a table with an ID field. I want to SELECT the records where the ID field only has one occurrence. If it appears in two or more rows I do not want to SELECT it.
View Complete Forum Thread with Replies
Related Forum Messages:
Delete The Records That Occur More Than Once
I have two tables...(the tables contain some duplicate entries) I need to combine them and then delete the records that occur more than once....this means not leaving any entry for the duplicate... Is there any simple way to do this?
View Replies !
Error 127 Occur
We have several tables experiencing table error 127 quite often, sometimes several times per day. I have read the relevant posts recently, including http://www.mysql.com/doc/en/Repair.html Forgive me for my lack of technical expertise in these questions: 1) The SQL statements are all INSERTS (using all fields - see below for table structure), so is it possible, say when a client server goes down during an insert, that somehow the data only gets inserted "half-way"? 2) From other posts, it seems that one common column is a date or timestamp field. Could this be the problem? 3) There are no (very few - once a month for statistical reporting) selects done on the tables, so we don't know when the problem occurs, so is there a way to monitor the tables? 4) It only affects my tables with more than 10k-20k inserts per day. 5) Is moving to InnoDB a way to possible solve the above? 6) We occasionally experience "Mysql server has gone away" - due to network congestion, could this cause problems? I am using MyISAM tables, MySQL version 3.23-49a. CREATE TABLE /*!32300 IF NOT EXISTS*/ vine ( id int(25) unsigned NOT NULL auto_increment, username varchar(50) , url text , timeaccess datetime , remote_addr varchar(30) , include varchar(50) , PRIMARY KEY (id), UNIQUE id (id), INDEX others (include,username) );
View Replies !
Where Numerous Fields Occur In A Second Table (efficiently)
I have 2 tables, one (games) contains a list of games. Each game contain 2 fields, p1 and p2 representing the IDs of the players participating in the specific game, as well as other non-important information. Another table (topplayers) contains only 2 fields, pid which corresponds to a player id, and score which corresponds to its score. All entries of pid occur in games.p1 or games.p2, but the opposite is not true. ie: the 2 tables are games(p1 int(11), p2 int(11)...) and toppplayers(pid int(11), score int(11)) I want to launch a query which returns all games (from games) where both p1 and p2 are appear in topplayers.pid I managed to write some horrible abomination using 2 instances of 'Exist', but it took 5(!) minutes to run, which is _way_ too slow for my needs. What is the right way of doing it? EDIT: I've realized I better post my current query: select p1, p2 from games where exists(select * from topplayers where p1 = pid) and exists(select * from topplayers where p2 = pid)
View Replies !
Counting Values That First Occur During A Date Range
I don't know why the query for this is eluding me.. I haven't had a problem coming up with a query for something in a while. I've got a table with two relevant columns, a name and a datetime created_at. The name can occur on many rows at various times. I am creating a list of all names whose first entry into the table was during a user-specified time frame. I have that working fine. It looks similar to this: SELECT name, MIN(created_at) as `min_time` FROM table GROUP BY name HAVING `min_time` BETWEEN ? AND ? In order to page this list, to display only 20 rows at a time, I need to also count how many names appeared during the time frame. For some reason I can't think of the COUNT() to do that...
View Replies !
Calculate Time Between Records
I have been trying to do this for days and would appreciate some help! This database was designed by someone else and i have been lumped with writing some reports! I have a table that stores GPS information from vehicles. I am trying to write a report that will show a vehicles "idle time" Below is the table Structure.....
View Replies !
Time Difference Between Records From Two Tables
I have two tables with data from two separate data loggers. The timestamps of the records are not perfectly synchronised (eg. 2005-07-11 22:50:00 and 2005-07-11 22:49:58). All data must be joined to one table and all records that are out of sync by more then 30 seconds must be rejected. I tried somethig like this: Code:
View Replies !
Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code: select date, `time` as timetime , time_format(`time','%l:%i %p') as Printtime, client, address from..........
View Replies !
Select Between Date And Time
I have field date and time respectively. I want to select date between (date1 and time1 > 17) and (date2 < 17) I tried two ways but doesn't work, please see code below. SELECT * from purchases_suppliers WHERE (date_invoice >= '2009/02/25' and time_invoice > 17) AND (date_invoice <= '2009/02/27' AND time_invoice < 17) SELECT * from purchases_suppliers WHERE date_invoice between ('2009/02/25' ANd time_invoice > 17) AND ('2009/02/27' AND time_invoice < 17 )
View Replies !
Update And Select Same Time
Code: UPDATE art SET art.art_writer = (SELECT writer.writer_name, art.art_author FROM writer, author WHERE art.art_author LIKE 'writer.writer_name') Is this query syantex is ok ? Where, art_writer is empty fild where I want to put correct value which is in writer table and it should similar to value in art_author filed.
View Replies !
Select Statement Where Time <= 5min Ago
i tried many different MySQL fucntions and looked through the MySQL manual, and it didnt help me any. What i want to do is select all rows from a table where my time column(DATETIME) is less then or equal to 5 minutes ago from now().
View Replies !
Cron Job And Time Select Question
I am trying to figure out a select...where statement to use in a script running via cron job several times an hour. example in plain english:cron job runs hourly, e.g. 12:50, 1:10, 1:30 need to select records where a datetime field is > 12:45 next hour I need to select where the datetime field is > 1:45 and so on....How do I calculate the time to use in my where statement?
View Replies !
Select Date And Time In Same Column
I have table called mytable and there I have column named time example: time 2006-07-10 10:28:06 2006-08-18 20:48:22 2006-09-15 12:11:41 2006-10-12 23:06:02 is there any possibles that I can make query example: SELECT *FROM mytable WHERE date BETWEEN 2006-07-10 AND 2006-09-15 AND time BETWEEN 10:28:06 AND 12:11:41 My point is can I make query where I first find between date and after that I make query where I find between time when information is in the same column?
View Replies !
Select Date Ignoring Time
Im using a datetime fields eg. "2006-08-07 16:01:09" and I want to select dates... eg. "select * from mytable where mydate="2006-08-07" ignoring the time.
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
Datatype TIME - Select Statement Format
The table I have has two columns of ID and INTERVALS. The INTERVALS is datatype TIME. Whenever I do a select I always get the table back showing the time in the format HH:MM:SS like for 11am I get back 11:00:00. I only want HH:MM like 11:00. How do I achieve this? I have tried to limit to 2 decimal places but that has not worked so far. ...
View Replies !
Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working
View Replies !
Select Last Records
This is the code i am trying but it ain't working, i am basically trying to find the last 10 entries (according to ref) entered into the database.
View Replies !
Select Records
I am needing to select records where a field has the current date. I figured that this would work: SELECT * FROM table WHERE DATE(datetimefield) = DATE(NOW()) But that doesnt seem to work. In PHP i receive the following error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(datetimefield) = DATE(NOW()) ORDER BY datetimefield' at line 1,I have tried with a conjuction of MONTH and DAY, but it always seems to hang on the DAY part of it. Whats up with it?
View Replies !
How To Select Records?
I select a list of records eg: from table1. select names as temp from table1; next i want to use the names selected from table1 to select other records again from table 2. How do i write the combine statement?
View Replies !
Select All Records
I have a structure like this: A table (authors) that consists of authors names, ages, and an authorid A table (books) that consists of books names, year published, and a bookid A table (aTob) that consists of authorid and bookid, to match book to author I want to write a select statement that selects all authors and all books they've written, if any. So my best guess is: SELECT authors.name, authors.age, books.name, books.year from authors, books, aTob. But this doesn't work...sems to draw SQL into an infinite operation. The result I'm looking for is a row for each author and book...so some authors will be listed several times if they've written several books...BUT, I also want authors who haven't written any books...The end goal is to have a view in which I can do some statistical things, such as find the average year of publication for each author.
View Replies !
Select Certain Records
I have a database that has a column of int named crsnum. I need to select data from this db omitting certain crsnum's, and a certain email match from the email field example: "SELECT * FROM login WHERE (Email = '$email') ORDER BY ID DESC"; Works great but I need to add " WHERE Crsnum != 1,3,6,9,19 " so these crsnums are omited from the select.
View Replies !
Cant Select More Than 1000 Records
Command "select * from table" whith 3000 records give me only 1000 rows. I can't figure out what is wrong. I think that something wrong is with my my.cnf but can't find which varaible I have to change.....
View Replies !
Select Random Records Amongst Last 10
Never came across this kind of probelm, I suppose there's a simple answer... I need to select 2 random records amongst the, say, 20 last entered records of a table (theres a date field). For example, I wish to display random products on the front page of a website, but since people come to this site to see new products, I would like these random products to be selected from the last ones that were entered. I thought I could put a date clause and a rand order, to restrict to the records to the last x days, but it is not an option because this could result in no records being selected, since in theory this chosen limit could be nearer than the last record entered. My other option would be to use PHP to fetch random rows in a recordset of the last 20 records selected through mysql, but I wonder if there's a way to do it directly in a mysql query.
View Replies !
Select Unique Records
I am using php, have a database table with 200 records, each record has an ip field, How can i select only records with unique ip values? rather than all the records?
View Replies !
Select All Records From X Days Ago
You have DATE, DATETIME and TIMESTAMP. I want to be able to retrieve records by saying, "Select all records from 4 days ago." I dont care so much for the time, but the date is very important.
View Replies !
Select Records By Date
I got a form where the date is being plugged into the database on items that are marked as sold. I want all records to show up until 5 days after being posted as sold. So for example: If I posted a item as sold on Oct 5th, after Oct 10th the record wouldnt be shown again.
View Replies !
Select Surrounding Records
I am trying to find a way to select 2 rows before and two rows after a known row. For example, I have a table with an id and a datetime field. Given row with an id of 70, how can I select the two rows that would preceed it, and the two that would follow it, when the table is sorted on the datetime field? I have been looking around, but can't seem to find a way to this.
View Replies !
How Select Top 2 Records For Every User
here is my easy table. Primary key is User: User - Points Karl - 50 Karl - 30 Karl - 9 Karl - null Peter - 88 Peter - 87 Peter - 50 Mark - 100 I need select max top 2 record of points for every name, eventually create column with suma of points and count of record per name. Here is result, which I assume after reguest. User - Points Karl - 50 Karl - 30 Peter - 88 Peter - 87 Mark - 100 eventually possibilty: User - Points - Sum - Count Karl - 50 - 80 -2 Karl - 30 - 80 - 2 Peter - 88 - 175 - 2 Peter - 87 - 175 - 2 Mark - 100 - 100 - 1
View Replies !
Select Distinct Records
I am trying to compare the date part of a datetime value field with today's date.... Here's the sql: mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate" Set rs= Con.Execute( mySQL ) That return nothing even though Test has records for today..
View Replies !
Random Select Records
I've got a question about a query in MySQL. I would like to random select records from a table. Everytime the query will execute the resultset need to be diferent (random selected records from the table).
View Replies !
Select Records From Remote Server
I need to know if it is possible select records from a database table on a remote server and if so what is the command syntax? I'm picturing something along the lines of SELECT * FROM <server address>:<database>.<table> but that does not work.
View Replies !
Select Unique Records From A Join?
I have two tables, both tables contain a matching JobID. Some records in the job table are not present in the joblocation table. I need to Select only those records. I thought I could just do a left join on the two tables with a not equal to where clause? SELECT job.JobLocation1, job.City, job.Province, job.SiteName1, job.Code, job.JobID, joblocation.JobID FROM soileng.joblocation joblocation LEFT OUTER JOIN soileng.job job ON joblocation.JobID = job.JobID WHERE job.JobID != joblocation.JobID limit 100 Any ideas on how to get this to work?
View Replies !
SELECT Date Records Between Two Given Dates.
Let's say i have a job posting site and a user wants to see the jobs posted between dates.... how would i do that? here's how a table would look like(sample): table: Jobs fields: JobID JobStatus DatePosted DateToDeactivate and when i build the query i would make it something like this: "Select * from Jobs Where JobID = '123' and ( DatePosted BETWEEN 2003-05-08 12:14:37 AND 2004-06-08 12:14:37)" i know that this does not work! how can i do this.
View Replies !
|
TRACKER
