Help with ordering and grouping with distinct ...

Hi all, I have a table with three columns: Destination varchar(254) SentDatetimestamp(14) UserID varchar(32) I want to be able to get a list of the 100 most recently used numbers, based on the timestamp (SentDate) column. I want to have them ordered such th

Help with selecting, grouping and distincting ... May need nested statement?

Hi all, I am writing a PHP web page that displays some statistics based on information in a database. The database has a table (stats) with three fields: ID, Sender, Text, UserID, Timestamp I have set it up so that I get basic statistics with the following query: SELECT Text, Count(*) FROM st

Indexing question

Hi all, I would like to create an index to speed up the following query: SELECT Account, Status, count(*) From MessageStatus WHERE sentDate > '(variable)' AND sentDate < '(variable)' GROUP BY Account,Status ORDER BY Account sentDate is a timestamp(14), Account and Status are both varchars. The

Indexing Question

Hi all, I have developed a system that lets people send messages to each other, with a MySQL database and PHP. Among the tables that exist, one is called "Users" and contains information such as Username, password (md5'd of course), email address etc. This table has a primary key "UserID" that i

Newbie: Help with count

Hi all, I have a table that has data that looks something like this (fixed font on): UserID MessageID MessageState 1 1 PENDING 1 2 PENDING 2 3 FAILED 3 4 DELIVERED 2

RE: Scheduling with MySQL

> However, I would suggest, if you have control of the search > tools, that you simply put in an extra timestamp field called > "hideuntil" into your database, and add conditions into your > searches that drop rows for which hideuntil is not null and is > after the current time. This is a great i

Scheduling with MySQL

Hi all, I have a PHP based web interface that interacts with MySQL to let users insert data into a table called "Messages". Once an entry is made in this table, other applications read and process the entries, modifying them where appropriate. I would like to add the capability to have this inse

RE: Timestamps querying question

> It is not apparent from your question, where the problem lies. Why not > show us what you have so far? > SQL is used to retrieve data from relational databases. Are you really > intending to retrieve FROM a web page? Sorry, I meant that I am writing a PHP web page that takes input from a user t

Timestamps querying question

Hi all, I have a database table which includes a timestamp(14) field. I would like to construct an SQL statement that pulls out records between two dates, based on this field (i.e. time is not important, date is). So a user input "01/01/2001" -> "01/01/2002" and I want to retrieve all records t

RE: Database Design Question

Thanks very much to every who helped me with my MySQL problem! I will probably go with the three table solution as it also eliminates the need for yet another table! Cheers, Ben - Before posting, please check: http://www.my

RE: Database Design Question

Hi Chris/Nick/Scalper, Thanks for the replies. I am not too sure how to implement this in tables, so I will give an example: Let's say I have three lists - a,b and c. List a contains 10,000 entries, list b contains 2,500 entries and list c contains 75,000 entries. I have a table of lists, with