Selecting Records, Then Summing Parts Of A Column, Then Sorting By That Column...
I’m keeping track of baseball stats, and each row represents one line of stats (from a box score) for one player of a single game. Because of this, a single player may have multiple rows in the table. I want to cumulate each player’s stats (so they’ll be one row per player) and display as output, which isn’t a problem. Then I want sort by a certain stat, but by now I’ve already looped through the table, so I can’t sort using a mySQL query at this point. I tried first putting values into an array in a previous project, but that became extremely complicated. What’s the best way to approach this?
View Complete Forum Thread with Replies
Related Forum Messages:
Summing A Column?
PHP session_start();ob_start();$item = $_GET['item'];$user = $_POST['username'];$pass = $_POST['password'];mysql_connect('localhost', 'mysqluser', 'mysqlpass');mysql_select_db("mysqldb");$result = mysql_query("SELECT * FROM users WHERE user = $user AND password = $pass");$row = mysql_fetch_row($result);if ($row <= 0) { header("Location:item.php?item=failed");} else { $_SESSION['login'] = 1; $_SESSION['user'] = $user; header("Location:item.php?item=$item");}ob_flush(); I think it should work, but it's repeatedly telling me Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /my/directory/html/item.php on line 10. I can't figure out for the life of me why it won't work. It connects properly, I've been doing SQL queries all over the site. The variables POST correctly, and the SQL variables are all correct as well.
View Replies !
Selecting From Column A Where Column B Matches Column A Twice?
Here's a table called Creatures containing Creatures and EntryIDs: Code: [Creatures] Creature EntryID ================== fish 100 cat 100 fish 200 bird 200 pig 300 bird 400 I would like to select all EntryIDs that contain both "fish" and "bird". From the above table, "200" should be returned, seeing as both fish and bird are the only rows that both use the same EntryID. How can I do this? Do I need to use Group By or Left Join or something?
View Replies !
Sorting Column With Values A-Z Before AA-ZZ
I need to sort based on a column that has values as follows: row ---- A B C D AA BB CC DD etc..... i need the data to be sorted and displayed as A-Z then AA-BB. I saw this thread: http://forums.mysql.com/read.php?100,69968,70410#msg-70410 is there something similar, that would allow me to sort this way.
View Replies !
Sorting Column; NULL Last
if there is a better way to sort a column with NULL values being last instead of first? Right now the best I can come up with is: Code: ORDER BY IFNULL(smt.country_type, 'ZZZ') Unfortunately that seems like an ugly hack. I don't want to use ORDER BY smt.country_type DESC because it reverses the non-null results. Is there a better way? PostgreSQL has a NULLS { FIRST | LAST } keyword that would be perfect this
View Replies !
Selecting Only Parts Of The Content Of A Field?
Is it possible to select only parts of of the content of a field with MySQL? For example if I have some text fields in a table that contain HTML markup in them and I want to only select what is contained within the first <h1> tag from each field. Note that I don't want to just select the fields that have <h1> in them, but select only the text within that tag and not anything else contained in that field. Is this possible?
View Replies !
Sorting By Number On A VARCHAR Column
I have a table containing a field ref_number Example column contents: 1 2 3a 3b 5a 5b 6 ... It's a number generally but may also have a or b or c etc tacked on the end. So I had to make this field a VARCHAR but now when I order ASC on this, it doesn't display in the order expected i.e. 1 10 11 19 2 So, this is my attempt to get around the issue: SELECT *, RIGHT(CONCAT('00000',ref_number), 5) as number_sort FROM item ORDER by number_sort ASC;
View Replies !
Sorting By A Column With Letters And Numbers
i have a column called `card_number` which the values inside are listed like "C1", "C2", "C3", etc etc... the problem i have is when i do this SELECT * FROM `cards` ORDER BY `card_number` ASC .... it returns... C1, C10, C11, C12, C2, C3, C4... any way to make it do C1, C2, C3, C4, ... C10, C11, C12.... ??
View Replies !
Can I: Export Column, Optimize & Fill New Column With Value To Original Column?
I have a database with over 40,000 rows and 28 columns (learned how to import large files by changing php.ini!). Using Excel, I am able to: (A) copy and paste original column (e.g., SIZE_TEXT) (B) ALONG with each records' UNIQUE_KEY, (C) sort SIZE_TEXT column, (D) filter for unique values, (E) fill new column with its SIZE_TEXT__KEY, and then (F) import the new SIZE_TEXT_KEY value into the database by creating a new column or pasting over the columns original value. However, I think this can be performed, with less potential for error, using phpMyAdmin, but I haven't found anything demonstrating how to perform these actions. I searched the terms optimization and normalization. I am hoping one can use phpMyAdmin to: 1) create a new table with export column of the original table (SIZE_TEXT_TABLE), 2) sort new table for duplicates and show only unique values, then 3) import the column results into each Unique Records original column (SIZE_TEXT) 4) link new column values to Foreign Key (the Parent Table's SIZE_TEXT_KEY)
View Replies !
Selecting Another Column Based On Max()
I've got a funky query I'm trying to set up. I've got this poll answers database, and each row has a qid that matches to a question. So I can group answers by the question they correspond to. Now I'm trying to select the total votes and the answer with the most votes for each question. The total votes part is working fine, but I'm having trouble with the winning answer part. I've got a MAX(votes) that works well, and finds the answer with the most votes for every question, and returns its votes. What I want to do is select the `answer` column (the one that houses the actual text answer) of the answer with the greatest votes (the winner). Code:
View Replies !
Selecting MAX Value Of VARCHAR Column
MySQL Version = 4.0.17 I am having some troubles with a db and php written by another developer - unfortunately I do not have the luxury of altering the way in which this has been implemented - basically I am just trying to patch this up! Basically its a table of appointment time slots. The first column is VARCHAR(10) and stores the ApptID eg A9826 Each time an appointment slot is created, the PHP script runs the following SQL: SELECT MAX(ApptID) FROM AppointmentDates The script then gets the substring of the result so that it only has the numeric content - eg A9826 becomes 9826. The script then adds 1 to this value in order to create the next ApptID (dont ask me why its been implemented in this manner! ) Basically, the appointment ID has now reached 10000+ and when the SQL runs, it always returned 9999 as the max.....Therefore making the next ApptID = 10000 - creating a duplicate key error when attempting to insert... My question is - is there a limit on the value that MAX can return? Or is there some other explanation for this?
View Replies !
Selecting Certain Dates From Date Column
I wonder how this can be done, data is like this +---------------------+ | whenstamp | +---------------------+ | 2005-02-21 12:27:54 | | 2005-02-21 12:27:54 | | 2005-02-21 12:27:55 | | 2005-02-21 12:42:55 | +---------------------+ 4 rows in set (0.00 sec) mysql> I tried something like this :: $this_month = mysqli_query($dbcon,"select date_format(whenstamp,'%d-%m-%Y') as datex from logs where %m = '02'"); Basically I want to retrive records corresponding to the current month, I know I have hardcore '02' in their but that was just for testing and getting started.
View Replies !
Selecting Based On Matched Column?
Lets say I have table with two columns zip_work and zip_home. I'd like to construct a query looking something like this: SELECT {here is the problem} FROM myTable WHERE zip_home=99999 OR zip_work=99999 GROUP BY zip_home, zip_work LIMIT 100; In response I'd like get two column table with zipcode as the first column and COUNT() of matched users. The idea is to know how many users are working or living in 99999 zipcode.
View Replies !
Selecting Values Based On Column
I couldn'y find solution to this on forum so here it is : sample table : id | name | value 1 | x | y 1 | z | v 2 | x | y i would like to select id where x=y and z=v. so in this case result should be : 1 (2 is not in the result coz it doesnt match x=y). How to create such a query?
View Replies !
Combine Records Of A Column Of The Same Table
i have all my portal contents on the SQL Database in the following manner: Node_id Contents 12 this the first page 12 welcome to the portal 12 this is some sample data 13 this the second page, 13 sample data when a page is being loaded, depending on the node_id contents are retrieved and displayed. Now i need a query which would combine all records of the same Node_id (page) and display as one record. Eg: Node_id Contents 12 this the first page welcome to the portal this is... 13 this is the second page, sample data
View Replies !
View Records In A Column/field
I used to use phpmyadmin and loved the fact i could just click on a field and hit 'browse' and it shows me all the records in it. I just downloaded all the GUI tools for mysql 5.0 and I love them and don't think i need phpmyadmin anymore. I have problems though with one thing. How do you browse through all the records in your db fields? I am sure this is possible with one of the new GUI tools I downloaded from this site: MySQL Migration Toolkit MySQL Administrator MySQL Query Browser MySQL Workbench I am probably just missing the obvious. If anyone knows how I can browse all of the records using one of these tools,
View Replies !
Return Records Only If Other Column Is Largest
I have an interesting situation here that I am having problems figuring out. The only data I have coming into the query is the field_parent_nid. I want to then grab the nid on the same row as the field_parent_nid only if the vid (version ID) that is linked to the nid is the largest value. So if you check the image you can see that I have highlighted the pertinent rows for this example. If I came in and wanted to return the nid where field_parent_nid = 114 I would want this not to return anything since the vid of this is only 443, yet there is another vid of 447 with the same nid a few rows down. If I came in with field_parent_nid = 154 I would want the nid to be returned as even though it has vids of 439, 440 and 447, 447 is in fact the largest vid therefore this one row with the largest value should be returned.
View Replies !
A Date Column That Auto-updates When Any Column In The Row Is Updated
Let's say I have a table with several columns. I would like to add a column called "date last updated". How this would work is that any time one of the other columns in that row was updated, the 'date last updated' column would update. I *know* how to do this with PHP, but here is the special part... Is there a way to have this automatically just within MySQL, so that EVEN IF if update a column manually through the command line or PHPmyAdmin, the 'date last updated' field updates.
View Replies !
Selecting And Sorting
I have a query: mysql_query("SELECT * FROM table WHERE code IN ('G1','D36','M17')") I want to be able to sort the results in the same order as they are specified in the above, i.e G1-D36-M17. However, since I entered them in the database in the order D36-G1-M17, that is the order in which they are displayed by default. Is there any way I can specify which rows I want to pull from the database AND the order in which I want them displayed, at the same time? If not, is there any other way I can get them to sort in this order without adding an index to the database?
View Replies !
Selecting And Sorting With Different Columns.
I am creating a small search engine for a site that reviews other sites. My DB structure is: TABLE `sites` ( `ID` int(11) NOT NULL auto_increment, `site_name` varchar(100) default '', `site_description` varchar(255) default '', `type1` varchar(100) default '', `type2` varchar(100) default '', `type3` varchar(100) default '', `type1_rating` int(4) default '0', `type2_rating` int(4) default '0', `type3_rating` int(4) default '0', PRIMARY KEY (`ID`), FULLTEXT KEY `forsearch` (`site_name`,`site_description`,`type1`,`type2`,`type3`) ) TYPE=MyISAM AUTO_INCREMENT=483 ; Assuming that I have 2 rows on my DB with the following data: SITE1 site_name='mysite' type1='car' type2='aircraft' type3='construction' type1_rating='23' type2_rating='40' type3_rating='34' SITE2 site_name='coolsite' type1='jet' type2='bus' type3='car' type1_rating='35' type2_rating='22' type3_rating='66' When I search for "car" it will find that the two sites match the query. That's fine. But how do I sort the results (based on the 'type_ratingX') if the value found is in different fields? On SITE1 I will use type1_rating and on SITE2 I will use type3_rating to sort!
View Replies !
Selecting And Sorting The Last 5 Entries.
I have a table with songs, for each song i have ID,title,duration The ID column is auto_increment and is unique. So when i want to select the last 5 entries i do a "SELECT * FROM songs WHERE 1 ORDER BY ID DESC LIMIT 5" But what if i want to sort those last 5 entries by song title ? I was able to do that with subqueries, but on my current server i have older version of mysql that does not support subqueries.
View Replies !
Update Part Of Column Into Another Column
I'm looking for a way to update a SQL column with a portion of info from another column in the same table. example of a sql command -------------------------- UPDATE table1 SET table1.columnname1 = table1.columnname2 FROM table WHERE blah blah blah Here's the thing... I only need a portion of the data found in the source column. I'm not sure how I would do this then. for example, the database has countries and states combined into one column like this 'US-DC', 'US-CA', US-FL', etc. I want to separate these into two columns, a country column and a state column.... and I dont want to go though all the results and do this line by line. How would I write the SQL command so that it puts just the country in the country column, and puts just the state in the state column, and it omits the dash all together. any ideas?
View Replies !
Order By On Column And Reoder By Another Column
im using php and mysql, but i would like from the sql statement to get the results of a query order by a column and then reorder by another column that is: i want to select the last four entries accoding to the date. SELECT * FROM table ORDER BY Date DESC LIMIT 4 but I also want to order those 4 entries i got, according to their values at the order column like this SELECT * FROM table ORDER BY ordercol ASC LIMIT 4 how can i put this two together? i thougt of SELECT * FROM table ORDER BY Date DESC, ordercol ASC LIMIT 4 but it gets the last four entries and then if two entries have the same date it orders them according to the ordercol, how can it do it?
View Replies !
Selecting Records Belonging To Multiple Records Through Join Table
I have is 3 tables, 'categories', 'levels' and 'categories_levels', the 'categories_levels' table is a join table, with the `id`, `category_id` and `level_id` fields. Categories can belong to many different levels, so records in the join table might look like this (assuming that the category has an ID of 5) id, category_id, level_id 1, 5, 1 2, 5, 2 3, 5, 3 So you can see that category 5 belongs to 3 different levels (1, 2, and 3). On a web page, I've got a multiple select box, which allows a user to filter the categories depending on which levels they belong to. What I need a query to do is use that join table to find all categories which belong to the selected levels. Now for the tricky bit. Only the categories which belong to every single selected level should be displayed. So in the example above, if I select level 1, then category 5 will be displayed, but if I select levels 1 and 4, then category 5 should not be displayed, because although it does belong to level 1, it doesn't belong to level 4 as well.
View Replies !
Sorting Records
I have a table that holds the monthly returns for different portfolios. The fields in that table are: PORTFOLIOID, DATE and RETURN. A typical record would be 1----31 Jan 2003----2.3%. Another record could be 1----28 Feb 2003----1.5% Another record could be 32----31 July 2002---3.5% etc I need a query that gives me the top 10 portfolios ID order by the compounded return between two dates (these dates should be variants) so that I can ask for the 10 best performing portfolios between say March 2001 and Jun 2004
View Replies !
Get Top 10 Records By Sorting Two Fields?
I am currently trying to get the top 10 records from a Highscore table for fastest time. The table has 3 fields: name, minutes, seconds What would be the Select statement to select the top 10 records from the table based on the fastest time (e.g 0:10, 1:30, 1:31 ...)
View Replies !
Sorting UTF8 Records With German Umlauts In MySQL 4
I have some records in a database that uses utf8 as character set and I am using MySQL 4.1. How do I sort results which are utf8 data with german umlauts? Using the SQL query SELECT memberid, lastname FROM member ORDER BY lastname does not sort the result after german umlauts. Any idea how I can sort the utf8 results containing german umlauts?
View Replies !
Selecting All Records
How would I go about selecting all records in a table with a date field of "date" -- "0000-00-00", "yyyy-mm-dd" -- that are 3 days older then the current date?
View Replies !
Selecting Known Records
Say I have a big list of IDs of the records I want to select. What is the best way to do this? 1. A select statement with multiple OR statements eg. SELECT * FROM table WHERE ID = 2 OR ID = 5 OR ID = 10 etc... 2. Multiple separate mysql queries each selecting an ID each. 3. A select statements like obove but all the queries sent in a single query using union. I dont know how important it is but with lots of queries a second it could make a difference. If there is other better way please tell.
View Replies !
Selecting Many Different Records
I have table A_1 which has one of its fields called ID (a unique integer field). Now if I wanted to select let say the records with the ID matching these numbers 5, 7, 8, 9, 15, 22, 26, 29 how would I do this? I would imagine there is a better way to do this than using many AND statements to do this?
View Replies !
Selecting Records NOT IN
I have one table containing itmes each having its own id, and a another table containing some of these numbers in one of its columns. I need to to build a query that returns the itmes from the first tables whose numbers are not present in the second table. (SELECT t1.* FROM t1, t2 WHERE < the value in t1.item_id is nowhwere in t2.item_id >) How do I do that?
View Replies !
Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?
View Replies !
Selecting Date Records
What is the most efficient way to select records based on a field called NOW which contains a date or a datetimestamp I use: SELECT * FROM `TBL` WHERE DATE_ADD( `TBL`.`NOW` , INTERVAL 3 MONTH ) > CURDATE( ); Is this the best way? TIA [color=blue] > Nicolaas[/color]
View Replies !
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
View Replies !
Selecting From Two Tables Random Records
I have two trivia tables of questions and answers that I want to select from. I need to select 6 random questions from one, and 2 random questions from the other. I tried variations of: (select * from table1 order by rand() limit 6) union (select * from table2 order by rand() limit 2)
View Replies !
Selecting Past 12 Weeks Records
Im trying to grab the past 12 weeks records in a table. here is my code: $query1 = "SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate"; if($result1 = mysql_query($query1)) { $amount = 0; while($row = mysql_fetch_array($result1)) { $compdate = $row['compdate']; $compdate = substr($row['compdate'],8,2); $compdate = ltrim ($compdate, "0"); $days_array[$compdate] = 0; } } else { echo mysql_errno().": ".mysql_error()."<br>".$query1; } And here is the error: 1064: 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 'WEEK) ORDER BY compdate' at line 1 SELECT * FROM table2 WHERE compdate > DATE_SUB(NOW(), INTERVAL 12 WEEK) ORDER BY compdate
View Replies !
Selecting A Subset Of Records From Database
I have a table with column of Strings which can take any thing from the array [ABC, BAEFD, ADS, AFD, XYZ]. for examle the entries in the column can look Search IDTAG 1ABC;BAFD;XYZ 2BAFD;ABC;ADS 3AFD;XYZ;ABC;BAFD Now, the user can give any combination of strings like, ABC,XYZ or AFD,XYZ Now i need to retrieve the records, whose TAG has all these tags.i.e, First set should fetch me records 1 and 3 and second set should fetch me only record 3 What will the sql qurey look like?
View Replies !
Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database: ID Channel Description 41 Africa News Latest news from africa 42 Technology News Latest Tech News 43 PC News Latest PC related news 44 Africa News Latest news from africa I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique. How can i return only one of the Africa entries in the results while still having the primary key value returned as well? Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?
View Replies !
|
TRACKER
