How To Generate Sequence Values During Query
I have query like below:
SELECT part_no,part_nm,qty FROM tb_stok_out ORDER BY part_no
as result:
part_no part_nm qty
aaa asdfd 3
abab sdfsdf 4
abab adfdf 5
Is it possible in mysql to generate sequence number using query, so the result will be like below :
1 aaa asdfd 3
2 abab sdfsdf 4
3 abab adfdf 5
... etc
View Complete Forum Thread with Replies
Related Forum Messages:
Ordering By Sequence Field With Empty Values
I have a sequence field in my db table that I'm using to order a list. I'm trying to ORDER BY this field, but it puts the empty values before 1 such as empty, empty, 1, 2, 3, 4 instead of 1, 2, 3, 4, empty, empty, empty How can I fix this?
View Replies !
How To Generate Html Output For Query Result With Perl
I can generate the html with the command like: >mysql -ppassword -uusername -H -e "select distinct depID from tabledepart" databasename but i need to know how to generate html output if i already connected with a database, and how to pass the html result to the perl program.
View Replies !
Generate Weight
Code: SELECT id, combo, ROUND(COUNT(*)*100/(SELECT COUNT(*) FROM combos),1) AS `weight` FROM combos GROUP BY slot1,slot2,slot3,slot4
View Replies !
Reference To Already Retrieved Values In Query
As a simple example, say there is table 'namelist' with column 'names' as char(20). I would like to do something akin to: select namelist.names as mynames, left(mynames,2) as initials; In this example, I could just do left(namelist.names,2), but in more complex cases a value retrieved may have had a more complex logic behind it, e.g., if a bunch of nested if() statements. It would seem logical that if a value is already retrieved then I should be able to refer to it within the same query, assuming that the original value does not need to be recalculated. I know I could use a view to do this, but it adds another layer of complexity, and I am not sure that the values would not be recalculated each time the value is needed. If someone could point out to me the correct terminology for this kind of thing I believe I should then be able to look up the information myself. My efforts to find this information on google and the mysql website were unsuccessful.
View Replies !
Update Query Where Values Will Come From Other Table
I'm creating an update query which the value will come from another table. I have here my current query which unfortunately makes the system hangs. Probably because of the query itself is not properly coded. update boxes b inner join messages m on b.ctnnumber = m.ctnno set b.consigneerecv = m.CName, b.consigneerecvdate = m.DateRcv, b.phrecventered = "Y", b.PhilStatus = "delivered", b.prevreleasestatus = b.releasestatus, b.releasestatus = "delivered", b.PhilStatusDate = m.smsrecvdate, b.phdelprice = "0.00", b.phdelamt = "0.00", b.recvrelation = m.Relation, b.APRecventered = m.smsRecvDate where b.consigneerecv = '' or b.consigneerecv = 'NA' or b.consigneerecv is null; I'm thinking revising it so that it will not cause the system to hang but I don't know how. Guys please help me with this one. I also have this another idea which probably will not work. My idea was something like this: Update table1 set table1.column1 = (select table2.column1 where table2.column1 = table1.column1), table1.column2 = (select table2.column2 where table2.column1 = table1.column1), .....
View Replies !
Accumulating Values Inside A Query
I only have experience of simple queries, and this one is a bit beyond me. I've done some research, and I think it might be possible to do within a single query, but I'm not sure. I have a single table. It contains columns (among others): date, number. I want to produce a table of DATE, and new_number_count, where 'new_number_count' is the number of numbers on day with date DATE that do not have rows on previous DATEs. For example, if this is my table:
View Replies !
Distinct Values In Mysql Query
I have mysql query but i am not sure about it,bcoz it return many rows.. I want like distinct values from three tables with ic number. Which is posted by user... "SELECT distinct loguser.icnumber,access.acccode,dealerdtl.name,loguser.fullname,access.period,loguser.staffcode, DATE_FORMAT(access.actdate, '%d-%m-%Y ')As formatteddate1, DATE_FORMAT(loguser.creationdate, '%d-%m-%Y %H:%i:%s')As formatteddate FROM dealerdtl,loguser,access where access.icnumber=loguser.icnumber AND loguser.icnumber = '$custic'"; This query return many rows i dont know why, i want only one row which is inserted by the user as customer id.
View Replies !
Generate Data Sequences
I have a table containing integer counters associated with particular datetime like this: CREATE TABLE `counters` ( ... `when` datetime NOT NULL default '0000-00-00 00:00:00', `counter` smallint(5) unsigned NOT NULL default '0', ... ); For some purposes I need to initialize frequently the table for time sequences with constant time step e.g. since 2003-11-06 8:00 to 2003-11-06 12:00 with time step 15 minutes. It means I need to do following INSERT commands: INSERT INTO couters (..., '2003-11-06 8:00', 0, ...); INSERT INTO couters (..., '2003-11-06 8:15', 0, ...); INSERT INTO couters (..., '2003-11-06 8:30', 0, ...); .... INSERT INTO couters (..., '2003-11-06 12:00', 0, ...); Of course my C code is able to generate such command strings in one simple loop and send them to the MySQL server. But the code should be as fast as possible and I prefer to do this task by one INSERT-SELECT command like this: INSERT INTO counters SELECT ... Unfortunately I do not have any idea how to build such SELECT command that will generate the datetime sequence for me.
View Replies !
Generate A List Of Year
i'm trying to make a list of year using integer table, but currently, i'm stuck.. the result that i'm looking for is like this, 2000, ......, ......, 2009
View Replies !
Generate A Unique Code
Did someone know how to generate a unique code with Mysql custom function? ex: a1254174, b41514741. I'm plan design a shop site with PHP. I wanna try to generate a Product Code with Mysql not use PHP.
View Replies !
Generate Table Of Week
Is there a way to generate a table automatically that looks like this: YearWeek StartDate EndDate ------------------------------------- 200701 2007-01-01 2007-01-06 200702 2007-01-07 etc. Ideally, the query would generate all the YearWeeks from year 2000 up until CURDATE, not including the current week (i.e. all "complete" weeks). Another option would be to simply store YearWeek in a table, and automatically select StartDate and EndDate accordingly. I guess another option would be not storing it in a table at all, simply generating it at runtime.
View Replies !
Generate Computed Timeseries
I'd like to left join data values to a computed time series that I know is not missing any time steps. This way, when we select data, there will be nulls in place of missing records instead of hidden gaps in the timeseries. I have not found a way to compute this time series in MySQL yet. Is there a built-in series of functions that might perform this task? We'll want the computed time series to step at 15 minute intervals from 2004 to the present most recent 15 minute interval. Any thoughts?
View Replies !
Generate Report Using Group By
I have few invoices which have due_date as one of its field. Now I want to generate a report like the following CName --------0-30 -------- 31 - 60 --------61 - 90 -------- over 90 abc --------$45 -------- $34 -------- NO -------- No Xyz --------No -------- $78 -------- $76 -------- No bcd -------- $897 -------- $456 -------- No --------No Totals: ---- --- --- --- Where those days are overdue period (today - due date). I want to retrieve the whole in one (max 2 not a buch in a loop) queries.
View Replies !
Generate ID Of A Specific Format
I am writing a Java web app, where I need to auto generate & increment the primary key. It should be of the format ABC<auto-increment-serial><month><year>, so it should look like ABC-001-072008. I know how use auto-increment id in MySql, but I am stumped on where to look for a similiar occurance. A search on this forum did not return a solution.
View Replies !
Generate Random String
I'm writing a sequence of insert statements which I want to execute via the phpmyadmin interface to insert records into a table. The table contains only two columns: name and uid. The first column is obvious. The second column is just a random string of 32 characters. My insert statement are of the form ... INSERT INTO user_table (name, uid) VALUES('Some Body', ��dfdfkdsfjdfddf33dfkdfk'); INSERT INTO user_table (name, uid) VALUES('Another Body', 'ewrkdk32832wrekldfnejdsfdsf'); I want to know if there is an easier way write the insert statement so that the statement will generate the random 32 character string for me. Such as something of the form.... INSERT INTO user_table (name, uid) VALUES('Some Body', RANDOM(32)); Or is there a way to tell phpmyadmin to populate the "uid" column of existing records with a random string if that column contains NULL.
View Replies !
Disrupted Sequence
Another brainteaser: CREATE TABLE IF NOT EXISTS `test` ( `id` INT(11) unsigned NOT NULL auto_increment, `cid` VARCHAR(10) NOT NULL, `item` VARCHAR(10) NOT NULL, `val` INT(11) NOT NULL, `dt` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 In the above example table I need to find the first 5 highest values of val that break the sequential set of values. In other words: say I have values 49,48,45,44,43,40,39,37,36,34 of val in my table, with 49 being the highest value of val (need to sorted first, because values may randomly be dispersed in my table), then I want to get 47,46,42,41,38 as the query result.
View Replies !
Create A Sequence?
I am creating an application that will contain three tables that need to share the same sequence. Upon digging through the docs, I do not see how I could do this. Could someone give me some guidance into this? Also, As I read the docs, when I am creating tables, there is something about an InnoDB? Sorry to be confused, but I am not sure how to begin creating my database, nor tables without this knowledge.
View Replies !
Sequence Substitute
I have a problem about create sequence number which can be reset after MAXVALUE has been assigned. In the older version of mysql i can use "create sequence", but in the recent version i can't do that anymore. All i know in mysql ver 5 is "Auto_increment" which cannot be reset number without delete or truncate table.
View Replies !
CREATE SEQUENCE ?
Tracing some examples in a book from Apress, for some reason they go for Oracle in this book - not my cup of tea, especially not when I am sitting on a vista-terminal. Enough of my complaing. How do I create a sequence in mysql ? This is the example I am stuck with: CREATE SEQUENCE SEQ_ID_GEN INCREMENT BY 1 START WITH 100 MINVALUE 1 CACHE 50 ; How do I write that sentence in mysql ?
View Replies !
Sequence Generation
I was wondering if there was something close to CREATE SEQUENCE in MaxDB for standard MySQL. I'm thinking of just doing a table/stored procedure and calling it a day, but was wondering if MySQL had some other alternative.
View Replies !
What Collation Sequence?
I've just transferred a small PHP/MySQL application from a Firepages phpdev5 install to an XAMPP install, so I've got new versions of everything. I used phpMyAdmin to export and import the tables of the database. When recreating the database on my new install I selected the default collation (latin1_general_ci) but my application is now displaying ? in place of a number of characters so I guess I got it wrong. I can't see where in the old phpMyAdmin (2.3.0-rc3) to determine what collation the database uses.
View Replies !
A Query To Select A Column When A Percentage Of Values Non Zero?
I wounder whether some of the experts out there might be able to help me with a problem I'm having. I do not know whether this is possible or not... I have a large table of stock price data which is straight-forward enought. I can select prices based on a ticker and date ranges. However, what I'd like to do is to select prices only when, say 75% of them are non-zero (with the goal of eliminating new/suspended/delisted stocks). Of course I could just select where price > 0, but then I might get only a few rows where this is the case. What I would like to do is always get the full date range of prices, but only if >75% are there.
View Replies !
Select Query, Unique Ids With Matching Values
trying to figure out how to select the pid's from the following table which have matching wid's. ie. the table below would return a result set of 8, 22 and 68 because they are shared by wid 1 and 2. PHP Code:  wid        |    pid --------------- 1        3 1        8 1        22 1        56 1        68 1        92 2        2 2        8 2        15 2        22 2        68 2        77Â
View Replies !
Select One Distinct And Several Random Values In One Query
I'm wondering if it's possible to select an id (let's say its 13) and several random id's between 1 and 100 in one query, excluding for example the id's 14 and 15. I've tried different ways with UNION - but when I include 'order by rand()' in my query, I'll never get the id 13 back. Could anybody help me please? Here's a query which did not work, because i will always get the the same result, without random values: SELECT * FROM table WHERE id = '13' UNION DISTINCT SELECT * FROM table WHERE id != 14 AND id != 15 AND (id BETWEEN 0 AND 100) LIMIT 3
View Replies !
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
View Replies !
MySQL Generate UNIX Timestamp
Cam MySQL create a UNIX timestamp during an INSERT? I want to create a timestamp that is the number of seconds since January 1st 1970. Are there any inbuilt functions for doing so?
View Replies !
Generate A Unique Key For A Group Of Records
I am interacting with a database using jdbc and I have a particular storage need. I have data pairs I need to store with some association knowledge. Basically I get in a bulk of key/value pairs and I need to store them with a common 'group id' and other common info so I can retrieve them all at a later point. To do this, I have a table 'messages' with a bigint 'groupid' and 2 varchar 'key','value' columns. I also have a table 'groups' with a autoincrement 'groupid', a 'stamp' timestamp', and a varchar 'details'. How I envision (with little db knowledge) the flow to go is: 1. insert a new groups record, providing the new common info. 2. Get the autogenerated groupid. 3. store each piece of data in the messages table using the obtained groupid. My question: is there any way to easily get the generated groupid from step 1?
View Replies !
Why Does This Generate A Syntax Error In Mysql 4.x?
SELECT * FROM (SELECT * FROM `tasks` as `task` WHERE `task`.`department_id` = `6` AND `task`.`parent_id` = `0` AND `task`.`progress` <> `100` ORDER BY `task`.`modified` DESC LIMIT 5) as `Task` LEFT JOIN `members` AS `AssignedTo` ON `Task`.`member_id` = `AssignedTo`.`id` LEFT JOIN `members` AS `AssignedBy` ON `Task`.`assigned_by_id` = `AssignedBy`.`id` LEFT JOIN `departments` AS `Department` ON `Task`.`department_id` = `Department`.`id` ORDER BY Task.priority asc
View Replies !
Generate A Comma Seperated List
I have two tables and I'd like to take the data from one and make a comma seperated list for the other. Basically, here is the structure: glossary: id INT NOT NULL PRIMARY KEY AUTO_INCREMENT term TEXT definition TEXT synonyms: id INT NOT NULL PRIMARY KEY AUTO_INCREMENT term TEXT glossary_id So, I need a query that will go through synonyms and give me a comma-seperated list (or some other convienent list) of them for the glossary entry.
View Replies !
Identity, Sequence, Serial
Does MySQL offer capability for any of the following? - IDENTITY - SEQUENCE - SERIAL Or is AUTO_INCREMENT the catch-all for the functionality of all of the above? I ask because I'm looking into Java EJB3 which describes support for any of the above (as well as AUTO_INCREMENT, so I'm not out of luck completely).
View Replies !
Re-arrange Primary Key Sequence
i am facing a problem with my application when someone deletes a record and hence its key disappears breaking the sequence e.g 1 - entry 2 - entry (3 - deleted no mere here) 4 - entry 5 - entry When key is not there my AJAX application fails to run properly due to not finding next id. Is there any method to re-arrange primary key values to a proper sequence when one or more numbers are missing?so that i will write a query and whenever user deletes an item i will re-arrange the primary key sequence automatically
View Replies !
Selcting Date Sequence
I am trying to return a set or rows that represent all of the days between two dates. For example, I want to select all the days between December 25th, 2005 and January 5th of 2006, and i am looking for it to return (shown here in CSV) 2005,12,25 2005,12,26 2005,12,27 2005,12,28 2005,12,29 2005,12,30 2005,12,31 2006,01,01 2006,01,02 2006,01,03 2006,01,04 2006,01,05 So, for any date, i want to select all the days in between. Any ideas?
View Replies !
Order By An (out Of Sequence) Set Of Numbers
I'm having a problem as follows: here is a an example table (tbl_test): id | value ---------------- 1 | hello 2 | hi 1 | bye 4 | cya where the value field is variable. and i want to select them so they are listed as follows: id | value ---------------- 2 | hi 1 | bye 1 | hello 4 | cya is it possible to order by and array - something like: SELECT * FROM tbl_test WHERE 1 ORDER BY id (2,1,4)
View Replies !
How To Create A Sequence In MySQL
I am not able to create a sequence in MYSQL 5.0 Want help on creating the same. Basically, what I am looking for is, that I should get a number greater that the last one on every call to this sequence. In Oracle, one can simply write something like:- CREATE SEQUENCE seq_ordermaster increment by 1 start with 1 maxvalue 999999minvalue 1 nocache Nocycle; And while calling, one can use it by selecting seq_ordermaster.nextval from DUAL Considering the fact that there is no DUAL table in MySQL, how can One write such a sequence and also, how one can get the next value on each select statement?
View Replies !
Mysql Execution Sequence
I need to retrieve the last value from a column in a mysql table, perform some actions on it, then add 1 to the original value and store a new entry. I am worried that if multiple users access the database at the same time I will end up with duplicated 'new values'. Does mysql complete execution of a script before it allows access to another user or do I need to 'lock' the table in some way while the script executes?
View Replies !
Find The Number Of A Row In A Sequence
MySQL Code: SELECT * FROM comments WHERE id=12345 ORDER BY rating Is there a way to determine the position that this rows appears in the sequence? Eg, so I can print out: "This site is rated position x out of y"
View Replies !
|
TRACKER
