Re: Query needed for this sol.
Not sure how to do it in a single query, but if you run this query repeatedly, eventually you'll have only 10 left of each category. DELETE tableName FROM tableName, (SELECT MAX(ID) deleteID,count(ID) categCount FROM tableName GROUP BY Category HAVING categCount10) AS Smry WHERE tableName.ID=Smry.deleteID - Original Message - From: abhishek jain [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, April 08, 2007 7:26 AM Subject: Query needed for this sol. Hi all, I need a query for the fol. sol. In a table i have 100s of rows with six categories like A,B,C,D,E,F which is marked with a column like Table ID Category 1 A 2 A 3 B 4 B 5 B 6 C 7 C and so on 100 ids for each of six categories , i want to delete all but 10 ids of each category, i wanted to know how to do so, Pl. help Thanks, Abhishek jain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query needed for this sol.
Hi Abhishek, In a table i have 100s of rows with six categories like A,B,C,D,E,F which is marked with a column like Table ID Category 1 A 2 A 3 B 4 B 5 B 6 C 7 C and so on 100 ids for each of six categories , i want to delete all but 10 ids of each category, i wanted to know how to do so, This seems easier to solve if you re-word the problem in two steps: 1) find 10 rows in each category 2) delete all other rows First define which 10 rows you want to keep. I will assume you want to keep the ten 'A' rows with the GREATEST id number. Query 1 then becomes: select ID, Category from tbl where ( select count(*) from tbl as f where f.Category = tbl.Category and f.ID tbl.ID ) 10; Try that and see if it finds the rows you want to keep. Now you can use what my colleague calls an 'exclusion join' to delete every other row. Plug query 1 into this and you get Query 2: delete t1.* from tbl as t1 left outer join ( -- same query as above select ID, Category from tbl where ( select count(*) from tbl as f where f.Category = tbl.Category and f.ID tbl.ID ) 10 ) as t2 using(ID, Category) where t2.ID is null; This is likely to run slowly on very large tables. If you need this to be efficient, write back to the list again. I hope this helps, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query needed for this sol.
Hi all, I need a query for the fol. sol. In a table i have 100s of rows with six categories like A,B,C,D,E,F which is marked with a column like Table ID Category 1 A 2 A 3 B 4 B 5 B 6 C 7 C and so on 100 ids for each of six categories , i want to delete all but 10 ids of each category, i wanted to know how to do so, Pl. help Thanks, Abhishek jain
Re: Query needed to convert varchar to int ....sorry previous posting was incomplete
VenuGopal Papasani wrote:
Dear all,
I have a table with the following structure.
ield Type CollationNullKey Default
Extra Privileges Comment
--- -- -- ---
-- --- ---
idint(11) (NULL) NO PRI (NULL)
auto_increment select,insert,update,references
indicatorName varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
periodNamevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
sourcevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
level int(11) (NULL) YES
(NULL) select,insert,update,references
value varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
numeratorValuevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
denominatorValue varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
The values in value,NumeratorValue and DenominatorValue
Value NumeratorValue
denominatorValue
NaNNull
Null
infinity
null Null
2143.9888 NUll
NUll
0.0 0.0
0.0
Now i need a query which converts the varchar into some numeric
values.For ex for non numeric values like NAN,Infinity, Null get as zero
and
2143.9 is converted into a numerical 2143.9888 and 0.0 is also
converted
to numeric.The resultset should in Numeric value all the above fields,
Can i
do it using a query.If so can any one give me the query
You could use case:
select case when value is null then 0 else cast(value as unsigned) end
AS new_value;
http://dev.mysql.com/doc/refman/5.1/en/case-statement.html
http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query needed to convert varchar to int ....sorry previous posting was incomplete
Dear all,
I have a table with the following structure.
ield Type CollationNullKey Default
Extra Privileges Comment
--- -- -- ---
-- --- ---
idint(11) (NULL) NO PRI (NULL)
auto_increment select,insert,update,references
indicatorName varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
periodNamevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
sourcevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
level int(11) (NULL) YES
(NULL) select,insert,update,references
value varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
numeratorValuevarchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
denominatorValue varchar(255) utf8_general_ci YES
(NULL) select,insert,update,references
The values in value,NumeratorValue and DenominatorValue
Value NumeratorValue
denominatorValue
NaNNull
Null
infinity
null Null
2143.9888 NUll
NUll
0.0 0.0
0.0
Now i need a query which converts the varchar into some numeric
values.For ex for non numeric values like NAN,Infinity, Null get as zero and
2143.9 is converted into a numerical 2143.9888 and 0.0 is also converted
to numeric.The resultset should in Numeric value all the above fields, Can i
do it using a query.If so can any one give me the query
thanks and regards,
venu
Re: query needed
VenuGopal Papasani wrote:
Once again i send the table data:
Code Period Value
c12004 22
c12005 10
c22005 15
c32005 20
c42005 15
c52005 5
c62005 30
c72005 25
c12006 5
c22006 15
c32006 40
c42006 30
From this I need the sum of values for period 2005 and codes c1+c2-c4-c5
(this is not constant its just an example there is lot of codes like
this..)
For ex:- the result that I want to get is:
the value for code c1, period 2005 is 10
for code c2, period 2005 is 15
sum of c1 and c2 is 10 + 15 = 25
The value for code c4, period 2005 is 15
for code c5, period 2005 is 5
Sum of c4 and c5 is 15 + 5 = 20
Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5
You mean, (c1+c2) - (c4+c5), right?
Peter Lauri wrote:
SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4',
code, IF(code='c5', code, 0))) FROM datavalue;
You can simplify this using IN. Also, we need to sum the values, not the codes.
SELECT SUM(IF(code IN ('c1', 'c2'), value, 0))
- SUM(IF(code IN ('c4', 'c5'), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
VenuGopal Papasani wrote:
if it is static then it works fine.but we have lots of codes in a table
which should be done similar operation.instead varifying staticly with
c1,c2
can we make dynamic.
Well,
SELECT SUM(IF(code IN (list of + codes), value, 0))
- SUM(IF(code IN (list of - codes), value, 0)) AS total
FROM datavalue
WHERE period = 2005;
seems simple enough, especially if this is to be truly dynamic.
Another method would be to store a coefficient for each code in a (perhaps
temporary) table.
CREATE TABLE code_values (code CHAR(2), coeff INT);
Set coeff to 1 for codes which specify addition, and -1 for codes that specify
subtraction. For example, to get c1 + c2 - c4 - c5:
INSERT INTO code_values VALUES ('c1', 1), ('c2', 1), ('c4', -1), ('c5', -1);
then join the tables to get your result:
SELECT period, SUM(d.value * cv.coeff) AS total
FROM datavalue d
JOIN code_values cv ON (d.code = cv.code)
WHERE d.period = 2005
GROUP by d.period;
++-+
| period | SUM(d.value * cv.coeff) |
++-+
| 2005 | 5 |
++-+
I would guess that you have a number of standard queries you must run from time
to time. In that case, you could store the coefficients of each query in a
permanent table. For example, if c1 + c2 - c4 - c5 and c6 + c7 - c3 were
two standard sums, you could do something like (results are using your sample
data above):
CREATE TABLE sum_queries (query_id INT, code CHAR(2), coeff INT);
INSERT INTO sum_queries VALUES
(1, 'c1', 1), (1, 'c2', 1), (1, 'c4', -1), (1, 'c5', -1),
(2, 'c6', 1), (2, 'c7', 1), (2, 'c3', -1);
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 1
GROUP by d.period;
++---+
| period | total |
++---+
| 2005 | 5 |
++---+
SELECT period, SUM(d.value * sq.coeff) AS total
FROM datavalue d
JOIN sum_queries sq ON (d.code = sq.code)
WHERE d.period = 2005
AND sq.query_id = 2
GROUP by d.period;
++---+
| period | total |
++---+
| 2005 |35 |
++---+
Of course, a query description table linked by query_id would be a good idea in
this case.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query needed
if it is static then it works fine.but we have lots of codes in a table which should be done similar operation.instead varifying staticly with c1,c2 can we make dynamic. On 8/14/06, Peter Lauri [EMAIL PROTECTED] wrote: SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: query needed
Not until we know the logic behind the code and how the calculations should be done. -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 1:03 PM To: Peter Lauri; mysql@lists.mysql.com Subject: Re: query needed if it is static then it works fine.but we have lots of codes in a table which should be done similar operation.instead varifying staticly with c1,c2 can we make dynamic. On 8/14/06, Peter Lauri [EMAIL PROTECTED] wrote: SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query needed
Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu.
Re: query needed
VenuGopal Papasani wrote: Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss sure. select c1 + c2 - c4 - c5; doesn't help you fix the problem because you haven't told us what the criteria is for the query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query needed
Once again i send the table data: Code Period Value c1 2004 22 c1 2005 10 c2 2005 15 c3 2005 20 c4 2005 15 c5 2005 5 c6 2005 30 c7 2005 25 c1 20065 c2 2006 15 c3 200640 c4 200630 From this I need the sum of values for period 2005 and codes c1+c2-c4-c5 (this is not constant its just an example there is lot of codes like this..) For ex:- the reulst that I want to get is: the value for code c1, period 2005 is 10 for code c2, period 2005 is 15 sum of c1 and c2 is 10 + 15 = 25 The value for code c4, period 2005 is 15 for code c5, period 2005 is 5 Sum of c4 and c5 is 15 + 5 = 20 Finally the result is (c1+c2) - (c4-c5) = 25 - 20 = 5 On 8/14/06, Chris [EMAIL PROTECTED] wrote: VenuGopal Papasani wrote: Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss sure. select c1 + c2 - c4 - c5; doesn't help you fix the problem because you haven't told us what the criteria is for the query.
RE: query needed
SELECT SUM(IF(code='c1', code, IF(code='c2', code, 0))) - SUM(IF(code='c4', code, IF(code='c5', code, 0))) FROM datavalue; -Original Message- From: VenuGopal Papasani [mailto:[EMAIL PROTECTED] Sent: Monday, August 14, 2006 11:26 AM To: mysql@lists.mysql.com Subject: query needed Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3 c4 2005 2 c52005 1 now i need a query where some values should be added and some values should be subtracted of certain period.for ex here 2005 now i need (c1+c2-c4-c5) can i do it in a single query .Can any one give me the query plsss regards, venu. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL query needed
Hi all I'm working on a book database with some special requirements. Each book is associated with some keywords and put into a category. Category 0 is special, as this is Unsorted, i.e. not associated with a category (which most books are at the moment). For thei query, let's simplify the structure and define it as follows: CREATE TABLE books ( bookID mediumint(8) unsigned NOT NULL auto_increment, title varchar(200) NOT NULL default '', category mediumint(9) default '0', score mediumint(9) NOT NULL default '0', PRIMARY KEY (bookID) ) TYPE=MyISAM AUTO_INCREMENT=1; With each book, there is also a score field which represents the rating of a book, therefore the higher the better. Now I would like to construct a query which does the following: * Return all books that match a requested keyword * All matching books from category 0 * Only one book for each category 0 if there is a match with the keyword (and if so, the one with the highest score) * Order the books by score Any idea and help? Thanks a lot cu reto
Re: SQL query needed
* Reto Baumann I'm working on a book database with some special requirements. Each book is associated with some keywords and put into a category. Category 0 is special, as this is Unsorted, i.e. not associated with a category (which most books are at the moment). For thei query, let's simplify the structure and define it as follows: CREATE TABLE books ( bookID mediumint(8) unsigned NOT NULL auto_increment, title varchar(200) NOT NULL default '', category mediumint(9) default '0', score mediumint(9) NOT NULL default '0', PRIMARY KEY (bookID) ) TYPE=MyISAM AUTO_INCREMENT=1; With each book, there is also a score field which represents the rating of a book, therefore the higher the better. Now I would like to construct a query which does the following: * Return all books that match a requested keyword * All matching books from category 0 * Only one book for each category 0 if there is a match with the keyword (and if so, the one with the highest score) * Order the books by score Kind of tricky, but maybe something like this could be used: SELECT DISTINCT books.* FROM books LEFT JOIN books b2 ON b2.title LIKE %$keyword% AND b2.category = books.category AND b2.score books.score WHERE books.title LIKE %$keyword% AND (b2.bookID IS NULL OR books.category = 0) ORDER BY books.score; The left join is used to check if there are any rows with a higher score for the same category. If there is, this row is _not_ included, unless category=0. You will get multiple books from the same category if two or more books share the same highest score within that category. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
