Mysql choosing rows after sorting

Gordon

I'm not sure if the title explains it, I'm a bit of a MYSQL novice..

I have a simple table with some entries: id, name, vote, comment for example.

How do I choose the nth selection of id after sorting the table by vote for example? I know how to sort but I can't figure out how to get the first result and the 7th and the 14th, 21st ... and the last. I know how to do this using MOD(id, 7) [or something like that] in the sql query but it just takes the ids in the table and outputs every seventh result. I need the seventh result after sorting.

I'm sure I'm not sounding clear, if so, apologies.

Tom Dunne

Could you do something like this (coming from an Oracle background, so it might not be exactly what you want):

SELECT <7th, 14th, 21st code> FROM
(
SELECT X FROM Y ORDER BY Z
)

Gordon

I'll try that, ta. I saw an Oracle method which was using numrow as Oracle seems to put an invisible index when sorting(?) but sql doesn't have this. thanks

showry

This might work, not sure how efficient it is:

Original table sorted by id desc:

mysql> select *
    -> from testa
    -> order by 1 desc;

+------+-------+
| id   | descr |
+------+-------+
|    6 | test6 | 
|    5 | test5 | 
|    4 | test4 | 
|    3 | test3 | 
|    2 | test2 | 
|    1 | test1 | 
+------+-------+
6 rows in set (0.00 sec)

get every second row sorted by id desc

mysql> select * 
           from 
           ( select @row := @row +1 as rownum, id, descr 
             from 
             ( select @row := 0) r, testa order by id desc) r1 
           where rownum %2 = 1;

+--------+------+-------+
| rownum | id   | descr |
+--------+------+-------+
|      1 |    6 | test6 | 
|      3 |    4 | test4 | 
|      5 |    2 | test2 | 
+--------+------+-------+
3 rows in set (0.00 sec)

Gordon

showry, I think that is what I'm needing, thanks for that. I'm going to play around with it now. Oh, and Inglesworth, just realised you changed your name! Thanks also.

Gordon

showry wrote: »

mysql> select * 
           from 
           ( select @row := @row +1 as rownum, id, descr 
             from 
             ( select @row := 0) r, testa order by id desc) r1 
           where rownum %2 = 1;

+--------+------+-------+
| rownum | id   | descr |
+--------+------+-------+
|      1 |    6 | test6 | 
|      3 |    4 | test4 | 
|      5 |    2 | test2 | 
+--------+------+-------+
3 rows in set (0.00 sec)

That's working great but I don't understand why! Is it creating a new table called 'row' or is it just outputting an individual row as it sees it (by +1)? The r and r1 bits lose me also.

The reason I ask is because I now want to pick a certain id and grab the surrounding limit around it (after the special sort).

amen

more fundamental question but why do you want to select the 7,14 ,21st row etc

showry

Gordon wrote: »

That's working great but I don't understand why! Is it creating a new table called 'row' or is it just outputting an individual row as it sees it (by +1)? The r and r1 bits lose me also.

The reason I ask is because I now want to pick a certain id and grab the surrounding limit around it (after the special sort).

Effectively the innermost select is just setting a variable to 0, the middle select is then incrementing it by 1 for each row returned and the outer select gets the first row and every nth row, 3rd in the case below. You could set @row each time and simplify the query:

mysql> select @row := 0;
+-----------+
| @row := 0 |
+-----------+
|         0 | 
+-----------+
1 row in set (0.00 sec)

mysql> select @row :=@row+1, id, descr from testa order by id desc; 
+---------------+------+-------+
| @row :=@row+1 | id   | descr |
+---------------+------+-------+
|             1 |    6 | test6 | 
|             2 |    5 | test5 | 
|             3 |    4 | test4 | 
|             4 |    3 | test3 | 
|             5 |    2 | test2 | 
|             6 |    1 | test1 | 
+---------------+------+-------+
6 rows in set (0.00 sec)

mysql> select @row := 0;
+-----------+
| @row := 0 |
+-----------+
|         0 | 
+-----------+
1 row in set (0.00 sec)

mysql> 
select *  from 
( select @row :=@row+1 as rownum, id, descr from testa order by id desc ) d1 
where rownum %3 = 1;
+--------+------+-------+
| rownum | id   | descr |
+--------+------+-------+
|      1 |    6 | test6 | 
|      4 |    3 | test3 | 
+--------+------+-------+
2 rows in set (0.00 sec)

