Order By and Ignore Punctuation
I would like to perform a query of a personnel database with an ORDER BY clause that ignores punctuation. For example, O'shea would sort after Osbourne, not to the beginning of the Os. Is this doable in the query? -= Bill =- -- You can tell a lot about a man by the way he handles these three things: a rainy day, lost luggage, and tangled Christmas tree lights. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using DISTINCT after the ORDER BY clause has been applied
At 11:33 PM + 3/13/07, [EMAIL PROTECTED] wrote: Date: Tue, 13 Mar 2007 20:56:08 +0530 To: mysql@lists.mysql.com From: Yashesh Bhatia [EMAIL PROTECTED] Subject: using DISTINCT after the ORDER BY clause has been applied Message-ID: [EMAIL PROTECTED] Hello: I had a quick question on using the DISTINCT clause in a SELECT query. I have the following table which stores webpages viewed table: page_viewed page_idint unsignedpage id of the page viewed user_id int unsigneduser id of the page viewed ts timestamptimestamp of the page view. Now i need to query the most recently viewed distinct pages and i have the following data page_id user_id ts 1 1 2007-03-13 20:40:46 2 1 2007-03-13 20:40:53 2 1 2007-03-13 20:41:01 1 1 2007-03-13 20:41:10 so basically i tried to write a query for recently viewed (for user_id 1) as follows SELECT DISTINCT page_id FROM page_viewed WHERE user_id =1 ORDER BY ts DESC however, this does not give me the result as i needed, i'd like to have it as page_id 1 2 but the output is page_id 2 1 therefore the DISTINCT clause would be first used to filter the rows and then the ORDER BY would be applied, is there anyway to specify that DISTINCT be applied after the ORDER BY clause ? if not, any other way i could retrieve the above data ? Thanks. Yashesh Bhatia. It looks to me as if your query returned exactly what you asked for. It found the first two rows (other rows are not distinct), and then ordered them in descending order by time stamp. Descending is largest to smallest. TS for row 2 is larger than TS for row 1. -= Bill =- -- You were born with all you need to win at life. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default Column Value
In one of my tables I have a start_date - timestamp (2007-07-04). A separate column, start_yr_mo, has 200704 (first seven characters of timestamp without the '-'). Both are entered manually. Can I define start_yr_mo as a default of, for example, set start_yr_mo = concat(substr(start_date, 1, 4),substr(start_date,6,2)). Something like ALTER TABLE events ALTER start_yr_mo start_yr_mo SET = concat(substr(start_date, 1, 4),substr(start_date,6,2)); -= Bill =- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]