displaying a specific row within a group by
Consider the ff. table: ++--+--+ | game | rank | date | ++--+--+ | GTA| 11 | 20081001 | ++--+--+ | SPORE | 1 | 20081103 | ++--+--+ | SPORE | 2 | 20091001 | ++--+--+ | SINSOL | 8 | 20081011 | ++--+--+ | SINSOL | 31 | 20080808 | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA| 21 | 20080821 | ++--+--+ | WOW| 1 | 20080922 | ++--+--+ | WOW| 11 | 20081023 | ++--+--+ | WOW| 15 | 20090106 | ++--+--+ I want a single SQL query that will return the latest ranking for each game: ++--+--+ | game | rank | date | ++--+--+ | SPORE | 50 | 20090402 | ++--+--+ | SINSOL | 11 | 20090104 | ++--+--+ | GTA| 21 | 20080821 | ++--+--+ | WOW| 15 | 20090106 | ++--+--+ How do I go about it? The initial 'obvious' solution: select game,rank,max(date) from gametbl group by game DOESN'T work because the 'rank' value we get will not necessarily come from the same row holding the 'max(date)'!! Instead, you have to sort by date first in a subquery before applying the GROUP BY: select * from ( select * from gametbl order by date desc ) as t group by game This seems to work in MySQL but I do not trust this construction because it relies on ordering, which relational philosophy is supposed to not depend on. (Will this really safely work on all proper SQL implementations?) Another reason I don't like it is because it relies on a subquery and I was wondering if it is possible to do away with that. I can also imagine a solution relying on a self-join which does not depend on sorting, but which would require a surrogate primary key which I find even less elegant than relying a subquery. = The Webmechs Webpress blog http://www.webmechs.com/webpress/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Index effectivity for a 2-valued field (was Re: Should I use an index in this case?)
[EMAIL PROTECTED] wrote: Hi Andy, Two questions for you: 1) Why store so many repeatable data in the same table? I mean you needn't to store purchases/previews for all records. You can choose MySql SET datatype, or you can choose another table to store the action types and let the original table refers to it. So that you can reduce the diskspace and also the I/O. Hmmm... not sure if the SET datatype is portable across databases, is it? Also, remember there will only be 2 action types: 'purchase' and 'preview'. Name (of visitor) will actually be an integer id, and I could use a bool or an int for 'action' also, since it is intended to be 2 valued. But a diff of 1 or 2MB of storage is negligible (around extra 9 bytes per row for the 'action' field), and I want the flexibility and readability of using the char field. The important concept here is the TIMESTAMP, each access/visit needs to be recorded separately and thus cannot be normalized away. So if there are a hundred thousand visits, there NEEDS to be a hundred thousand rows. I guess the question probably boils down to: If a field (of whatever type) will only ever have 2 values, will indexing it bring benefits when said field is being refered to in a WHERE clause for a very large table? - I just realized I could do 2 tables, one that stores purchase 'visits' and another that stores 'preview' visits, but that complicates the design and I don't think that's a good idea. Hundreds of thousands of rows is not that hard for MySQL to deal with, but what I want to understand is if I make an index for 'action', will the performance diff be on the order of, say, a query taking 2 seconds versus 0.5 seconds? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Should I use an index in this case?
I have a table that is roughly like the below: id - PK, autoincremented integer name - varchar visit- timestamp action - char(10) Now, 'action', while a char(10), is only ever intended to contain two possible values, purchase and preview. The table might end up containing tens or even hundrds of thousands of rows and a report will eventually be generated that differentiates between purchases and previews. Should I still create an index on the 'action' column and of how much benefit will it be ? Thanks in advance for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is the general MySQL list available via NNTP?
The volume on this list is so big that it desperately needs to be available as a newsgroup. I was able to find one on Google groups as mailing.database.mysql but does anyone know of a publicly available NNTP server which carries this? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why does Key==Mul in DESCRIBE TABLE for unique key?
mysql CREATE TABLE X (FLD1 INT UNIQUE); Query OK, 0 rows affected (0.05 sec) mysql DESCRIBE X; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | FLD1 | int(11) | YES | MUL | NULL| | +---+-+--+-+-+---+ Doesn't MUL mean a key can exist multiple times in the index? I am using 3.23.49-max-debug. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why does Key==Mul in DESCRIBE TABLE for unique key?
- Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Andy Sy [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, June 14, 2002 2:45 AM Subject: Re: Why does Key==Mul in DESCRIBE TABLE for unique key? At 2:03 +0800 6/14/02, Andy Sy wrote: mysql CREATE TABLE X (FLD1 INT UNIQUE); Query OK, 0 rows affected (0.05 sec) mysql DESCRIBE X; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | FLD1 | int(11) | YES | MUL | NULL| | +---+-+--+-+-+---+ Doesn't MUL mean a key can exist multiple times in the index? Right. It means it's part of a non-unique index. But I declared the field as UNIQUE (see the CREATE TABLE statement). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help needed with JOIN on 3 tables
Strange, there shouldn't be any difference between MySQL query behaviour under Win2K or Linux or BSD or Win98. It could be more to do with the version of MySQL you are using. - Original Message - From: bob [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 14, 2002 3:18 AM Subject: RE: Help needed with JOIN on 3 tables This is what I found I will stop using Microsoft for development of mysql. I was running the queries locally on my Win2k machine using the win32 version of mysql. After screwing with it for several days I finally gave up and posted to the list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: auto-increment across multiple tables / Atomicity of update statements
In MySQL, I want to use an auto-incrementing value that works across multiple tables. For example I have 3 tables forumA, forumB, forumC, each of which has a primary key field called 'msgnum'. However, if I declare 'msgnum' auto_increment, 'msgnum' will not be unique across the 3 tables. Thus, instead of using an auto_increment column, I made an auxiliary table which will hold the last used 'msgnum' and update it each time a new record is inserted in any of the 3 tables. Since I plan to use this auxiliary table to hold other values as well and INSERTs to the 3 tables may happen extremely often, I would rather not have the overhead of repeatedly LOCKing and UNLOCKing the table. In connection with this, the following 2 issues crop up: #1) Is the following statement guaranteed atomic? UPDATE TBL SET COL=COL+1 and is there anyway to retrieve the value of COL that was last set by the connection that set it? #2) If a thread with a LOCK on a table unexpectedly dies without being able to UNLOCK it, does it automatically relinquish the lock? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Auto-increment across multiple tables / Atomicity of update statements
In MySQL, I want to use an auto-incrementing value that works across multiple tables. For example I have 3 tables forumA, forumB, forumC, each of which has a primary key field called 'msgnum'. However, if I declare 'msgnum' auto_increment, 'msgnum' will not be unique across the 3 tables. Thus, instead of using an auto_increment column, I made an auxiliary table which will hold the last used 'msgnum' and update it each time a new record is inserted in any of the 3 tables. Since I plan to use this auxiliary table to hold other values as well and INSERTs to the 3 tables may happen extremely often, I would rather not have the overhead of repeatedly LOCKing and UNLOCKing the table. In connection with this, the following 2 issues crop up: #1) Is the following statement guaranteed atomic? UPDATE TBL SET COL=COL+1 and is there anyway to retrieve the value of COL that was last set by the connection that set it? #2) If a thread with a LOCK on a table unexpectedly dies without being able to UNLOCK it, does it automatically relinquish the lock? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
