Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance.
select *
from accounts
group by date(dateCol)
This always gives me the first row of the grouped column dateCol.
View Complete Forum Thread with Replies
Related Forum Messages:
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
View Replies !
Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this select name, priority, parent from table1 select max(priority) from table1 group by parent are the two select statements that I wish to combine...is it possible..?
View Replies !
SQL IN() Function
so i have a query such as SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)
View Replies !
Avg Function
Why doesn't this work: SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value I'm trying to select the avg_value of 'r' where the avg_value = '100'.
View Replies !
Using The AVG Function
I am trying to use this query: SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' ) to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?
View Replies !
Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?
View Replies !
SOUNDEX Function
I'm using SOUNDEX mysql function to find similar sounding names from a table with 2 million distinct names. Unfortunately there is a single soundex code for every 200,000 names! Meaning there are only 200,000 distinct codes for 2 million entries. Is there any other function / library / technique to work around this immense 1:10 redundancy ? for example, soundex for 'avis', 'apex' and 'apps' is A120, but I'd like to differentiate between them in my search - meaning implement a "stricter" sound comparison than the soundex function offers.
View Replies !
MBREquals Function
Has the MBREquals function been implemented in the latest 4.1.1-alpha build. I am finding problems with that command. It says that problem is there in sql syntax. If it has been implemented. Can you please help me in the syntax of the command.I am finding the same problems with the Intersection,Union,difference,symdifferenc commands But manual says that they are not yet implemented. So I guess they are not yet implemented.
View Replies !
Equivalent Function
I use the following function in Oracle SELEC T decode(status,'A','Active','L','Active','Former') FROM Table What it means is: if status = A, return "Active", if status=L, return "Active", else return "Former" in the select statement. Decode in mysql has nothing to do with this functionality and I didnt see a function while browsing the docs online that did this. Is there an equivalent function in mySQL?
View Replies !
CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done. Please let me know if/when this will be fixed, and if there is a work around that might be used, r a better way to insert BLOB data is known.
View Replies !
CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done.
View Replies !
A Date Function
I read that MySQL needs dates to be in the form YYYY-MM-DD before you can store them in a table. Since users probably won't enter dates in that order (more likely MM-DD-YYYY or MM/DD/YYYY) is there a MySQL date function that I can apply to the date before it is inserted into the database to format the date correctly? (or does the order of the elements have to be taken care of by the application prior to the database query?)
View Replies !
Add_date Function
I am running the query: SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs but it returns a null value. What am I doing wrong? In the bigger picture, I want to take a date from pubdate column, add 3 years to it, then save that return value in a different column named pulldate. I am unsure how to do that - can I nest select and insert statements? Something like: INSERT into pubs set pulldate='(SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs)'
View Replies !
Count() Function
I don't know why I cannot use the count function in mysql. I used the following query at the mysql prompt, but it gave me the syntax error at Count(*) SELECT QProgram, COUNT(*) FROM ContactUs GROUP BY QProgram;
View Replies !
Function Error
I´m trying to create a function using SQL language however even the command below that I found in the MySql docs is not working, I always get the same error whe I try to create a function Any suggestions ???? CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); 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 '(s Char(20)) Returns Char(50) RETURN CONCAT('HEllo,',s,'!')' at line 1 (State:37000, Native Code: 428)
View Replies !
Date Function?
I know there is a date function : TO_DAYS but is there a function that will turn a date object into millseconds or seconds?
View Replies !
Dateadd Function
i have a question what do you mean by this:YMD(YEARS(dateadd(month,0,'2006-10-11')),month(dateadd(month,0,'2006-10-11')),lastday(dateadd(month,0,'2006-10-11')))) can somebody explains to me what do this means, is this same as dateadd(month,0,date)?
View Replies !
Preg_replace Function
I am looking for a preg_replace function in mysql....one where I can match a substring within a body of text and replace it with a new substring. I have been looking at both the replace and match functions that mysql offers, but nothing seems to fit the bill. Can someone help me construct a simple query so I can update some text within a field without having to write a php script to do so?
View Replies !
ISNULL Function
In MS SQL I often use the ISNULL function in the select statement to return the non-null field. For example, isnull(field1,field2) would return field1 if there is a value in it but if it is NULL it would return field2. I am now trying to do something similar in MySQL but I see that ISNULL does not work. Is there another easy way to do this in a select statement in MySQL?
View Replies !
LIKE In Search Function
my mysql server experiences really high load average and this is function which is rans most, does anyone know how to rewrite optimize this? Would be appreciated , thanks. if ($q) { $exp = explode(" ",$q); for ($i=0; $i<count($exp); $i++) { $hvahvor .= " && title LIKE '%$exp[$i]%'"; } } $this->get = mysql_query("SELECT id,type,title,url,sname,surl,date FROM $this->mysql_tb_dl WHERE $hvahvor ORDER BY id DESC LIMIT $this->page, $this->limit");
View Replies !
Lookup Function
i have 3 tables; Customer, DVDDetails and OrderForm. In the Customer table there are a few standard fields, including a Customer_ID field set to auto increment and as the Primary Key.In my OrderForm table, i have fields into which data about the order is inputted, but i also have a Customer_ID field.Is it possible to have MySQL 'lookup' the Customer_ID field as assigned in the Customer table and basically paste this number into the Customer_ID field in the OrderForm table?I basically want to be able to associate the Customer's details with their order by the unique order number. the Customer_ID fields are the only 2 common fields between these 2 tables...an easier way to this?
View Replies !
Function For 2 Dates
i have inserted 2 dates say "2008-01-01" and "2008-01-05", I want the return to be "2008-01-01", "2008-01-02", "2008-01-03", "2008-01-04", "2008-01-05" . Is there a mysql function or need a custom function? IF so, can you provide some reference ?
View Replies !
Median Function
Is there function to find the median value of a column? To all of you who don't remember your core statistics anymore: a median is NOT an average. 1, 1, 1, 10... average = 3.25 1, 1, 1, 10... median = 1
View Replies !
Rank Function ?
looking for an aggregate function similar to count to return the rank of a grouped set. What I mean by rank is the relationship of the count of the group to the total group. An example would be: SELECT COUNT(*) AS Rows, RANK, Manufacturer FROM Cars GROUP BY Manufacturer If the result was Rows Rank Manufacturer 10 2 Audi 20 1 Honda 5 3 Kia I would like to be able to order by any column but still have the rank mean something.
View Replies !
DATE_ADD Function
I have a table which stores the date a user registered on my site and I would like to compute the date a year from that as their membership expiry date and store in in a field called expiry_date.Since I use the NOW() function to generate the registration date, can I also use NOW() as a parameter to DATE_ADD along with an INTERVAL 1 YEAR to get expiry date?Also, I am stuck with a fairly old version of MySQL as I am using Mambo (version 3.23.58) so I guess I have to be careful with DATETIME fields? My date_registered column is of type DATETIME. Any help would be enormously appreciated as I ma working to a rapidly approaching deadline.
View Replies !
Login Function
I’m creating a login function for my customers and administers. But I have some problems modelling the tables. I think I wont something like this Table usergruop Usergroupid usertyp Tabel useraccount Useraccountid fk_usergroup accountname accountpassword The question is how am I suppose to do whit the fk_keys? I need to have something like fk_costumer , fk_admin , fk_reseller because I have to have some connection between usergruop/useraccount and the tables where the costumer, resellers and admin are stored.
View Replies !
Extract Function
Is there any way to adapt the EXTRACT function to work on multiples of minutes, hours etc. In the example below I would like to count the distinct opid's in an hour for a 2 min or a 5 min interval. I do not want to use a while loop to make multiple queries. Code: SELECT EXTRACT(minute FROM arrived) AS themin, count(DISTINCT(opid)) AS op_count FROM `chatEntries` WHERE `arrived` >= '2008-04-01 00:00:00' AND arrived <= '2008-04-01 23:59:59' GROUP BY themin
View Replies !
ROWS Not SUM() Function
I have a table with 700000 rows and 300 columns, 299 columns except the first column is BIGINT columns, What I am trying to do is to create a new column call SUMALL and write the SUM of all values of a single row to that column ? I tried several different things including searching for an help, but it did not work out, for instance I tried alter table tbl ADD (sumall BIGINT as (1 + 2 +3))
View Replies !
Phpmyadmin Md5 Function
Is there a way I can have the MD5 function encrypt a text field every time I add a new row? I know the function is md5(text_password). I also know that UPDATE table SET md5_field=MD5(text_pass).
View Replies !
Using The SUBSTR Function
I'm wanting to use the SUBSTR function to select only 20 or 30 characters in a string, but i'd like to make it obvious to the user if the string has been cropped by putting "..." at the end. So for example, "This is an example string" would become "This is an exa...", but if the string is short enough that it doesn't need to be cropped then there shouldn't be any dots at the end. Is there a way I can do this using purely MySQL? The only way I can think of is to stop using SUBSTR altogether and do it using PHP.
View Replies !
LIMIT FUNCTION
in using limit function...please help me what is the coding in displaying the remaining results if i limit the result to 10 then the actual result is 20.
View Replies !
Calling Db Function
how do i call a db function from with in a db function which returns a result. can some one pls help me with the syntax, when i call a function from with in a function it says funtion does not allow to return a result set where as it returns only one values.
View Replies !
SUBSTR() Function
SELECT SUBSTR(message, 0, 20) FROM posts My goal is to start at the first characters on the far left, and then return 20 chars only. This function for some crazy reason seems to start at the end of the string though, which is stupid, because how do I know where the string starts?
View Replies !
Create Function
How can I create a function in mysql command prompt because when i try it throw a lot of errors but in mysql administrator they create find and they work find.
View Replies !
RAND Function
Basically, I have a stored function that is designed to deliver a random 3 digit code. But I need it to be of the format #~#~# (ie, 3~0~7, 5~1~5, etc) So I set up the function to generate 3 random digits and then concatenate them together, using the following lines inside the function: SET v1 = FLOOR(RAND() * 10); SET v2 = FLOOR(RAND() * 10); SET v3 = FLOOR(RAND() * 10); SET vCode = CONCAT_WS('~', v1, v2, v3); The function works fine, and gives me back my 3 digit code in the exact format that I want. However, after testing it out, I've discovered that it isn't entirely random. There are certain combinations that I never end up getting returned from the function. There are basically 1000 different possible combinations, but I'm only ever receiving 800 combinations back. I've loop tested the function to generate literally millions and millions of different codes, and there are certain codes that I just never see. Code:
View Replies !
Function In MYSQL
I am using MYSQL 5.0.22. My database name is 'normal'. It could contain 2 tables, such as Itemgroup & Itemlist. I tried with the FUNCTION query for Concatination purpose. Funtion name is Serial, The Query is, CREATE FUNCTION Serial (s CHAR(10)) RETURNS CHAR(25) RETURN CONCAT('SALES',s,'IND'); It would say the error as followes, ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'normal' Is the Query right or wrong? or How will I used (or) handiled the FUNCTION option? If any one knows, please guid me
View Replies !
YEARWEEK Function
I have just discovered the YEARWEEK date function and very useful it is too! However, having changed a date into a YEARWEEK I now need to convert it back again - I want the date of the first day in that week. Is there an easy way to do this? I'm thinking something like: my $year_week = 'select YEARWEEK($date)'; my $first_day_of_week = 'select FROM_YEARWEEK($year_week)';
View Replies !
Mysql 4 Function From A Mysql 5 Function
I found this function on the mysql website : CREATE FUNCTION substrCount(x TEXT, delim varchar(100)) RETURNS int RETURN (length(x) - length(REPLACE(x, delim, ''))) / length(delim); That works perfectly with mysql 5. But when I try to import this to mysql 4, I have an error : suse-server:/partages # mysql mybase <function-add.sql> output.tab ERROR 1064 (42000) at line 1: 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 '(x TEXT, delim varchar(100)) returns int return (length(x)-length(REPLACE(x, del' at line 1
View Replies !
Convert Function
How can I convert a function (SQL SERVER) that returns a TABLE type and IS USED in queries, like, SELECT * FROM Item INNER JOIN myFunction(SomeParameter) ON Item.ItemID=myFunction.ItemID Here's an example (SQL SERVER): CREATE FUNCTION [DPVIEW71_0](@TransDocument nvarchar(255),@TransSerial nvarchar(255)) RETURNS TABLE AS Return (SELECT [TransDocNumber], [CreateDate], [DeferredPaymentDate], [PartyID], [PartyName], [TotalNetAmount], [TotalTaxAmount], [TotalTransactionAmount], [CurrencyID], [ContractReferenceNumber] FROM [BuyTransaction] WHERE [BuyTransaction].[TransDocument]=@TransDocument AND [BuyTransaction].[TransSerial]=@TransSerial)
View Replies !
Aggregate Function
I am kind of confuse how to select this data Which city has the most airports. There are two tables airport and cities the airport has two column IATA_CODE and the name of airports where as city has the IATA_CODE and the name of the cities. I used two diffrent ways but I dont't which one is right. select name, count(distinct IATA_CODE) from airports group by name; SELECT c.name,count(*) AS no_of_airports FROM airports a LEFT JOIN cities c ON c.IATA_CODE = a.CTY_IATA_CODE GROUP BY a.CTY_IATA_CODE; both have the same result and I coudn't be able to select once city who has the most airports.
View Replies !
NOW() Function MySql
We are developing a tool here that uses a php script to insert data into a MySQL 5.0.41 database running with the INNODB engine. While tracking a bug we noticed a strange effect. Each insert query contains a call to the NOW() function to store the create data of the record into a field automatically. Therefore we can expect in my opinion that the timestamps of the records are increasing simultaniously with the autoincrement key of the table. This actually is not the case. On the contrary the timestamps are jumping up and down with large differences. For example we have the record with ID1 that got the timestamp 14 minutes 2 seconds where ID2 got 15 minutes 15 seconds and ID3 14 minutes 9 seconds. How could this be? Does it mean that the server does not execute the statements in the order he receives them? Is it possible that it took more than a minute to execute a simple insert statement?
View Replies !
Count Function
I have a table with 40 rows. After I connect to the server and select a database I try to store the number of rows in a certain variable using php. I'm using.... $count = mysql_query("SELECT count(*) FROM League1); echo $count; ...when I do this it displays "resourceID#2resourceID#4" For some reason this made me think I was getting an array instead of a straight up number so I added this line before echo $count; $count = count($count); ....and that just displayed "11" no matter how many rows there were in the table. After all of this I am getting the feeling that I am doing something totally wrong but everywhere I look to find out what I did wrong I get either an answer that is specific to another problem or something way too general.
View Replies !
Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ? I need to loop blindly without see any updates or inserts occur during the loop....
View Replies !
Function In Index?
I have a table that has a datetime field called (confusingly) 'date', but a lot of my queries do DATE(`date`). I decided to add an index on DATE(`date`), but it doesn't seem to make much difference in speed. Is it valid to put an index on a function of a field?
View Replies !
SEC To TIME Function
I am presently trying to calculating times from a mysql database. I have calculated hrs worked on a job by an employee and hrs worked by an subcontractor using the TIME_T_SEC and SEC_TO_TIME functions. These results I get are correct. When I calculate hrs worked by both and convert to time using the SEC_TO_TIME function I get a wrong result. I have tried a number of ways to do this and get the same wrong result. If I convert both the correct results to seconds and divide by 3600 and add them I get a correct result. I don't understand why the sec_to_time function gives me a wrong result.
View Replies !
Function Transaction
I'm working on a little framework based on SQL, i'm fighting with session becouse i'm need to check if the user is login or not and then if can see the resource or not, it's can be change for each resource. Code:
View Replies !
|
TRACKER
