1

In MySQL, how would I find the row number of the first occurrence of a value?

E.G., if the data returned by my query was

value1
value1
value1
value1
value2
value2
value1

I would want to return 1 is searching on value1, and 5 if searching on value2.

Is this possible? I can't find any documentation on it.

Sinister Beard
  • 3,570
  • 12
  • 59
  • 95
  • 3
    Add an auto increment primary key row id. That is the simpliest. – vaso123 Apr 25 '16 at 13:52
  • Maybe you can select all values, and try to iterate through them. https://dev.mysql.com/doc/refman/5.5/en/loop.html But I think, you need to rethink your table design. – vaso123 Apr 25 '16 at 14:01
  • do you have `id` column? – Alex Apr 25 '16 at 14:03
  • 1
    Note that unless `order by` is specified the row number doesn't contain particularly useful information – Andy Nichols Apr 25 '16 at 14:06
  • @alex - I do, but this is the result of a query, so I don't want to use the table's id, I want to count it up in the results. – Sinister Beard Apr 25 '16 at 14:07
  • but you can just set any of those to 1 (constant) then. if you are not really tracking related data ;-) and you just want to "find" 1st one. any of those records could be titled "first". if you don't need number for all of them, you can use any of them as first. that has very few sense to me. I guess, you'll finally use `id`. You just did not get why you need that, but I am sure you will :-) – Alex Apr 25 '16 at 14:15

2 Answers2

1

Add a row number column in your select statement.

Input

yourtable
samplefield
value1
value1
value1
value1
value2
value2
value1

Code

SELECT @n := @n + 1 RowNumber, t.*
FROM (SELECT @n:=0) initvars, yourtable t

Output

SQL Fiddle: Coming soon! SQL Fiddle is erroring :(

Matt
  • 14,906
  • 27
  • 99
  • 149
-1

Thanks to lolka_bolka and this answer, I came up with the following solution. To save me editing my actual query, a little bit of additional information: I was trying to find the first occurrence of a post-type that wasn't an attachment in WordPress:

SELECT `type_count` FROM (SELECT @s:=@s+1 type_count,`post_type` 
    FROM (SELECT @s:= 0) AS s, `wp_posts` 
    WHERE wp_posts.post_type IN ('post', 'attachment') 
    AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'inherit')) 
    AND wp_posts.post_mime_type NOT LIKE 'image/%' ORDER BY `ID` DESC) 
AS `type_counts` 
WHERE `post_type` != 'attachment' LIMIT 0,1
Community
  • 1
  • 1
Sinister Beard
  • 3,570
  • 12
  • 59
  • 95