mysql> 

Gordon

Bloody hell programming is complicated. showry, many thanks it'll take me another week to fully understand that, I'll get there though dammit!

amen wrote: »

more fundamental question but why do you want to select the 7,14 ,21st row etc

I'm zooming out of the search results to give a brief overview essentialy (first post on this thread), and then zooming in to all results around the chosen result (latest post #6).

Gordon

Hmm, showry is going to hate me.. I kinda get that a temporary table is being created but how do I grab the rownum of a specified id?

I'm tacking on at the end of your first code essentially, I want to add on - 'give me the rownum of id=5' please..

select * from 
	(select @row := @row +1 as rownum, id, url from 
		(select @row := 0) 
		r, entries order by url ASC) 
		r1 where rownum=(select rownum from entries where id=3)

This last line seems to be the problem, I can't work out how to find the results in this table with rownums.

showry

Hi Gordon,
Your query won't work because rownum isn't a column in the entries table, it's a column in the r1 subquery table.

I'm not sure what you're trying to get here - is it the position (rownum) in the sorted table of a specific ID?

So for example, the table sorted by url asc might look like

mysql> 
select * from 
   (select @row := @row + 1 as rownum, id, url from 
      (select @row := 0) dummy_table, entries order by url asc) outer_table;
+--------+----+-----+
| rownum | id | url |
+--------+----+-----+
|      1 |  3 | 100 | 
|      2 |  5 | 102 | 
|      3 |  1 | 107 | 
|      4 |  2 | 109 | 
|      5 |  4 | 115 | 
|      6 |  8 | 117 | 
|      7 |  7 | 120 | 
|      8 |  6 | 129 | 
|      9 | 18 | 133 | 
|     10 | 14 | 141 | 
|     11 | 10 | 145 | 
|     12 |  9 | 150 | 
|     13 | 11 | 160 | 
|     14 | 15 | 161 | 
|     15 | 13 | 165 | 
|     16 | 12 | 180 | 
|     17 | 17 | 199 | 
+--------+----+-----+
17 rows in set (0.00 sec)

You can now select from outer_table using whatever criteria you need e.g. rownum%7 = 1.
If you want to find what position in the sorted table a particular id is then you just need to add that id to a where clause:

mysql> 
select * from 
   (select @row := @row + 1 as rownum, id, url from 
      (select @row := 0) dummy_table, entries order by url asc) outer_table 
[B]where id = 3;[/B]
+--------+----+-----+
| rownum | id | url |
+--------+----+-----+
|      1 |  3 | 100 | 
+--------+----+-----+
1 row in set (0.00 sec)

If I've picked it up wrong let me know what rows you're looking for in the sorted set of data and we can figure out how to get it.

Gordon

showry wrote: »

mysql>
select * from
(select @row := @row + 1 as rownum, id, url from
(select @row := 0) dummy_table, entries order by url asc) outer_table
where id = 3;
+

---

+----+

---

+
| rownum | id | url |
+

---

+----+

---

+
| 1 | 3 | 100 |
+

---

+----+

---

+
1 row in set (0.00 sec)
[/Code]

If I've picked it up wrong let me know what rows you're looking for in the sorted set of data and we can figure out how to get it.

I managed to figure that 'where id = 3' bit out, but the problem is that I want to get the chosen row of id=x
PLUS
rownum (of id=3) +3 (plus id/descr/etc etc)
rownum (of id=3) +2 (plus id/descr/etc etc)
rownum (of id=3) +1 (plus id/descr/etc etc)
rownum (of id=3) 0 (plus id/descr/etc etc)
rownum (of id=3) -1 (plus id/descr/etc etc)
rownum (of id=3) -2 (plus id/descr/etc etc)
rownum (of id=3) -3 (plus id/descr/etc etc)

So to do this I thought I could SELECT the rownum of the id numbered 3:

select * from
(select @row := @row + 1 as rownum, id, url from
(select @row := 0) dummy_table, entries order by url asc) outer_table 
[b]where rownum=(select rownum from entries where id=3);[/b]

And maybe I could do this:

where rownum=(select rownum from entries where id=6) ||
rownum +1 =(select rownum from entries where id=6) ||
rownum+2=(select rownum from entries where id=6) ||
etc
etc

