How to get a specific number of entries per one key ?
Hellow everybody. I have a problem difficult for me, please give me an advice. I want to get a specific number of entry (rows) from a table by each Key. If the number is 1, I can use select dinstinct, but the number is not 1, I don't know how to select. For example: Key 0 | data 0-0 Key 0 | data 0-1 Key 0 | data 0-2 Key 1 | data 1-0 Key 1 | data 1-1 I want to select 2 data for each key. Key 0 (data 0-0, data 0-1), Key 1 (data 1-0, data 1-1). How to describe in query ? Best regards. --- On Sat, 12/3/11, Govinda govinda.webdnat...@gmail.com wrote: From: Govinda govinda.webdnat...@gmail.com Subject: Re: delete syntax To: Cc: mysql@lists.mysql.com Date: Saturday, December 3, 2011, 6:43 AM well, i am using delete/insert-statements since 10 years to maintain users since you only have to know the tables in the database mysql and use flush privileges after changes The privileges should be maintained only using the designated commands. You cannot rely on the knowledge you have of the underlying implementation which can change anytime , while the privileges command are standard. do what you think is good for you if YOU can't be sure what you do [snip] so please do not tell other peopole on what knowledge they can rely *all* the back and forth in these threads is good.. is susses out all the knowledge for everyone to see. Sincerely thanks to everyone who chimes in from all perspectives, -Govinda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: How to get a specific number of entries per one key ?
A quick guess, or at least a starting point: SELECT key, data FROM myTable GROUP BY key LIMIT 2 HTH, Arthur On Sat, Dec 3, 2011 at 11:41 AM, Blog Tieng Viet blogtiengv...@yahoo.comwrote: Hellow everybody. I have a problem difficult for me, please give me an advice. I want to get a specific number of entry (rows) from a table by each Key. If the number is 1, I can use select dinstinct, but the number is not 1, I don't know how to select. For example: Key 0 | data 0-0 Key 0 | data 0-1 Key 0 | data 0-2 Key 1 | data 1-0 Key 1 | data 1-1 I want to select 2 data for each key. Key 0 (data 0-0, data 0-1), Key 1 (data 1-0, data 1-1). How to describe in query ? Best regards.
Query query
I'm having brain freeze, and wonder if anyone can help me with a query. I have a library in MySQL. There's a table with a record per book, and other tables that it indexes into for meaningful info. One of those is an integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey field is decimal. In the Dewey table, it's an integer. I would like to make a report with the info for each DDC, including whether or not there are any books for any given code's integer part. In other words, I want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, and give some info if the count in that range is non-zero. I suspect I need a subquery to do this, but my brain is frozen! (Or should I use a join? Can you even join on an inequality?) The following crashes phpMyAdmin when I try to do it. I suspect it's because the subquery reference to ddn.Dewey is out of context. The subquery works on its own when ddn.Dewey is a literal integer. SELECT (SELECT COUNT(*) 0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn WHERE 1 Any thoughts on the best way to do this? Thanks! After providing the wealth on which the city is built, the countryside and its people are increasingly seen as dispensable. When it appears that cities can thrive on their global connections, rural hinterlands die. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query query
Second attempt, using a join, returns just one row for Dewey 000 with the COUNT being about half the volumes in the library, which isn't right... I thought a LEFT OUTER JOIN would have returned a record for every record in s_library_dewey, but it only returns the first. Brain freeze again... SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 I'm having brain freeze, and wonder if anyone can help me with a query. I have a library in MySQL. There's a table with a record per book, and other tables that it indexes into for meaningful info. One of those is an integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey field is decimal. In the Dewey table, it's an integer. I would like to make a report with the info for each DDC, including whether or not there are any books for any given code's integer part. In other words, I want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, and give some info if the count in that range is non-zero. I suspect I need a subquery to do this, but my brain is frozen! (Or should I use a join? Can you even join on an inequality?) The following crashes phpMyAdmin when I try to do it. I suspect it's because the subquery reference to ddn.Dewey is out of context. The subquery works on its own when ddn.Dewey is a literal integer. SELECT (SELECT COUNT(*) 0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn WHERE 1 Any thoughts on the best way to do this? Thanks! After providing the wealth on which the city is built, the countryside and its people are increasingly seen as dispensable. When it appears that cities can thrive on their global connections, rural hinterlands die. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query query
On 12/3/2011 9:35 PM, Jan Steinman wrote: Second attempt, using a join, returns just one row for Dewey 000 with the COUNT being about half the volumes in the library, which isn't right... I thought a LEFT OUTER JOIN would have returned a record for every record in s_library_dewey, but it only returns the first. Brain freeze again... SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 Lose WHERE 1, it's useless. Add GROUP BY ddn.Dewey to get all counts. PB - I'm having brain freeze, and wonder if anyone can help me with a query. I have a library in MySQL. There's a table with a record per book, and other tables that it indexes into for meaningful info. One of those is an integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey field is decimal. In the Dewey table, it's an integer. I would like to make a report with the info for each DDC, including whether or not there are any books for any given code's integer part. In other words, I want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, and give some info if the count in that range is non-zero. I suspect I need a subquery to do this, but my brain is frozen! (Or should I use a join? Can you even join on an inequality?) The following crashes phpMyAdmin when I try to do it. I suspect it's because the subquery reference to ddn.Dewey is out of context. The subquery works on its own when ddn.Dewey is a literal integer. SELECT (SELECT COUNT(*) 0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn WHERE 1 Any thoughts on the best way to do this? Thanks! After providing the wealth on which the city is built, the countryside and its people are increasingly seen as dispensable. When it appears that cities can thrive on their global connections, rural hinterlands die. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Query query
DOH! Brain unfroze, and I realized I needed an aggregate: SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 GROUP BY ddn.Dewey ... although if there are any brighter ideas, I'm all ears. This doesn't seem very optimal to me, with the FLOOR() function in the JOIN and all... takes over half a second... Second attempt, using a join, returns just one row for Dewey 000 with the COUNT being about half the volumes in the library, which isn't right... I thought a LEFT OUTER JOIN would have returned a record for every record in s_library_dewey, but it only returns the first. Brain freeze again... SELECT COUNT(lib.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn LEFT OUTER JOIN s_library lib ON ddn.Dewey = FLOOR(lib.Dewey) WHERE 1 I'm having brain freeze, and wonder if anyone can help me with a query. I have a library in MySQL. There's a table with a record per book, and other tables that it indexes into for meaningful info. One of those is an integer-keyed list of 1,000 Dewey Decimal Codes. In the books table, the Dewey field is decimal. In the Dewey table, it's an integer. I would like to make a report with the info for each DDC, including whether or not there are any books for any given code's integer part. In other words, I want to bucketize 101.000 to 101.999, etc, for each integer Dewey number, and give some info if the count in that range is non-zero. I suspect I need a subquery to do this, but my brain is frozen! (Or should I use a join? Can you even join on an inequality?) The following crashes phpMyAdmin when I try to do it. I suspect it's because the subquery reference to ddn.Dewey is out of context. The subquery works on its own when ddn.Dewey is a literal integer. SELECT (SELECT COUNT(*) 0 FROM s_library lib WHERE FLOOR(lib.Dewey) = ddn.Dewey) AS Have, ddn.Dewey AS DDN, ddn.Classification AS Classification FROM s_library_dewey ddn WHERE 1 Any thoughts on the best way to do this? Thanks! After providing the wealth on which the city is built, the countryside and its people are increasingly seen as dispensable. When it appears that cities can thrive on their global connections, rural hinterlands die. -- David Holmgren Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
