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
(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
finally the ranking is on ascending order and need it to be in such a way that the highest marks/value takes position 1:
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 ;
Hi guys I have problem i need to make ERD relation entity
between employee and 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
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
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 ...
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???
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?