Re: Finding a record in a result set
Right now I'm trying to use PHP to do a binary search on the result set so I don't have to traverse the entire result set. I'm using PHP's mysql_data_seek() to move the pointer within the result set and looking at the data. What do people think of this approach? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding a record in a result set
That would only work if the result set is sorted by name. You said you wanted to sort by hiring date, that's not going to work. As for the general approach, I don't have enough experience to judge. How big would you expect the result set to be? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 1:05 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set Right now I'm trying to use PHP to do a binary search on the result set so I don't have to traverse the entire result set. I'm using PHP's mysql_data_seek() to move the pointer within the result set and looking at the data. What do people think of this approach? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- 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]
Re: Finding a record in a result set
James, one option would be to run a query to find the number of people in the list ahead of him, rather than determining position within the result set. As in: SELECT COUNT(*) FROM some_table WHERE state = Maine AND hire_date (SELECT hire_date FROM some_table WHERE last_name = Smith AND first_name = Joe AND state = Maine) Dan On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James
Re: Finding a record in a result set
Ok, sorry. When I described the initial scenario it wasn't exactly accurate. I want to find Joe in the list of everyone hired on the same date as Joe. So when I do my query,something to the effect of: select from users where hiring_date=$target_date ORDER BY user_uniq_id; Now the result set should be sorted by user_uniq_id. Then I do a binary search using Joe's user_uniq_id. (note user_uniq_id is an auto incremented field) That should work, no...is there a faster/better way to do this? Some people suggested creating a temporary table, but I'm just concerned that there may be memory impacts on the server if lots of people require this query. The result set could be pretty large...1000, 10,000, maybe even 100,000 if this app becomes popular. :) -James On Apr 4, 2007, at 1:21 PM, Jerry Schwartz wrote: That would only work if the result set is sorted by name. You said you wanted to sort by hiring date, that's not going to work. As for the general approach, I don't have enough experience to judge. How big would you expect the result set to be? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: James Tu [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 04, 2007 1:05 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set Right now I'm trying to use PHP to do a binary search on the result set so I don't have to traverse the entire result set. I'm using PHP's mysql_data_seek() to move the pointer within the result set and looking at the data. What do people think of this approach? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- 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]
Re: Finding a record in a result set
That is a nice idea, I'll have to keep it in my bag of tricks. However, I don't know if it will work b/c there are probably others that are hired on the same date... On Apr 4, 2007, at 1:51 PM, Dan Buettner wrote: James, one option would be to run a query to find the number of people in the list ahead of him, rather than determining position within the result set. As in: SELECT COUNT(*) FROM some_table WHERE state = Maine AND hire_date (SELECT hire_date FROM some_table WHERE last_name = Smith AND first_name = Joe AND state = Maine) Dan On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Then you just have to come up with some other criteria for determining who should be counted as before or after Joe, which might well be the same as the order by clause in whatever you're doing right now while examining the result set. I think your approach of examining the result set will work well in testing. In practice, with load, it could quickly become a real problem. Databases are very good at providing answers about the data they contain, while languages like PHP are very good at emitting HTML ... use each to its fullest potential. Looping through thousands of results provided by the database to find one record is not efficient in terms of either the database or PHP. Dan On 4/4/07, James Tu [EMAIL PROTECTED] wrote: That is a nice idea, I'll have to keep it in my bag of tricks. However, I don't know if it will work b/c there are probably others that are hired on the same date... On Apr 4, 2007, at 1:51 PM, Dan Buettner wrote: James, one option would be to run a query to find the number of people in the list ahead of him, rather than determining position within the result set. As in: SELECT COUNT(*) FROM some_table WHERE state = Maine AND hire_date (SELECT hire_date FROM some_table WHERE last_name = Smith AND first_name = Joe AND state = Maine) Dan On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Let me describe the problem another way, too. It's related to creating a paging interface to view many records. I figured that people deal with paging all the time and the solution to my problem may already be out there. Typically when you access a single record via a top down method, i.e. you page through records and then you click on a specific record, you still have information that allows you to go back to the paging view. But what do people do if: (1) Somehow, while you're browsing a single record, the database is updated and records are added. The order of the record that your browsing within the entire list of records could be shifted. When you go back to page view, you actually need to figure out the position, within your overall result set, of the current record that your browsing and then you have to figure out which 'new' page the record appears on. or (2) You browse to a specific record via let's say a search, and then you want to step back to page view to see where this record lives in the overall result set. (I guess this problem is the same as (1), b/c it's all about going from a detail view to a page view and figuring out it's position relative to everything so you can form the pages accordingly) Is there a way to do this in MySQL through a query or do I need to get the ENTIRE result set into, let's say PHP and figure it out, and then do another query for just a page of results? -James On Mar 22, 2007, at 11:21 AM, James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Thanks Maciek: The table that I'm doing this query on will be huge. It's essentially the users table for an online activity with, we hope, lots of users. :) The thing is that if I do a query for the entire result set and use PHP to figure out the position of the user and then do a query on a page of results that will contain the user, I'm still going to take a hit right? Or are you concerned about performance b/c MySQL and subqueries are really slow? -James On Mar 23, 2007, at 8:05 PM, Maciej Dobrzanski wrote: James Tu [EMAIL PROTECTED] wrote in message = news:[EMAIL PROTECTED] I want to do a query of all employees from Maine, ordered by hiring =20 date, and figure out where Joe falls in that list. (i.e. which record = number is he?) I think this can only be accomplished with a temporary table. SET @n:=3D0; SELECT t.name, t.n FROM (SELECT @n:[EMAIL PROTECTED] AS n, name FROM t WHERE state = =3D 'Maine' ORDER BY hire DESC) t WHERE t.name =3D 'Foo'; This query though may not be suitable for most situations as its = performance depends heavly on the size of the derived table.=20 Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
Maybe this one could do (untested) ? SELECT COUNT(*), ( SELECT count(*) FROM employees AS t2 WHERE t2.name = 'Joe' AND t2.state = 'ME' AND t2.hiredate = datevalue) AS joesexist FROM employees WHERE name = 'Joe' AND state = 'ME' AND hiredate = datevalue; Jerry Schwartz ha scritto: I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- 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]
Re: Finding a record in a result set
James Tu [EMAIL PROTECTED] wrote in message = news:[EMAIL PROTECTED] I want to do a query of all employees from Maine, ordered by hiring =20 date, and figure out where Joe falls in that list. (i.e. which record = number is he?) I think this can only be accomplished with a temporary table. SET @n:=3D0; SELECT t.name, t.n FROM (SELECT @n:[EMAIL PROTECTED] AS n, name FROM t WHERE state = =3D 'Maine' ORDER BY hire DESC) t WHERE t.name =3D 'Foo'; This query though may not be suitable for most situations as its = performance depends heavly on the size of the derived table.=20 Maciek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding a record in a result set
Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding a record in a result set
This may sound a little cheesy, but hear me out. Create a temp table in memory holding the result of the your employee query like this: CREATE TEMPORARY TABLE tmpEmpFromState ( EMPNAME VARCHAR(60), HIRED DATE, ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (ID), KEY OrderOfHire(HIRED,ID) ) ENGINE=MEMORY; Load the Results of your Query INSERT INTO tmpEmpFromState (HIRED,EMPNAME) SELECT HireDate,CONCAT(LastName,', 'FirstName) FROM... WHERE EmpState='ME' ORDER By HireDate; The table tmpEmpFromState is now loaded in order by HireDate. If on any given date, multiple employees were hired, then the employees that were hired on the same date will be ordered chronologically by the order in which the employee was enter in the database. If the table with the HireDate inquery select query has a timestamp, then order your select by hirdate,timestamp to guarantee this ordering scheme. Now get ID from tmpEmpFromState for Employee Joe Cool SELECT ID FROM tmpEmpFromState WHERE EMPNAME='Cool, Joe'; Give it a try. - Original Message - From: James Tu [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, March 22, 2007 11:21:34 AM (GMT-0500) Auto-Detected Subject: Finding a record in a result set Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James -- 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]
Re: Finding a record in a result set
Remember ME is Maine MA is Massachusettes - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: James Tu [EMAIL PROTECTED] Cc: MySQL List mysql@lists.mysql.com Sent: Thursday, March 22, 2007 12:32:41 PM (GMT-0500) Auto-Detected Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- 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]
RE: Finding a record in a result set
I don't think that will work. If there are 1,000 records that qualify but none for Joe, then it will return 1,001. If Joe is in record 1 of the retrieved record set, and there are 999 other people who match the WHERE clause, then it will retrieve 1,000. Am I missing something? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Thursday, March 22, 2007 12:33 PM To: James Tu Cc: MySQL List Subject: Re: Finding a record in a result set I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) If 'Joe' is a unique name LOL... SELECT 1 + COUNT(*) FROM employees WHERE name 'Joe' AND state = 'MA' AND hiredate datevalue; PB James Tu wrote: Is there some quick way to do the following in MySQL? (I know I can use PHP to search through the result set, but I wanted to see if there's a quick way using some sort of query) Let's say I know that Joe is from Maine. I want to do a query of all employees from Maine, ordered by hiring date, and figure out where Joe falls in that list. (i.e. which record number is he?) -James --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 268.18.17/730 - Release Date: 3/22/2007 7:44 AM -- 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]
