On Fri, Aug 13, 2010 at 1:45 PM, Dennis J. <[email protected]> wrote:
> On 08/13/2010 10:47 AM, erkin tek wrote: > >> assume *table* has *id, value* rows >> In mysql there is no special indicatior and we need to make funny things >> like >> >> 1) getting max valued row: >> select * from table , >> (select max(value) from table ) as t >> table.value=t.value >> > > Assuming you really only want one row (there could be several rows with the > same max. value after all): > > select * from table order by value desc limit 1 You'r right. But perhaps I thought to select from different tables. I used but cant remember why. 2) last inserted row > select * from table order by id limit 1 > What if you already have an entry with an id of say 5000 but then a script > deletes some rows and re-uses the id's? "last inserted row" and "row with > the highest id" are semantically different questions. > It would probably be better to add a date field and then do: > > select * from table order by date desc limit 1 I think id is to be auto increment field. I didnt include this information. In mysql, auto-incremented field is incremented. When it hits maximum value you get " #1062 - Duplicate entry '2147483647' for key 1" (for signed int field). I want last inserted row, and I know the limitations and behaviour of mysql, since I know highest number is the lastest insert. thus I gather lastest ins. row by asking highest, programmatically. 3) one random row > select * from table order by rand() limit 1 > This is a slow method to get a random row. A solution you see mentioned > often on the web is: > > select max(id) as max_id from table > random_id = random(1,max_id) > select * from table where id >= random_id order by id limit 1 another way is to keep a lookup table, to reflect inserts and deletion, dont create holes. Gather random number that will be the id of lookup table. And reach orginal table as like. Thus you can gather as much random row as you like. I think these solitions are only workaraounds. They are NOT elegant. > > I think there could faster, elegant row methods are required. >> like : >> 1) select maxrow(*) from table >> > > There would be no way for drizzle to determine what you mean with > maxrow(*). Do you mean the row where the id field is highest or where value > is highest? 2) select lastrow(*) from table >> > When do you really actually want to get only the last inserted row? > Usually "last" or "latest" means something in relation to the data you have > stored in the table e.g. if you are processing orders you don't want the > "last inserted row" but the "latest orders" which might actually be several > ones depending on how many orders are coming in. Again the "lastrow(*)" > syntax would be either not very useful or at least ambiguous. I'm not on the syntax, I'm on the idea. select * from table where id in maxofvalue(id, NumberOfRows, value) select * from table where id in random(id, NumberOfRows) select * from table where id in lastest(id, NumberOfRows) maxofvalue takes 3 arg, 1 is column of found rows, maximum row count, and the column to search maximum value. searches for maximum value, returns thereir id's as array. random takes 2 arg, 1 is column of found rows, maximum row count gathers id's of random rows. lastest takes 2 arg, 1 is column of found rows, maximum row count same as maxofvalue if autoincrement always increments, if it is not garantiied than last inserted row column. There should be database side solutions for lastest, random, maximum value of one field's rows. Perhaps these are redundant for you but they are queries that we have at least one in one project and life can be easier. Many cases it is not important to have an exact value. select max(value), id from table limit 1 if there is hundreds of id's, because there are hundreds of values at max, mysql returns only one! Oracle dont. That's why I like mysql. where are the row informations? does every storage engine provide ( I think some engines keep max, min values) column information. can these values store also row refrences?
_______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
