Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this:
[news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid]
One entry in 'news' can be attached to many different categories via 'newscategory'.
What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,
View Complete Forum Thread with Replies
Related Forum Messages:
SELECT Rows With Field Different From Previous Rows
I have a table (sens_samples) with mysqlerature sensor samples taken every 5 minutes. Rows are: id (INT), sensor_id (INT), timestamp (DATETIME), mysql(FLOAT) and over_mysql(BOOL). I want to make a query to get ONLY the rows when over_mysql field changed from "previous" row value. I know that database rows are UNORDERED, but ORDER BY can "place" them in a given order. My query may be "syntactically" something like: SELECT timestamp, mysql FROM sens_samples WHERE "PREV_ROW".over_mysql <> over_mysql AND sens_id = 1 ORDER BY id; How can I get it using MySQL?
View Replies !
Groups
I have: col1col2 item13 item15 item27 item23 item14 item34 I'm looking for the result: col1col2 item15 item27 item34 --the highest col2 value for data paired with col1 --no duplicates in col1 --order does not matter for either col1 or col2 in the result The SQL I have now is: select col1, col2 from table1 group by col1 I'm not sure how to force it to group col1 using the highest value from col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if this is relevant, but table1 is made by joining two other tables together (one of which is a heap).
View Replies !
Group Of Groups
Basically, I have a simple group query... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` The query returns the number of records for each student. What I want is a query that produces a count of the number of students with a distinct number of records, that is, I want to group by COUNT(*) and return the COUNT(*) of the new group. My nieve atmysqlt... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` GROUP BY COUNT(*)
View Replies !
Groups For Members
My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list. Would this be a good way to do it?.
View Replies !
Order By Giving Two Groups
I have a new database table which contains name and url field. When I order by name I get two groups, the first being those without an entry in the url field, the second with an entry in the url field, each grou seperately ordered correctly. In a possibly related problem with the same table displaying in a web browser through php, there are again two groups diplaying when ordered by name. If a member of the first group is displayed and resubmitted without change, it then appears in the second group. I tried doing an export / import to refresh the data, but it didn't help. Any ideas for causes and or solutions?
View Replies !
Two Servers On Windows And My.cnf/my.ini Groups
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
View Replies !
Return Groups For Each Month
I would like to return monthly reports with a single query.For example: January 15 Purchases 5 Refunds 6 Exchanges February 20 Purchases 4 Refunds 2 Exchanges The above data has about 60 records. The query has to group by month and then again by transaction type. Here is what the record would look like TransactionID - primary key TransactionDate - Date Transactiontype - integer
View Replies !
Counting And Ranking Within Groups
(commas are in place to show data seperation and are not needed in the result set) Name Won Julio, 2 julio, 3 julio, 0 Ron, 4 Ron, 2 and the results should be based upon wins sorted descending Name Won Rank Julio, 3, 1 julio, 2, 2 julio, 0, 3 Ron, 4, 1 Ron, 2, 2
View Replies !
Don't Select Rows That Match Both Columns But DO Select Rows That Don't Match Both
I suppose it is because it is 2:30 AM but I'm having a bit of trouble figuring out the SQL I need to write. I am usually pretty good at this. Here's what I'm having trouble with. My app includes a Poll system built from scratch. Each question will run for one month. the month that the poll will run is kept in 2 columns in my table... runMonth and runYear. What i'm having trouble doing is selecting the rows that don't match both the current runMonth and current runYear. My Table... id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 4..............QD...................4......................2007 (current month and year) 5..............QE...................5......................2007 6..............QF...................6......................2007 I can simply get the question for the current month... SELECT * FROM table WHERE runMonth = 4 AND runYear = 2007; but if I try to get all others using SELECT * FROM table WHERE runMonth != 4 AND runYear != 2007 I get an empty result set. As I should because there is nothing there that the runYear != 2007 doesn't knock out. The result set I am looking for is id.............Question...........runMonth...........runYear 1..............QA...................1......................2007 2..............QB...................2......................2007 3..............QC...................3......................2007 *********************(remove runMonth4 and runYear 2007) 5..............QE...................5......................2007 6..............QF...................6......................2007 What am I missing?
View Replies !
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
SELECT With 1,000,000 ROWS
I have aproximately 1,000,000 rows and I would like to do some query. The first one is to get the number of row so I do : mysql> SELECT COUNT(*) FROM `Log`; +----------+ | COUNT(*) | +----------+ | 969129 | +----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; +--------+ | Nb | +--------+ | 969129 | +--------+ 1 row in set (1 min 20.99 sec) But like you can see it, it take a long with the WHERE clause. I use Pentium III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the computer but I'm not shure (I need more RAM ?).
View Replies !
Num Of Rows In SELECT
I am just wondering if it is possible in one query to select some rows using WHERE and LIMIT clausule, and get the number of rows I would get when i dont use LIMIT. if I have SELECT * FROM table WHERE column1='something' LIMIT 10, 15; i know it is 15 rows selected. But i need to know how many rows are in: SELECT * FROM table WHERE column1='something'; I know, there is a way.. just to do both SELECTs.. and retrieve data from the first one, and num of rows from the another one.. But I just dont want to make two SELECTs if there is maybe some solution how can i do it in one query.it for page with forum where i want to show users how many threads are in database, but at one page i need to show just 15 threads...
View Replies !
Select All Rows
I'm trying to figure out how to select all rows created during the current month. So, for instance, now I would want it to return ALL rows created during May. Next month, it should show every row created in June 2005. In my database, I have the field "date," where a date is stored in mm/dd/yy syntax, and I also have a "timestamp" field, which is created using PHP's time() function.
View Replies !
Select Rows With Max Value
Given a table like this: CREATE TABLE IF NOT EXISTS `people` ( `name` varchar(10) NOT NULL, `age` smallint(5) unsigned NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `people` -- INSERT INTO `people` (`name`, `age`) VALUES ('bob', 13), ('john', 25), ('mike', 25), ('steve', 8), ('sue', 13); How would I select only the rows with the highest 'age' value? In other words, because "john" and "mike" are the oldest, I want only their rows.
View Replies !
Select Only The First X Rows
Is i posible to select only the first X rows? Or row X to X? ID want help me, it will be diffrent from one day to another... Now it might be ID 1 to 3 but later it is ID 6 to 3 on one set and 0 to 3 on another
View Replies !
Select All Rows Except One...
This is a dumb question. These are my rows with three columns. id | Firstname | Lastname | ---------------------------------------------------- 1 | John | Smith | 2 | Jill | Smith | 3 | Joe | Smith | 4 | James | Smith | 5 | Jello | Smith | 6 | Joseph | Smith | . . . n | xxxxxxx | xxxxxxxxxx | How would I select all rows except the last row without using PHP to manipulate the LIMIT keyword? SELECT * FROM tbl ORDER BY id LIMIT 0, (SELECT COUNT(*) FROM tbl) - 1 course the above statement doesn't work... but you get the point.
View Replies !
Select Duplicate Rows
In a table, i have records about phone number and stateID, sometimes there are rows that have the same phone number with different stateID (assume stateID has value from 1 to 50, or 100), I want to find out duplicate phone numbers with stateID value of 100 (those same phone numbers also contain stateID of value between 1 and 50). Can someone please tell me what would be the query for selecting this?
View Replies !
Select All Matching Rows
I need to look in table 1 and pick all those records not in table 2.I know that table 2 has 51000 records and table 1 has 65000. I want to see the 14000 in table 1 that doesn't have a match in table 2
View Replies !
Select Multiple Rows With The Same Id
So I have this table: table_name id_one ---- id_two 1 ---------- 1 1 ---------- 2 1 ---------- 3 2 ---------- 1 2 ---------- 3 I want to select all of the id_one rows where the id_two is '1' and '2'. The desired output is: id_one 1 1 Later I might use SELECT DISTINCT to produce the following: id_one 1 So far I have: SELECT id_one FROM table_name WHERE id_two IN ('1','2') But this selects all the id_one rows where id_two is '1' OR '2'. I want the rows where it is '1' AND '2'.
View Replies !
Trying To Select Rows With Max Date
This is my query: SELECT i_dest,i_rate, price_1, effective_from FROM Rates r ORDER BY i_dest; I have multiple entries for each "i_dest". Every entry does have a unique i_rate (the younger the entry the higher the i_rate since it's the tables primary key with auto-inc). Also every entry does have a unique effective_from datetime value. I would like to select all distinct i_dest definitions having either the highest i_rate value or the highest/youngest datetime in effective_from. I have tried something like this: SELECT i_dest,i_rate, price_1, effective_from FROM Rates r GROUP BY i_dest HAVING MAX(i_rate); Unfortunately this does not work, i.e. it does not result in only the rows with the highest i_rate. If I go like this: SELECT i_dest,max(i_rate), price_1, effective_from FROM Rates r GROUP BY i_dest; then I do see the highest i_rate BUT the price_1 and effective_from entries are those from other rows with a lower i_rate (and same i_dest obviously).
View Replies !
Select Rows On MAX(date)
My application inserts rows with a run_date - often all the other data will remain the same. I need to count only the rows with the latest run_date (and other WHERE clauses defined by the user). Table looks like: |rundate |vendor|users|vertical| |2009-02-01|blah |100 |chemical| |2009-02-02|blah |100 |chemical| So my query needs to return '1' as only the latest run_date should be included. I've played with MAX(run_date) but I don't know how to use it as part of a WHERE clause. e.g. SELECT vendor, COUNT(*) FROM results WHERE vendor = 'blah' AND MAX(rundate);
View Replies !
Select Rows Not In Second Table
I want to select all records in a "User" table that are not in the "Groups" Table. Select users.USERID, users.NAME From users Outer Join groupmembers ON users.USERID = groupmembers.empid Where groupmembers.groupid <> '9' In therory this would return me all users not in group 9, but I am getting an error in my SQL... I dont really understand Left - Right - and Outer joins but I have tried all three... I could do this in 3 queries - Create a Temp Table - Delete Records w/ 9 in group ID ... Select remaing records.... But there must be a better way...
View Replies !
Select 5 Rows Per Column Value
ID | NAME | EMAIL | CITY | How could i select 5 rows for each city value? In other words, if there are rows containing St. Luis, NY, LA - I want to select 5 users from St L, 5 from NY and 5 from LA.
View Replies !
Select The First Letter Of Each Rows In A Db
I want to be able to select only the first letter of each row in my table. I want to select only DISTINCT row so i would have as a result, an array of letters. For example, if i have 25 rows starting with the letter 'A', well 'A' would come out only once.
View Replies !
SELECT Results After X Rows
I am trying to run a league creation script using php and mysql. I was thinking that i could do it by getting every user into one column and then pair them off by starting from the second result found and place that with the first user in the first fixture row. For the next fixtures i would place the 3rd result found in a row with the initial users. This way i make sure they dont play each other twice and each fixture created in unique. Is their a mysql function to do a select query but dont have it start outputting the results until it reaches a certain row from the results? I will also need it to go back to the first result found once it reaches the end of the results.
View Replies !
Select Every 3 Rows From A Table.
I have a bit of a problem, Iam doing a match results page with data from mysql. My table Name: JuniorMatchResults Data Colums: id, date, venue, position (1,2 or 3), weight. What i wanto to do is output this information in my website such as you would have the 1st 3 positions outputted in a table, the the next set of 3 in a table and so on.
View Replies !
Is That Possible To Select Range Of Rows?
I use simple query right now to select rows from database: SELECT * FROM cities WHERE country = '$country' ORDER BY city But what if I need to select not all rows (from 1st to 800th, for example), but only rows from 70th to 140th? Is that possible to select this range with MySQL query?
View Replies !
SELECT And Exclude Rows
SELECT * FROM myTable ORDER BY DATE DESC The result of this query is: 17/08/2008 17:11:38 CERTALDO BOSCO 17/08/2008 16:58:01 CERTALDO BOSCO 17/08/2008 15:05:27 CERTALDO BOSCO 17/08/2008 14:48:31 CERTALDO BOSCO 17/08/2008 14:43:38 CERTALDO BOSCO 17/08/2008 09:43:56 SUBBIANO CAPOLONA 17/08/2008 00:16:13 ROSIA DOCCIO...
View Replies !
Select With Dummy Rows When Grouping?
If I select from a table and, for example, group by the month or day to determine how much activity there is day to day or month to month, it will only return months and days with a record and leave out any month or day without any records making it apear at a glance that every day or month has activity which forces me or whoever to have to look at the date column and actively look for spots where a day or more elapses without a single row in order to determine when nothing has happened. Is there any select method for grouping on something like a month and to have it output a dummy row for missing days or months without having to write a program that makes an individual select for each day?
View Replies !
Select With Dummy Rows When Grouping
If I select from a table and, for example, group by the month or day to determine how much activity there is day to day or month to month, it will only return months and days with a record and leave out any month or day without any records making it apear at a glance that every day or month has activity which forces me or whoever to have to look at the date column and actively look for spots where a day or more elapses without a single row in order to determine when nothing has happened. Is there any select method for grouping on something like a month and to have it output a dummy row for missing days or months without having to write a program that makes an individual select for each day.
View Replies !
Select Distinct With Multiple Rows
SELECT distinct name, id FROM `raffaello_ties` WHERE `advertisercategory` like "Cufflinks and Tie Clips" order by name limit 0, 200 it works, but it doesn't select distinct names. if I changed it to just "distinct name" (and leave out id) it works fine. how are you supposed to use select distinct with multiple rows?
View Replies !
Select Rows Only Created LAST MONTH
i want to run this below command, but only look at records from the previous month and where ACTIVE = 0 MYsql> SELECT user,ROUND(AVG(overalldone), 1) AS average FROM data GROUP BY user ORDER BY average DESC;
View Replies !
Master List Of Select Rows
I am creating a database as part of a service record for items. When an item is serviced, an entry is added to the database with the item serial number and some other info. That all works great. I also want to create a master list of all items. If I use the old "SELECT * FROM database WHERE 1" query, it will display all the rows in the database. Is there any way to retrieve the last row for each item number, instead of displaying all rows for all items? It seems I have worked myself into a corner with this table setup. If it can't be accomplished the way I have it set up, is there a better way to structure the table? I thought about creating another field "type" and have it be either "parent" or "child" and then only display the "parent" rows in the master list. That would involve two queries when updating an item's status (adding a new row and updating the "parent" row for that item), and seems to be less convenient. This seems like a common application so there has to be another way.
View Replies !
SELECT Would Examine More Than MAX_JOIN_SIZE Rows
I started getting the following error message: Error Number: 1104 The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay I tried running the query: SQL_BIG_SELECTS=1 but it doesn't change the error?
View Replies !
Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?. bash-2.03# ./mysql -u test Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 to server version: 5.0.18-max Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Code:
View Replies !
Select Rows With Common Column
I'm trying to figure out a simple query, and just can't manage it. I have table like so: Name-----------Programme-----------Channel Dave Simpsons Sky One Dave 24 Sky One Dave Family Guy BBC2 Nicola CSI Hallmark Nicola Friends E4 What I want to do is select everything from the table. But, I would like to group them so that the rows returned look like Row 1:Dave, Simpsons, Sky One, 23, Sky One, Family Guy, BBC2; Row 2:Nicola, CSI, Hallmark, Friends, E4; Is this possible, or just plane stupid? I take it I would have to create a temporary table?
View Replies !
Select Only Rows Of 2 Fields That Does Not Repeat
I would like select only rows where registerNo AND entryid that does not repeat. Example, from the table only one row of 'registerNo=1 and entryid=sqbiiphiu1' and the last 5 rows will be selected. I'm using php to build this. i'm not sure if distinct can help. this is wat i thought of $query = "SELECT DISTINCT * FROM fn_vote WHERE registerNo = '".$registerNo."' AND entryid='".$entryid."'";
View Replies !
Select Distinc Rows At Random
I have been pulling my hair over how to select unique rows at random. I have a table as follows: id | title | description | company_id -------------------------------------------- 1 | Title 2 | Descripiton1 | 23 2 | Title 3 | description 2 | 45 3 | title 4 | description 3 | 23 4 | Title 5 | descrioption 4 | 34 5 | Title 87 | description 3 | 23 6 | title 35 | description 45 | 34 ------------------------------------------- I want to be able to select 3 rows at random but the company_id should be unique. I have tried: SELECT title, description, company_id FROM table_name GROUP BY company_id ORDER BY RAND() LIMIT 3 The problem with this is that it's giving me the the same results all the time and it's not random.
View Replies !
Select Rows From Yesterday/Last Week
PHP //yesterday WHERE (month(articles.date) = month(now()) AND dayofmonth(articles.date) = (dayofmonth(now())-1) AND year(articles.date)=year(now())) //last week WHERE (week(articles.date)=(week(now())-1) AND year(articles.date)=year(now())) This works all good and well, EXCEPT: When the date is the 1st for yesterday, need to go back a month. What is a better way to write the query to grab yesterday, even if yesterday was in the previous month. What is a better way to write the last week, even if last week was the end of last year?
View Replies !
|
TRACKER
