|
is it possible to create a view in mysql with ranking row?
I tried the following:
CREATE VIEW MYVIEW AS SELECT `SAdmNo`, `Average`, CASE WHEN @PREVRANK=`Average` THEN @CURRANK WHEN @PREVRANK :=`Average` THEN @CURRANK :=@CURRANK+1 END AS RANK FROM `total_termaverage_view`, (SELECT @CURRANK :=0,@PREVRANK :=NULL)R ORDER BY `Average` DESC ;
and got the following:#1351 - View's SELECT contains a variable or parameter
|
|
|
|
|
Does Google not work where you are?
First result of searching for "mysql ranking 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
|
|
|
|
|
that one doesnt work ...thats why i resorted to codeProject...
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I created a table exactly the way it is for testing purposes before customizing to my needs
|
|
|
|
|
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
-- modified 12-Feb-15 11:12am.
|
|
|
|
|
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
|
|
|
|
|
thanks so much for taking your time to kind me...God's Blessings. i Have learned a lot
|
|
|
|
|
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
|
|
|
|
|
Hi guys I have problem i need to make ERD relation entity
between employee and allowance
Employee table
Name
address
Basic Salary
Bonus
Allowance table
House rent
Food Allowance
Moving Allowance
Basic Salary is monthly and fixed
Bonus is monthly and fixed
food allowance is monthly and fixed for married employee
House rent is monthly and fixed for some employee and some employee take house rent two time in year every 6 month
every employee married take 3 months salary from basic salary in year
suppose i m married and i take basic salary 5000
i will take rent 5000 x 3=15000/12=1250 monthly
some employee take rent every half year meaning every 6 month
meaning 15000/2=7500
My question according to my case above
Which is best put allowance in table allowance or put allowance(food,housing,moving)
in employee table and what relation between two tables
|
|
|
|
|
Some things to consider in your design ...
How about a design like this:
employee Key
Allowance Code
Effective_Start_Date
Effective_End_Date
Allowance Rate
This may not be complete, but what it does for you is that it allows for many types of allowance codes to be associated with an employee, each allowance code has an effective date range with an associated rate. This also allows for 2 employee who have the same allowance code to be compensated differently.
You would then need another table which translates the Allowance Code to a description.
I use a similar approach when dealing with prices, you have the history of the prices over time ...
Just a thought.
Good luck. 
|
|
|
|
|
What David has described is a many to many relationship, an allowance can be used by many employees and an employee can have many allowances. This is the correct data structure for the requirements.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI,
EveryBody,
I am new in sql server,i want to know what is trigger? and What are the different types of trigger and What it's use with sample example???Can you provide me a link where there are good examples about triggers???
Thanks...
IN Advance...
|
|
|
|
|
|
|
I have Records which are supposed to be assigned Position/Be Ranked e.g
RegNo Marks Position
1 300
3 301
4 403
5 345
My Problem is How to rank/give position according to marks attained such that the results will be displayed as:
RegNo Marks Position
1 300 4
3 301 3
4 403 1
5 345 2
using VB.NET MYSQL
|
|
|
|
|
KipkoechE wrote: how i will generate the position based on marks
Probably by some calculation and sorting. But if you want some more useful help then please edit your question and explain the problem in proper detail. Also, is this really a Database question or a VB one?
|
|
|
|
|
I have Records which are supposed to be assigned Position/Be Ranked e.g
RegNo Marks Position
1 300
3 301
4 403
5 345
My Problem is How to rank/give position according to marks attained such that the results will be displayed as:
RegNo Marks Position
1 300 4
3 301 3
4 403 1
5 345 2
using VB.NET MYSQL
|
|
|
|
|
You don't need the position field as that depends on the Marks. Just select your data using the ORDER BY clause[^] to sort them.
|
|
|
|
|
but that will only sort it...What about the position
|
|
|
|
|
A bit of google fu[^]gave me this:
SELECT
RegNo,
Marks,
@Position := @Position + 1 AS Position
FROM results , (SELECT @Position:= 0) r
ORDER BY Marks desc;
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 11-Feb-15 9:01am.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
