1

Suppose I have a data set called table which has 7 data such as [a,b,c,d,e,f,g]. I want to write a query to find out the number of a specific data. For example,I want to find 'd'.The sql should give me a number '3'. If I want to find 'g'.The sql should give me a number '6'. Please help.Thank you.

Michael
  • 183
  • 2
  • 10
  • So the array is stored in db? – George Sharvadze Sep 19 '17 at 07:49
  • 1
    What RDBMS you are using? SQL Server, MySQL, or what? –  Sep 19 '17 at 07:49
  • 1
    This is a very poor question. To improve it: post a real table with create table. Also specify a database brand or just say 'agnostic sql'. Take in mind than the answer is different for each database branch. Also explain what did you tried. – dani herrera Sep 19 '17 at 07:49
  • May be this will help you [https://stackoverflow.com/questions/8738785/how-to-find-row-number-of-a-record](https://stackoverflow.com/questions/8738785/how-to-find-row-number-of-a-record) – siva Sep 19 '17 at 07:51
  • thank you siva.That's is useful and help me a lot. – Michael Sep 19 '17 at 07:56
  • @siva, yes, this is nice explained. Michael, you can learn how to make question here: [How to find row number of a record?](https://stackoverflow.com/questions/8738785/how-to-find-row-number-of-a-record) – dani herrera Sep 19 '17 at 07:57

1 Answers1

3

Since you are using Mysql which doesn't support ranking functions, then you can do this:

SELECT col, rank -1 as rownumber
FROM
(
  SELECT col, 
         @rownum := (@rownum + 1) AS rank
    FROM Table1 t, 
         (SELECT @rownum := 0) r
) AS t

mysql demo

This will give you:

| col | RowNumber |
|-----|-----------|
|   a |         0 |
|   b |         1 |
|   c |         2 |
|   d |         3 |
|   e |         4 |
|   f |         5 |
|   g |         6 |

Then you can just filter on the the value you want.