Or something silly like that.

But mysql doesn't like my alternative as it returns all my results instead of id=3 (without even delving into the multiple OR statements).

Apologies for the scattyness..

showry

Am not quite sure I understand what you're trying to return but herself is out and I've a race tomorrow so what else would I be doing of a Saturday night.

Are you trying to get the 3 rows either side of a given id? So for example in my data set above, for the id=8 would you expect to see the following?
ID URL
1 107
2 109
4 115
8 117
7 120
6 129
18 133

Gordon

Yes exactly!

showry

Try this:

mysql> 
select id, url  from 
   (select @row := @row + 1 as rownum, id, url, 
             if(id=8, @mid:=@row,@mid)  as mid 
    from        
      (select @row := 0) dummy_table, entries 
       order by url asc) 
outer_table 
where rownum >= mid-3 and rownum <= mid+3;
+----+-----+
| id | url |
+----+-----+
|  1 | 107 | 
|  2 | 109 | 
|  4 | 115 | 
|  8 | 117 | 
|  7 | 120 | 
|  6 | 129 | 
| 18 | 133 | 
+----+-----+
7 rows in set (0.00 sec)

Gordon

Woah, I'm testing the code and it doesn't give me the mid-3 results (it gives me anything and everything ABOVE the mid number, even if I change the mid number) but Ill play around with it, I'm probably doing something wrong. You rock.

It works if I replace the last line with where rownum >= mid, but gives me nothing if I state <= mid
the following gives me the full list of results expected

select id, url from 
   (select @row := @row + 1 as rownum, id, url, 
             if(id=8, @mid:=@row,@mid)  as mid 
    from        
      (select @row := 0) dummy_table, entries 
       order by url asc) 
outer_table 
[b]where rownum >= mid[/b]
+----+-----+
| id | url |
+----+-----+
|  8 | 117 | 
|  7 | 120 | 
|  6 | 129 | 
| 18 | 133 | 
etc
+----+-----+

the following just gives me one result - the mid result

select id, url from 
   (select @row := @row + 1 as rownum, id, url, 
             if(id=8, @mid:=@row,@mid)  as mid 
    from        
      (select @row := 0) dummy_table, entries 
       order by url asc) 
outer_table 
[b]where rownum <= mid[/b]
+----+-----+
| id | url |
+----+-----+
|  8 | 117 | 
+----+-----+

stevenmu

This isn't answering your question directly, but I think your coming at this from the wrong angle. As a rule SQL is about manipulating (manipulating can be selecting, updating, inserting, deleting etc) entire sets of data. While just one record may be the entire set of data, it's not really good at manipulating single records within a set of data, and when you start trying to do that it's a good sign that you are looking at the problem in the wrong way.

What you really want to do is use SQL to return your result set back to your application and then manipulate the individual records in there.

Using the example you provided I think what you are trying to do is show a list of search results with minimal details, say ID and Name. The by zooming in I presume you mean showing the complete details, i.e. including Vote and Comment. There's two ways I would suggest doing this, one is to just select all of the fields for every record that matches your search, then in your application code you can easily pick out the 7th records and display the full details. Or you could just select the ID, Name fields and then in your application code pick out the records to zoom in on and fire off extra queries for each of those to get the full details.

The point being with both methods is that SQL is just returning data sets and you are addressing the individual records within that data set with your application code.

Gordon

I hear ya steven, maybe I am using mysql when I don't need to. I'm going to output the whole of the database and then use php to grab the necessaries and see how that goes. I just thought that you should use mysql to grab all the relevant info from the database (ie look for only the records you need) and then php to manipulate that info accordingly. I thought it was better on server load or summit..

stevenmu

The trick really is to find the balance between what you do on the database server and what you do in the application. In general it's more efficient to select as few records as needed, but if you look at the samples suggested above, in the SQL you are already selecting all of the records, then adding them to a temp-table, and then selecting again from that. Since you're already selecting all of the records, I would suggest that in this case it's actually more efficient to just return all of them to the application and let that process them since your application code can more efficiently process individual records in the result set. An exception to this would be if the link between your application and your DB server is very slow (modem etc), or if the records are very very big.

To give another example, if you wanted every 7th record where the vote was yes and the id was < 500, you would filter that first on the server, selecting all records with vote=yes and id<500, then return that result set to the application and let it pick out every 7th.