MYSQL RANKING IN A VIEW
-
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 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
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
-
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
I take it you have a table in your database called
TABLEwith the columnsidandvarietywithin 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
-
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
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
-
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
-
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
-
-
-
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
-
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
