Need To Find 1 Occurance In A Table
I have a database storing photos. I want to display the first picture from each album on one page. The table in question has, say, 10 pictures, all with the same "album" id. I want to pull the first picture from each album.
Any ideas?
View Complete Forum Thread with Replies
Related Forum Messages:
Selecting First Occurance In A Given Period Of Record
I have a table to log sessions and user (connect_id) id's, start time etc - see below +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | connect_id | varchar(12) | | | | | | session_id | varchar(32) | | | | | | start_time | int(11) | | | 0 | | | client_ip | varchar(16) | | | | | +---------------+-------------+------+-----+---------+-------+ I need to create a report that will tell me, for instance, how many sessions were started today, that's easy I know but I also need another report that will tell me how many of those sessions were first-time visitors.
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
View Replies !
Find Rows In One Table
I am setting up an osCommerce store and have discovered that some products do not have corresponding entries in the categories table. I need to find these items (there are roughly 5000 items) so that they can be fixed. These are the relevant tables: Table Name: products Primary Key: products_id Table Name: products_to_categories Primary Keys: products_id and categories_id I need to find all rows in products that do not have a corresponding entry in products_to_categoriesIs this possible?
View Replies !
Find The Size Of The Table
Is there any function OR any way to find a size of the table? Let us assume the table contains the 3 rows and 4 columns, if i add the one more column does this increases the table size. How to find the size of the table?
View Replies !
How To Find Out Which Table Is Changed?
I dont have source code of my web application I installed. Its using mysql 5. when I create a new user account, I know "users" tables is changed, but there's also a couple other tables are modified. Is there a way I can find out which table is changed?
View Replies !
Mysqldump Can't Find Table
I'm trying to do a backup of just 1 table but when I run the command mysqldump -h machine -u lagoona -p database_name [collection] > collection.sql it complains that it can't find the table called collection which is definitely in the database. mysqldump: Couldn't find table: "[collection]" Is this the right command to use, I'm not that familiar with mysqldump? It's on linux and I'm running it from the command line. I can connect OK to the database itself when I use the command mysql -h machine -u lagoona -p I've also tried it as root and as my own username but no difference.
View Replies !
Find Nearest Value From Lookup Table
I am trying to implement a 'nearest value' lookup table name (name age table (100k+ records) id age 1 1.0 2 2.2 3 3.3 4 4.4 I want to lookup closest age and pull out the appropriate correction factor. cf (correction_factor) age factor 1.0 10 2.0 20 3.0 30 3.5 35 4.0 40 desired output id age factor 1 1.0 10 2 2.2 20 3 3.3 35 4 4.4 40 Looking at past forum posts I tried the following query which finds the nearest value OK.. ( SELECT factor FROM cf WHERE age > name.age ORDER BY factor ASC LIMIT 1 ) UNION ( SELECT factor FROM cf WHERE age <= name.age ORDER BY factor DESC LIMIT 1 ) ORDER BY ABS(cf.age - name.age) LIMIT 1 I understand the (SELECT...UNION...SELECT) part but cannot figure how to put it into a bigger query to get my desired output. I tried SELECT id, age, (( SELECT factor FROM cf WHERE age > name.age ORDER BY factor ASC LIMIT 1 ) UNION ( SELECT factor FROM cf WHERE age <= name.age ORDER BY factor DESC LIMIT 1 ) ORDER BY ABS(cf.age - name.age) LIMIT 1) FROM name;
View Replies !
Error 1032 - Can't Find Record In Table
I have a problem with a very simple table when using partitioning in mysql 5.1.11 on SuSE Linux 10.0: Table Information: CREATE TABLE `adr-0` ( Adr varchar(255) COLLATE latin1_german1_ci NOT NULL DEFAULT '', Qty int(10) NOT NULL, PRIMARY KEY (Adr), ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci PARTITION BY KEY (Adr) PARTITIONS 10 ; now i'm entering data into the table: insert into `adr-0` values ('TestAdr','1') on duplicate key update Qty = Qty + 1; this works fine until the "on duplicate key" triggers. When a duplicate key is found I get the error 1032 "can't find record in adr-0" instead of an increasing field qty. I see no limitations in the partioning section of the mysql documentation which could cause this error. On a non-partitioned table it works fine under 5.1.11. Is someone out there who knows if this is a bug or a feature?
View Replies !
Find Double (not Uniqe) Entries In A Table
I've got a table that looks like this (offcourse, the real one is much bigger): id| name | city 1 | john | 19 2 | bella | 11 3 | john | 12 4 | mike | 06 5 | alex | 12 6 | simon | 19 7 | alex | 11 .. and I want to select the 'id', 'name' and 'city' of those entries that the field 'name' isn't uniqe, in this case, john and alex. How should my sql-query look like?
View Replies !
How To Find Records That Are NOT Referenced In A Join Table?
Table structure... CREATE TABLE category ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL ); INSERT INTO category (id, name) VALUES (1, 'CSS'), (2, 'HTML'), (3, 'XML'), (4, 'Javascript'), (5, 'PERL'); CREATE TABLE category_j_article ( category_id TINYINT UNSIGNED NOT NULL, article_id INT UNSIGNED NOT NULL, PRIMARY KEY (category_id, article_id) ); CREATE TABLE article ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL ); Let's say I want to find all articles that aren't filed under multiple categories? For example, if I want to find articles that aren't filed under the CSS and HTML categories?
View Replies !
Having MySQL Find Only Orders With Multiple Rows In Another Table
In PHP I could just do a querey of all ordernumbers and within the WHILE do a querey of orderitems where the ordernum equals the original querey, and then do what I need IF numrows > 1. But surely there's a way to maybe do that in the mySQL querey? Some way that might be more efficient than a querey of EVERY order? If there's simply a name of some topic in mySQL that might apply, just tell me to look that up. For example: "Take a look for 'distinct',
View Replies !
Can't Find MYD
I cannot access any tables of a database which previously worked fine and suddenly broke. I get Error 1017 errno: 2 from both mysqlcc 0.9.4 and mysqlc. The system complains that it cannot find any of the .MYD files, although they are clearly there. The file system is perfectly analogous to other databases in the same tree which work ok. I can write a program to recover the MYD files in plain text form if I knew the file structure, but I can't figure it out.
View Replies !
Different Way To Find Min
I have the following query (courtesy ip2nation): SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < INET_ATON('some.ip.address') AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1 Given an ip address, it returns the country that has the closest ip to it. Now, I need to join these 2 tables with a table (logs) which contains ips (FROM ip2nationCountries c, ip2nation i, logs l). If I simply replace "WHERE i.ip < INET_ATON(l.ip)", my query is messed up by the LIMIT statement, which will only return only one ip.
View Replies !
Find Row
how do you use the function: mysql_tell_row I just get the error: Call to undefined function: mysql_tell_row()
View Replies !
Find All Possible Combinations Of A Set
For a match schedule I would like to find all possible combinations of teams playing home and away (without teams playing to themselves of course). I now the simple version works something like this: For a (very) simple table containing three rows like this:
View Replies !
Find Value Not In Between Values.
I have a SQL question I am having trouble with. We have a table, with a column that contains a code. These codes start at '00001', '00002', '00003', '00004'... and so on. If the record containing '00003' as it's code is deleted from the table, is there an SQL statement that can be used to find the first missing record. I guess what we are looking for is the lowest value of CODE not in the list of values between two values, such as in the case '00001' to '99999'.
View Replies !
Find Mysqladmin
I am new to mysql. Please let me know where can I find mysqladmin in the linux file system. I have mysql-3.23.41-2 rpm installed. Most of the mysql bin files are installed in /usr/bin, but I could not find mysqladmin.
View Replies !
Find Row Size
I need to find the record size when i m fetching the data from mysql table . example: id|name | details| 1 |vetri | b2w | 2| peter | dog | query: SELECT * FROM example where id='1'; ------- When i execute this query i need to find the size(in bytes) of the resulting output.
View Replies !
How To Find Server
i have installed sql server management studio express but there is a problem when I open it. it says no server connection. so i don't know how to make my pc a server. would u please tell me the process to make pc a server for sql server 2005.
View Replies !
Where To Find Shell>?
i am trying to do database replication in mysql...and as i read the manual, aside from the statements i need to execute in the mysql command client... i read some statements that was executed in shell> and im wondering where can i find this...im so sory for this simple question, im just a beginner in this field and im just studying...
View Replies !
How To Find The Record No?
how to find the record no.. ie what is the location of the particular record in the database.. i dont hav any s.no column in my database.. if a particular record is retrived from the table i have to show its position in the database in my page. can anybody help me? is there any query for this?
View Replies !
Find A Row And Inserting
I have a table with username(pk), password, description. After the user auths, i store the username in a cookie. Based of that cookie value(username), I want to find the appropriate user in the table and modify the description field of that user. How do I go about fetching a row and then modifying a specific column in that row(description).I basically want to find the username based of the cookie and modify the description field of that user.
View Replies !
Find New Entries
I'm having problems finding new entries in a table. I have tried using <> ANY .... but this seems not to work. I didn't think it would be so difficult. Are there any good suggestions
View Replies !
Find Occurrence
I have a table containing daily dates of working days through several years. What I would like to have is to be able to choose a date and then find its corresponding date one year later. An example would be: the date 2000-01-01 and its corresponding date: 2001-01-01. The problem is that 2000-01-01 wasn't a working day and so I will not be able to find it in my table. Is there any trick for telling mySQL to find a corresponding date in a certain interval that is also actually found in the table?
View Replies !
Find Top 10 Of Entries
I can't think of a way how to write mysql. I have a table that contains id, url and thedate. I want to be able to find the top 10 that url has been visited. I want the result to be like 1. google.com 56 2. yahoo.com 45 3. google.com/blah 34 4. etc...
View Replies !
How Do I Find Out The Row Size In Kb?
how do i find out the row size in kb? is it possible to automatically insert the size I have a mail table and have the field is there a way of automatically creating the value on insert by resetting the values of the field?? Code: `messagesize` int(255) NOT NULL default '0', but am unsure how to do this any ideas I have thought that maybe i have to call the last inserted ID get the value then do an update of that row but am unsure of how to do this in php.
View Replies !
Find Records
I need to delete all "question" records in a table whose NEWEST related "answer" record is 30 days old (purging the old stuff). The best I could come up with is: delete from questions where questions.id=answers.question_id and max(answers.creation) < date_sub(curdate(), interval 30 day) it returns no error, but doesn't delete anything
View Replies !
Find Members
I have 3 tables: members, groups, and group_converge. Members contains a userid and some information I need, groups contains the groupid and name, and group_converge tells me which members are in which groups using a memberid and a groupid. I'm trying to generate a list of people who are able to be put in X group. I can do this using a loop, by first getting a list of members, then checking which ones are in the group and eliminating them, but that involves more queries than I want to use. I know there's a way to narrow this down to one query.
View Replies !
Can't Find Server
I have installed MySQL 5 on my win XP to use with my apache, but... When I use winMySQLadmin 1.4 There is no databases... Under the "Environment" tab under "Server" there is no info whatsoever...Its like it cant find it... In my system_ini file it is all the right paths... I then downloaded "MYSQL administrator" but it cant connect to it using localhost.
View Replies !
Find Xy Points
I have successfully imported an ersi shapefile into a spatial indexed mysql table. I would like to use that table to store the polygons. I then have a 2nd table that stores x,y data of points. I need to select the rows of the points that fall within the polygons from the 1st table and give the selected row the polygon id in which it falls. I use php to run my queries. Should I use the spatial index table or is there an easier way to conduct this query?
View Replies !
Can't Find Rows From VB6
I have a DB with several tables. When I use the MySQL Query Browser to query the tables, I get all of the rows. By using a program written in VB6, I get the rows in all of the tables except for 5 of them. That is, if I run a SELECT on those tables, the resulting recordset is empty. Strangely, if I run a SELECT in VB6 on a view that has an INNER JOIN on 4 tables (3 of the tables mentioned above plus another one), I get the correct result. And if I run an INSERT in VB6 on one of those tables, the new row is correctly inserted (but I can't retrieve it later with a SELECT...). I tried with several users, including root, but the result is always the same.
View Replies !
Find The List Of X For Which There Is Only One Y?
I am trying to find an SQL query that will return a list of items in column X that only appear once, or more precisly only have 1 corresponding Y (X is key). For example: Table (R): X | Y ----- 0 a 1 a 1 b 2 c 2 d 2 e In this case, I would expect to only get back "0" because it only has one corresponding Y value (a), whereas "1" has two (a & b) and "2" has 3 (c & d & e) ... etc....
View Replies !
|
TRACKER
