MYSQL RANKING IN A VIEW

[Richard Deeming]

Does Google not work where you are? First result of searching for "mysql ranking view":

Row Rank in a MySQL View[^]

Use:

SELECT t.id,
t.variety,
(SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM
FROM TABLE t

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[KipkoechE]

that one doesnt work ...thats why i resorted to codeProject...

[GuyThiebaut]

KipkoechE wrote:

that one doesnt work

Have a think about what would help us help you - phrases like 'that doesn't work' don't give us any useful information. What helps us help you are - the query you ran together with any error messages you received ;)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[KipkoechE]

SELECT t.id, t.variety, (SELECT COUNT(*) FROM TABLE WHERE id < t.id) +1 AS NUM FROM TABLE t; #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE WHERE id < t.id) +1 AS NUM FROM TABLE t LIMIT 0, 25' at line 3

[GuyThiebaut]

I take it you have a table in your database called TABLE with the columns id and variety within it? My initial guess is - probably not. You will need to take what Richard has given you and adapt it to your table.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[Richard Deeming]

So you copied the query from the StackOverflow answer verbatim, without making any effort to adapt it to your real table structure, and you expected it to work? :doh:

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[KipkoechE]

I created a table exactly the way it is for testing purposes before customizing to my needs

[KipkoechE]

sorry...i had not understand the structure of the table...modified to : create view vb as SELECT t.id, t.variety, (SELECT COUNT(*) FROM t WHERE id < t.id) +1 AS NUM FROM t t Results: id variety num 1 sss 1 2 sdsdssd 1 3 dfddd 1 need num to be : 1,2,3 instead of 1,1,1

[GuyThiebaut]

Your table aliases confused me and they may have confused MySQL too, try:

create view vb as SELECT t1.id, t1.variety, (SELECT COUNT(*) FROM t WHERE id < t1.id) +1 AS NUM FROM t t1

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[KipkoechE]

thanks so much for taking your time to kind me...God's Blessings. i Have learned a lot

[GuyThiebaut]

Less of the 'God' please although I am grateful for the Blessings part :)

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[KipkoechE]

finally the ranking is on ascending order and need it to be in such a way that the highest marks/value takes position 1: tried this: create view View_Positioning as SELECT t1.`SAdmNo`, t1.`YearAdmitted`,t1.`TermAdmitted`,t1.`CLASSAdmitted` ,t1.`StreamAdmitted`,t1.`OutOfMarks`,t1.`ENGLISH`,t1.`KISWAHILI`,t1.`MATHEMATICS`,t1.`SCIENCE`,t1.`SSR`,t1.`Average`,(SELECT COUNT(*) FROM total_termaverage_view WHERE `Average` IS NOT NULL AND`Average` < t1.`Average`) +1 AS Position FROM total_termaverage_view t1 order by Average ASC ; which gives: Average position 324 1 345 2