Returning Decades From A List Of Years
I have a column containing a year and I want to return a list of the decades in that column. For example:
Year
--------
1987
1992
1914
1922
1923
1928
1984
would return:
Year
-------
1910
1920
1980
1990
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Getting The Age In Years
i want to get the age of a person in years with sql query in mysql. i have stored the date of birth in the database. I have tried Datediff function of mysql but it returns in Days not in Years. I would like to use some thing like this one. select * from tablename Where "datefunction that get me date in Years like this one Datediff(CURDATE(),date_of_birth) " between agefrom AND ageto.
Do Years Begin With 1901???
I just created a new table that stores the names of Washington State governors, with separate fields for the years they began and ended office. When I filled it from my CSV file, I discovered that all the years prior to 1901 defaulted to 0000. Is there a way to fix this, or should I just use INTEGERS instead of YEAR?
SQL For Finding Years In Dates?
What I need is a SQL statement that can find what years are in the database that is greater than last year. For example... I have a table full of dates ranging from 2001 to 2005.. I am straining my brain to figure out a SQL statement that can give me a list of years greater than last year.. In this case I need a list that has.. 2003 2004 2005
Adding Multiple Years
I have set up tables that increase input dates by either a month, quarterly, half yearly or annual using M,Q,H or Y in the default space, and ('M','Q','H','A') in the length/values space.However, I want to be able to add 5 years to one of the fields in the table.
Grouping Weeks, Months Or Years
I have a database full of log information that has a record for every hour of every day. So I have 24 records all with the same 'dateCreated' field and then the 'timeCreated' field changes for each hour of that day. So grouping the query results of the database into days is very simple, just a quick 'GROUP BY dateCreated' does the trick, however i now have the requirement to group that data into weeks, months and years. What is the best way of achieving this? is it even possible?
Storing Dates In The Billions Of Years
Since the datetime format only allows dates between 1000-9999 I won't be able to use this directly. I am considering using perhaps an integer or some sort of a date hack. Right now I am considering all options. One I came up with is storing an alternate number to be stored alongside which would designate the date's power. So '0' would mean the date is between 1000-9999, '1' would mean the date is between 8000BC-999... etc. I am wondering if this would be too much of a problem coming up with queries, however, so right now I am leaning towards a regular year integer and perhaps a secondary integer for the MMDD.
Mysql Nt Service Stopped Starting After 2 Years
i have been running mysql on windows server 2003 for the last almost 2 years (its a really early version of mysql 4 anyway) but now all of a sudden mysql service stopped starting, i can go to services, press start and then refresh and its off again! i have a feeling that someone deleted the core mysql table (mysql) as it is now totally empty. im not too good with my sql as i generally use mssql or whatever, infact mysql never really installed properly, the only user i could ever use was root but i didnt mind this so i just left it and it was fine for 2 years but now its just stopped all of a sudden and i cant get it back on. i have enabled the mssql php exension as a backup system but i cant transfer any databases across without enabling mysql.. but it wont turn on! i tried several exes in e:mysqlin but none got it on (well one of them made the little winmysqladmin light go on for half a second)
Using A Field, Which Is A Comma Separated List, As The List In An IN Comparison Test.
The following query only reads the first character of a comma separated list stored in a field of the records being queried. The varchar field in table d named "display" holds 1,4 The query " select * from d where 4 IN (display) " tests false. If I change the field "display" to 4,1 Then the query tests true. It is either testing only the first character, or is stopping at the comma. I need this to test true if the value is anywhere in the list, what am I doing wrong here?
Select * Using Id List V.fast, Delete Using Id List Taking Hours.
I have a table of about 20 million rows, with about 20 columns. I want to delete 10 of these rows. I have the ids already, and id is the primary key so it should be simple - i thought. This query completed in 0.27 seconds - select * from users where id in (3811150, 4470160, 479422, 2491277, 9443643, 4594310, 2556165, 12075603, 2533320, 45737); This next query, however, has been running for several hours - how can there be such a discrepancy? Should i kill it and retry? delete from users where id in (3811150, 4470160, 479422, 2491277, 9443643, 4594310, 2556165, 12075603, 2533320, 45737);
Full List Of MyTable21 And Some List Of MyTable22
data in myTable21 (id) country (1) Canada (2) France (3) Egypt (4) America data in myTable22 (id) city name (1) Montreol Jane (2) Paris Mary (3) Cairo Tom (4) New York Jane (4) Chicago Tom I have data in myTables like the above. The following code produces the following result. but I like to produce my target result. code1 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Tom' result1 (3) Egypt Cairo (4) America Chicago target result1 (1) Canada (2) France (3) Egypt Cairo (4) America Chicago code2 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Jane' result2 (1) Canada Montreol (4) America New York target result2 (1) Canada Montreol (2) France (3) Egypt (4) America Chicago code3 select myTable21.id, name, country, city from myTable21 left join myTable22 on myTable21.id=myTable22.id where name='Mary' result3 (2) France Paris target result2 (1) Canada (2) France Paris (3) Egypt (4) America How can I get the full list of myTable21 and selected values from myTable22?
Creating List Of People Not On List
I am using the following query to try to get a list of people not in a specific list. I can further restrict it by company name but I am getting duplicate entries if the person is listed on multiple lists. (It is further reduced by a clause to restrict by company, but that is not causing errors AFAIK) SELECT people.`First Name`, people.`Last Name`, people.PersonID, Company FROM people LEFT JOIN listtrack ON people.PersonID=listtrack.Person_ID WHERE (listtrack.List_ID<>4 Or listtrack.List_ID Is Null) If I remove the List_ID<>4 clause then I get people who are not on any list., but I want to be able to add one person to multiple lists, without running the risk of data duplication.
Models Where The Years Overlap (was "Query Help Needed")
I'm trying to formulate a query for the following example: Table: model_by_year model start_year end_year model_1 2001 2004 model_1 2003 2004 model_2 2000 2005 model_2 1998 1999 model_3 1998 2003 model_3 2002 2006 I want a query that will return all like models where the years overlap. In other words, I'd want to return model_1 and model_3, but not model_2.
Naming Years As "seasons"
In the football records database many of you have been so kind to push me along on, I have a dropdown menu that pulls each year into the list. The problem now is adapting it for sports that play one season in two calendar years (like this year is the 2006-2007 basketball season). Is there a simple way to do this? The easiest I've come up with so far is making another table of "seasons" and use a "season_id" in the 'games' table. I've tried looking into "fiscal year" solutions, since that also spans two calendar years, but the naming conventions are different (i.e., "Fiscal Year 2006")
Returning More That One Row From SP
I have 2 simple questions on stored procedures: 1) how do I return multiple rows? DELIMITER // CREATE PROCEDURE join_nary_relation() BEGIN DECLARE c INT; SELECT clanak_id FROM vidi_clanak_hardver INTO c; END; // CALL join_nary_relation(); This fails: ERROR 1172 (42000): Result consisted of more than one row 2) how do I return multiple columns? As you see, I'd like to warp a SP around a SELECT in general case: a SELECT which returns a table with few rows and few columns. Is that possbile?
Returning Last Row
I need to return the last row in the table, I'm using auto_increment which is the 'priKey' column. 'beachName' is the name of the table , 'DATEOFFILE' is the column in the table that I need to return the last value for. Code:
Returning One Row
I'm working on a support ticket system. My two main tables are "tickets" and "ticket_messages". One ticket can have many ticket_messages. Ticket_messages have time stamps. I need a query that will return all tickets with the ticket_message of the earliest time stamp, as opposed to pulling all ticket_messages for each ticket. I'm having trouble digging up documentation or examples of this. Here's a pseudo SQL statement that I'm trying to accomplish: Code: SELECT DISTINCT tm.subject, t.id, t.incident_date_time, t.create_date_time, FROM tickets t INNER JOIN ticket_messages tm ON tm.ticket_id = t.id WHERE tm.id OF Min(tm.date_time_stamp)
Returning All Values
I'm sure this is a straight forward and obvious one: I have this: SELECT * FROM tbl_contacts WHERE con_Customer = "#URL.con_Customer#" Which obviously when you pass a value over the url returns the relevant records. How can I pass a value to the url which passes ALL the records back. Is it something like ?con_customer='%'?
AVG Returning 0, Not Null
I'm writing a script to allow visitors to rate articles on my site. Sometimes a question does not apply to that particular article or a person is just too lazy to fill out the entire form. Either way, I'm passing NULL to the MySQL database if the question was not filled out. I want to do averages for each article for each question I ask. The problem is that I'm getting 0 only when I use AVG () even though the MySQL site says that should return a null if empty. It is not. I have a "control group" table pulling the same data without using AVG. Of course, every vote is visible, but it's clear that null is working properly in that example. Here's is a shortened version of my PHP $queryA = " SELECT URL , AVG(Vote) ,AVG(A1) , AVG(A2) , AVG(A3) FROM Galleries GROUP BY URL "; $result = mysql_query($queryA) or die(mysql_error()); while($row = mysql_fetch_array($result)){ ?> <tr> <td><?php echo $row['AVG(A1)']; ?> </td> <td><?php echo $row['AVG(A2)']; ?> </td> <td><?php echo $row['AVG(A3)']; ?> </td> </tr> <? } echo "</table>"; Any thoughts as to why AVG() would return 0 and not null when my database seams to be setup correctly?
Returning A One Value Average
I'm using PHP and MySQL, and am trying to return an average (songle value). My code isn't working, and I'm getting confused between the mysql_fetch, here's my code: $average = "SELECT AVG(overall) as overall, AVG(gameplay) as gameplay, AVG(graphics) as graphics, AVG(sound) as sound FROM VIDEO_GAME_DETAIL WHERE name=" .$row['name']; $result = mysql_query($average); $rating = mysql_fetch_array($result); // down in an HTML table echo "<td>" .$rating['overall']. "</td>"; $row['name'] is the name of a PC Game from the VIDEO_GAME table. In the VIDEO_GAME_DETAIL table I have reviews for many games, with ratings. My goal here is to average the different ratings (overall, gameplay, sounds and graphics) for a one game, and stick them with the main page that lists the games (1 row per game). I get this following error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /Users/Marconi/Grad/lleccia/www/ISYG250/proj4/reviews.php on line 54 line 54 is $rating = mysql_fetch_array($result);
Returning Most Relevant
I have a table with a text field which contains the information for help files on my website. Using full text searches i can return the most relevant results in order but is it possible to return the most relevant help text for each of the returned results. I'm trying to show the user a sentence or two as a preview for the returned results.
How Returning Zero With COUNT()?
I have this table: CREATE TABLE `people` ( `id` int(11) NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `sex` char(1) NOT NULL default 'M', `age` tinyint(4) NOT NULL default '0', PRIMARY KEY (`id`) ) TYPE=MyISAM; And I want knon how many Females and Males there are in three ranges of ages: from 0 to 20, from 21 to 30 and over 31. I've used COUNT() GROUP BY(sex) but the problem is that COUNT() doesn't return zero but I want see the exact count of peoples of every ranges of age (for sex) including zero. This is my query: SELECT sex, ' < 20' AS range, COUNT(*) AS Num FROM people WHERE age between 0 and 20 GROUP BY(sex) UNION SELECT sex, '21-30' AS range, COUNT(*) AS Num FROM people WHERE age between 21 and 30 GROUP BY(sex) UNION SELECT sex, ' > 30' AS range, COUNT(*) AS Num FROM people WHERE age > 30 GROUP BY(sex) How can I see zeros?
Returning The ID Of The Row Just Inserted...
I'm trying to amend my simple SP to return the unique 'auto-incremented' ID value to my SP as a client reference. The SP inserts the new DB record and then should return the ID for that row...however when I run the PHP page that calls the SP, I get: "ERROR: 1312 - PROCEDURE lead_collector.SPInsertEnquiry can't return a result set in the given context" My SP is as follows: Code:
Last_insert_id() Returning 0
I was using a stored function to return the last inserted record id. With the latest version of the database that functions appears to have stopped working. Is there a fix or a work around that I can use for the meantime ?
Query Returning
I'm having a small problem with my news system. The query below should return the last 5 posts, who posted them, and the number of comments for each. It works fine, except the value comments_count is returning is 7 times what it should actually be.
Returning To The Community
Recently the community helped me immensly when I had a problem with my SQL and the server, I can only feel indebted (is there such a word?) to them for their help.
Last_insert_id Returning 0
Im trying to something like the following: INSERT INTO foo (auto,text) VALUES(NULL,'text'); # generate ID by inserting NULL INSERT INTO foo2 (id,text) VALUES(LAST_INSERT_ID(),'text'); The first insert goes ok and a record in foo is created, but the id inserted into foo2 is 0 and thats not right.
Returning A Percentage
is it possable to return ONLY 20% percent of the overall possable returns in a query? e.g. i have 100 possable returns and i only want 20% of them. So i'd receive 20 returns. What i'm trying to do exactly is return the latest 30 entries and then the next 20% of the possable entries. i'm working with a database that has thousands of possable returns but i don't want all of them.
Returning New Columns
I have data in my SQL table like this: (it goes like this to March 2008) And I would like MySQL to return the same data but with this structure: Is there any possible way to build a query that returns the data ordered in that way? That will really help me to build the application I need just by running through the results.I've been trying UNION, inner SELECTS and many other stuff without any good result. Just wondering if this is possible.
Returning A Field
It's difficult to explain what I'm trying to do, but this should help; name | new article | Ross | 1 Ross | 1 James | 1 Jeff | 1 Ross | 1 If this is the result of a query to select all the names/new article columns from a database where the new article column equals 1, how would I then return the name 'Ross' as the person with most new articles
Syntax Returning
i have a database on the follow format: city, status, status_action and for example i want to return peoples that have the status equal to 1 or 3 and status action equal to 1 or 3 from the city NY so i am using the follow query: city = 'NY' AND (status = 1 or status = 3) AND (status_action = 1 or status_action = 3) but it dont return me the right value, it always give for example status equal 1 of everyone and ignore status 3 i have tryed to use AND aswell and i go no return.
Returning Msg From Stored Procedures
I`m learning how to use SP in MySQL and have some doubts about returning msg from SP. I`ve done sth like this: SQL DELIMITER $$ DROP PROCEDURE IF EXISTS `site`.`addContact` $$CREATE DEFINER=`root`@`localhost` PROCEDURE `addContact`(IN c_type VARCHAR(30)) BEGIN DECLARE msg VARCHAR(100); IF TRIM(c_type)<>'' THEN IF (SELECT COUNT(*) FROM contacts WHERE `type`=c_type)=0 THEN INSERT INTO `contacts` (type) VALUES (c_type); SET msg='Successfuly added' ELSE SET msg='Already exists' END IF; ELSE SET msg='Type name must be provided.' END IF; SELECT msg; END $$ DELIMITER ; SP returns always one msg and it`s easy to use it later in app code i.e. PHP. I`d like to ask if this aproach (with select returning msg) is acceptible? Or maybe I should use OUTPUT variable inside SP to get such info?
Returning The Automatic ID On Insert ?
Is it possible to return the auto_increment ID on inserting a row ? It would save me alot of hassle so i don't have to do another query to get the ID + i'd have problems getting the ID of a row without the ID to say 'where pic_id = $id'. Then again i could just do 'where pic_created = $time AND user_id = $uid'
Returning More Than One Column In A Variable
How do you return three columns with dashes in between them as one variable Like in PHP, you'd do PHP $dob = $day."-".$month."-".$year; Like... i.dob_day + "-" + i.dob_month + "-" + i.dob_year as dob That obviously doesn't work, but you get the idea.
Returning Every Saturday Between Two Dates?
Having a major mental block with this, and was wondering if anyone can help me? Basically I want to create query which will return every occurence of a Saturday between two dates. Looking at the the DAYOFWEEK function, it doesn't appear that you can only provide it with a specific date. The only other way round that I could think of is by having a prepopulated table with all dates within a year and the actual date occurence. I was hoping to do this on the fly, as the query would be used as part of trigger.
Returning Row Id For Added Values
This is probably a simple problem, but I don't know what term describes it so I've had trouble searching for a solution. I have a table with an automatically incrementing primary key that I am adding data to. I would like to be able to return the row numbr (primary key) of the row to which the new data is being inserted.
Its Returning A Value That Doesnt Exist
Okay here is the deal. I am running a query to find out if there is an ID in my table that has a case number that matches the one being submitted. If there isnt once its should step into an if statement that would submit a number of records. Otherwise it should jump to the else statement. I the problem comes from: the query keeps returning "Record id#2" but there is no record matching the case number being submited from the form. if( isset($_POST['Submit'])){ //Begin If statement logic $resultcheck = mysql_query("SELECT * FROM submissions WHERE wfmcase = " . $_POST['wfmc']) or die(mysql_error()); //Query string to test if wfmcase was already submitted echo "Sucess, form varibles have been passed.<br />"; echo "Resultcheck query results: $resultcheck <br />"; if ($resultcheck == ""){ //Checks to see if the query was empty. echo "There are no duplicate case escalations pending. <br />"; I added the echo statement so that i could see how far it was getting through the script. It dies after printing the varible $resultcheck which gives "Record id#2". It never enters the 2nd if statement because for what ever reason the query has the "record #id2" in it... What is causing this? I have verfied that there is no case that matches the one coming from the form and i get the same error.
Returning Top 5 Tags For An Item
System Info: RedHat Linux 9 (text only) Mysql 3 When I execute mysql from the command line I receive an error: "error 2002: Mysql cannot connect to socket /var/lib/mysql.sock" (or something similar) - the socket was definately not "/tmp/mysql.sock" It was installed by the RedHat 9 installer. Do I have to edit some configuration file.
Query Not Returning Any Results
I can't get any results form my query: SELECT * FROM userfile There's only one record in the table but I don't get nay results. The query is written in PERL: #!/usr/bin/perl -w use CGI qw(param); use CGI qw(:standard); use CGI::Carp qw(fatalsToBrowser); my $q = new CGI; print $q->header(); ..........
Returning Columns From Query
I have a table in my SQL database that has about 45 columns. Each row is a date, and on any given date, the value in any of the columns can be null. My question is, how can I got about making a query that will return all of the columns with values other than null? I want the match to be made based on the date entered, and I would like to only have the columns with valid values returned.
Fulltext Returning Too Many Results
How do I get a fulltext search to return only records that contain all terms instead of records that contain any of the terms? In other words, if I enter the search "blues clues" MySQL will only find records that contain the words "blues" AND "clues" without having to introduce special boolean search syntax.
Returning A Column With Row Information
I have two tables, customer and customer_phone. The customer table has an id col, cust_id, which links the two tables. The phone table has a number col, a type col, and a few more. What I need is to be able to return a recordset which comprises of the following: c.firstname c.lastname c.title cp.number (where cp.type=1) cp.number (where cp.type=2) cp.number (where cp.type=3) I currently have this as a left join query, with a left join for each instance of the phone number eg: SELECT c.first, c.last, c.title, c1.number, c2.number, c3.number from customer c left join customer_phone c1 on c.cust_id = c1.cust_id left join cust........... where c1.type ='1' and c2.type = '2' and c3.type= '3' Is there an easier way which doesn't involve so many left joins? My concern is that I'm looking at returning 100 rows per page for a report, and currently I need to return 6 number types, and other relevant information for each phone number.
Returning Image Type...
I have some code that worked on one server but I guess since the configuration of php was different it doesnt work on my new one. It's an upload form that just allows images. It finds out what image type the file is by doing something like the following. $fieldname_type would return either "gif", "pjpeg", "x-png", "bmp" but now its not returning anything. I know that is a short way of doing it so there must be a longer way that will work for it. I don't want to just get the extension bc people could just add an extension on to it, but this would ensure that they are actually the type of images they are suppose to be.
Returning A Random Result
I have the need to return a random record out of a subset of records. For example I'd like to return a random record out of the resulting records from a query such as: select * from test where category="4"; The query could return 1-many results, but I only want a single random result from that subset.
Returning Current Form
I have a table called `match` which holds details of games between teams. `match` has: `match_date` `home_team` `away_team` `home_score` `away_score` I want a query to return the last 6 games (Win/Draw/Lose) for each team. I am unsure if this can be done using my current table format.
Sql Returning Too Many Results On JOIN
I have three tables which I'm joining... AGENTS ======== agent_id commission promo_code AGENT_PAYMENTS ============== payment_id agent_id amount SALES ======== sale_id promo_code status I need to select all the sales info where an agent's promo code has been used (and the status of the sale is "C" (complete)). THe trouble is, the SQL I've used is doubling, trebling etc the sum of the payments made to an agent - this depends on how many results are returned for the number of sales... eg: 4 sales means the SUM is being multiplied by 4. here's the SQL Code:
Returning Values That Are In One Table, But Not In Another
I'm looking for a query to return values which occur in one table, but not in the other. Say we have two tables (table_1, table_2) each with a column titled "appointments". I want a list of results for appointments which are in table_1, but not in table_2 I have tried the following, but all I get is a huge list of duplicate appointments which occur in both tables: SELECT table_1.appointments FROM table_1, table_2 WHERE table_1.appointments<>table_2.appointments; I've also tried 'NOT IN' for this but the result was just the same.
Returning Field Data
I am trying to use the field pc from the Query below in an update query later in my script. How do I do I refer to the field pc? $query = "SELECT sum(cmp) as pc FROM $weeklydb_table WHERE id = $id AND year = $year AND wk < $wk "; $result = @mysql_query($query); if ($result == false) { echo "No Data"; } # echo $result; echo pc;
|
TRACKER
