Reverse count in mySQL

Question

id  one two thr fou five
1   37  84  1   68  10
2   72  50  87  41  67
3   66  30  89  57  48
4   29  27  35  75  36
5   2   72  9   1   55
6   33  89  17  40  64
7   70  90  63  26  54
8   36  19  51  43  61
9   10  61  20  44  84
10  2   41  43  65  87

I need to reverse count each number in the above table.

Examples:

61=>1 because if you count from the bottom to the first 61 there's 1 row
26=>3 because if you count from the bottom to the first 26 there are 3 rows
9=>5 because if you count from the bottom to the first 9 there are 5 rows
and so on...

The query should output a table similar to the following for all numbers:

Number  Rows count
61  1
26  3
9   5

The problem here is: How to reverse count in mySQL? Is there a special function? Thank you

How is the table ordered? By `id`? If so, is it guaranteed that `id` is sequential (i.e., no gaps)? — eggyal, Aug 29 '12 at 12:37
@eggyal ID is NOT sequential - juergend I haven't try anything because I don't know how to count rows from the bottom... — Floppy88, Aug 29 '12 at 12:40

score 1 · Answer 1 · answered Aug 29 '12 at 14:04

1

This would be cleaner if MySQL had an UNPIVOT function:

SELECT x.num, co.co - count(x.id) FROM
    (SELECT count(id) as co FROM tab) as co, 
    (SELECT t.num, max(t.id) as 'id' FROM
        (SELECT id, one as 'num' FROM tab
        UNION
        SELECT id, two as 'num' FROM tab
        UNION
        SELECT id, thr as 'num' FROM tab
        UNION
        SELECT id, fou as 'num' FROM tab
        UNION
        SELECT id, fiv as 'num' FROM tab) as t
    GROUP BY t.num) as x
    INNER JOIN
    (SELECT id FROM tab) as y on x.id >= y.id
GROUP BY x.num, co.co

answered Aug 29 '12 at 14:04

Jodaka

1,237
8
16

This assumes that row ids are increasing but not necessarily sequential. – Jodaka Aug 29 '12 at 14:09
1

The only solution that so far answers the question +1. However, why bother with `co` at all: just reverse the join criterion on table `y` to `x.id <= y.id` (for that matter, just join `tab` directly instead of the table `y` subquery)! http://sqlfiddle.com/#!2/dfba8/12 – eggyal Aug 30 '12 at 09:15

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

You can get the count you want with (Count(*) - id) if you order by id that should do it.

If not, I believe that you can use @curRow tag if the ids are not in order. take a look at these other questions:

MySQL get row position in ORDER BY

With MySQL, how can I generate a column containing the record index in a table?

edited May 23 '17 at 12:20

Community

1
1

answered Aug 29 '12 at 12:43

Sednus

2,095
1
18
35

score 0 · Answer 3 · answered Aug 29 '12 at 13:02

0

select COUNT(*) from TestTbl
where id > (select max(id) from TestTbl
where one='9' or two ='9' or three='9' or four='9' or five='9')

select COUNT(*) from TestTbl
where id > (select max(id) from TestTbl
where one='26' or two ='26' or three='26' or four='26' or five='26')

select COUNT(*) from TestTbl
where id > (select max(id) from TestTbl
where one='61' or two ='61' or three='61' or four='61' or five='61')

answered Aug 29 '12 at 13:02

srini.venigalla

5,137
1
18
29

Thank you, however the query should do that automatically for all numbers contained in the first table of my post. I shouldn't write each number in the query. – Floppy88 Aug 29 '12 at 13:18
It would be quite hard to accomplish that in inline sql, since you are trying to cross too many hurdles (1) you have to pivot the table to get each number (2) then you have to correlate that number to get the max via a a subquery. So, a better option is to write a stored procedure to do it. – srini.venigalla Aug 29 '12 at 13:40
my solution worked fine for me. i tested it, and it did what was asked. not that hard to accomplish. – jasonmclose Aug 29 '12 at 13:41

jasonmclose · Answer 4 · 2012-08-29T13:32:26.377

0

here is what i came up with. it's ugly.

select num, t1.max - t2.id as reverse_count from 
(select max(id) as max from testtable) as t1, 
(select id, row1 as num from testtable union all select id, 
 row2 as num from testtable union all select id, 
 row3 as num from testtable union all select id, 
 row4 as num from testtable) as t2;

edited to use the right naming schema

select num as Number, t1.max - t2.id as RowsCount from 
(select max(id) as max from testtable) as t1, 
(select id, one as num from testtable union all select id, 
 two as num from testtable union all select id, 
 thr as num from testtable union all select id, 
 fou as num from testtable union all select id, 
 five as num from testtable) as t2;

edited Aug 29 '12 at 13:32

answered Aug 29 '12 at 13:27

jasonmclose

1,667
4
22
38

i didn't follow your naming schema. sorry. row1 should be thought of as your column 'one', row2 should be 'two'. was in a hurry. – jasonmclose Aug 29 '12 at 13:29
I've tried your solution but it takes too much time to load (about 5 mins on my PC with XAMPP!) – Floppy88 Aug 30 '12 at 12:44
really? wow. if you have the ability to break this up into 2 queries, and get the MAX(id), so that you can pass it into a variable, you can use the 2nd half of the query. so, `select MAX(id) from testtable`, and then plug that into the second half of the function with (assume you have variable $MAX) `select t2.num as Number, ($MAX - t2.id) as RowsCount FROM (select id, one as num from testtable union all select id, two as num from testtable union all select id, thr as num from testtable union all select id, fou as num from testtable union all select id, five as num from testtable) as t2;` – jasonmclose Aug 30 '12 at 17:25
the hangup is probably all of the unions. but i don't know another way to compress all of the rows down into one column without using the unions. if you have a large dataset, then i could see it taking a while. your other option is to create a stored procedure that can work on each column independently. – jasonmclose Aug 30 '12 at 17:39

Reverse count in mySQL

4 Answers4