Record count help needed

Forum|Forum|12 years ago
June 20, 2013
1 reply
943 views

I am trying to wirte a MySQL query where I can display the record number returned in a repeat region... eg:

Record 1

Record 2

Record 3

Record 4

Record 5 etc...

I keep getting a stupid number or a '0' repeated in place of the incrementing number.

I have:

SELECT COUNT(*) AS Count, fld_fID, fld_fTHREADID FROM tbl_forumPOSTS WHERE fld_fTHREADID = %s

What am I doing wrong?

Thanks.

Server side applications

This topic has been closed for replies.

Correct answer bregent

>What am I doing wrong?

There is no row number in your SQL. COUNT() is an aggreate function that's used to count the number of records in a group - not to return a row number. In a SQL database, there really is no such thing as a row number as rows are returned in arbitrary order unless specified. If you just want to assign an incremental number to a row, either do it with a loop counter in your script, or try this method:

http://blog.gomilko.com/2007/04/28/mysql-rownum-imitation

B

bregentCorrect answer

Participating Frequently

>What am I doing wrong?

There is no row number in your SQL. COUNT() is an aggreate function that's used to count the number of records in a group - not to return a row number. In a SQL database, there really is no such thing as a row number as rows are returned in arbitrary order unless specified. If you just want to assign an incremental number to a row, either do it with a loop counter in your script, or try this method:

http://blog.gomilko.com/2007/04/28/mysql-rownum-imitation