0

i have this table and it can be easily sorted by order by id asc or desc but it will always give same result so there is only possibility of getting two results either by asc or desc here is tablea

                    id              linkid      

                    5454             766
                    5453             766
                    5452             766
                    5451             766
                    5450             766
                    5449             766
                    5448             765
                    5447             765
                    5446             765
                    5445             765
   select * from tablea where linkid='766' order by id desc limit 1

   or
                select * from tablea where linkid='766' order by id asc limit 1

when i use above query i have only two options either to use asc or desc which can sort linkid 766 only in two ways and i will get output of either id 5454 or 5449 but i want to use any one of the id correspnding to linkid .i want output for linkid 766 like this.the query should give any one value not only first or last value which i am getting by using asc or desc but again i dont want to use rand() as it is dead slow

i can try rand() but it will be dead slow 

     select * from tablea where linkid='766' order by rand()  limit 1

any idea how can be achieved.

suhani
  • 19
  • 5
  • `rand()` is the right solution for what you want to do. Have you timed it to determine that its performance really is worse than the other methods? You should also compare to another column that is not a primary key. – Gordon Linoff May 07 '13 at 00:37
  • 1
    Possibly newID()? [link](http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table/848908#848908) – xQbert May 07 '13 at 00:40
  • @xQbert first time heard about newid thanks – suhani May 07 '13 at 00:44
  • @xQbert if i know no of such rows with same id can i use order by id+number – suhani May 07 '13 at 00:58

2 Answers2

0

If your expected result set from the query is small enough, then you may want to consider using app tier for randomizing your selection. I do not know your database infrastructure or load but general rule of thumb I use is that "App Tier is far more easier to scale then the database tier"!

Having said that, I know there are some situations where you really want to randomize results at the DB tier. We had to do that in couple of instances and in our research, we stumbled upon following which seems to have worked for us. http://www.rndblog.com/how-to-select-random-rows-in-mysql/

It doesn't make sense to repeat everything here, but for the sake of ensuring that if the page at the given link disappears, here is a high level summary. Basically, what this link is discussing is.... instead of using RAND in the order by clause (which can be very expensive)

SELECT col1 FROM tbl ORDER BY RAND() LIMIT 10;

Use it in the WHERE clause:

SELECT col1 FROM tbl WHERE RAND()<=0.0006 limit 100;

Hope this helps you.

smallworld
  • 910
  • 7
  • 15
  • i have checked rand() it will simply kill my server – suhani May 07 '13 at 00:44
  • I am assuming that you looked at the difference between RAND() in the ORDER BY vs. WHERE in my above post. No matter what, RAND at DB tier is going to be expensive. How much data are we talking about? What is your database infrastructure like? i.e. how big is database, what kind of CPU/memory is available to it. How many rows is your query expected to return with a given where clause? – smallworld May 07 '13 at 00:47
  • if i know no of such rows with same id can i use order by id+number – suhani May 07 '13 at 00:57
  • @suhani before you try any of what I suggested, you should try what xQbert suggested. I have not used that approach before, but looks like it will provide you a more elegant solution. Try that first. – smallworld May 07 '13 at 01:08
0

Have you try this?

SELECT * FROM table LIMIT 1 ORDER BY id DESC

Or

SELECT * FROM table ORDER BY id DESC
TheCrazyProfessor
  • 919
  • 1
  • 15
  • 31