Database

12-Feb-15 3:28

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

Row Rank in a MySQL View[^]

Richard Deeming12-Feb-15 4:26

Richard Deeming

12-Feb-15 4:26

Does Google not work where you are?

First result of searching for "mysql ranking view":

Use:

SQL

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

KipkoechE12-Feb-15 5:06

12-Feb-15 5:06

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

GuyThiebaut12-Feb-15 5:20

12-Feb-15 5:20

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 Wink | ;)

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

― Christopher Hitchens

KipkoechE12-Feb-15 5:27

12-Feb-15 5:27

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

GuyThiebaut12-Feb-15 5:33

12-Feb-15 5:33

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 Deeming12-Feb-15 5:34

Richard Deeming

12-Feb-15 5:34

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? D'Oh! | :doh:

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

KipkoechE12-Feb-15 5:53

12-Feb-15 5:53

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

KipkoechE12-Feb-15 6:03

12-Feb-15 6:03

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.

GuyThiebaut12-Feb-15 6:18

12-Feb-15 6:18

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

KipkoechE12-Feb-15 6:23

12-Feb-15 6:23

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

GuyThiebaut12-Feb-15 6:28

12-Feb-15 6:28

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

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

― Christopher Hitchens

KipkoechE12-Feb-15 7:05

Add allowance to employee table is wrong or true according to my case

12-Feb-15 7:05

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

ahmed_sa11-Feb-15 22:52

ahmed_sa

11-Feb-15 22:52

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

Re: Add allowance to employee table is wrong or true according to my case

David Mujica12-Feb-15 4:49

David Mujica

12-Feb-15 4:49

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. Thumbs Up | :thumbsup:

Re: Add allowance to employee table is wrong or true according to my case

Mycroft Holmes12-Feb-15 13:01

Mycroft Holmes

12-Feb-15 13:01

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

Triggers

Member 1116162511-Feb-15 20:19

Member 11161625

11-Feb-15 20:19

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...

Re: Triggers

Kornfeld Eliyahu Peter11-Feb-15 20:37

Kornfeld Eliyahu Peter

11-Feb-15 20:37

All you need is Google...
There are endless articles about triggers...Start here:
http://en.wikipedia.org/wiki/Database_trigger[^]
Overview of SQL Server database Triggers[^]

Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.

Re: Triggers

John C Rayan12-Feb-15 4:32

John C Rayan

12-Feb-15 4:32

Have a look at our code project article
Overview of SQL Server database Triggers[^]

RANKING IN VB.NET MYSQL

KipkoechE10-Feb-15 23:27

Re: RANKING IN VB.NET MYSQL

10-Feb-15 23:27

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

Richard MacCutchan11-Feb-15 1:06

Richard MacCutchan

11-Feb-15 1:06

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?

Re: RANKING IN VB.NET MYSQL

KipkoechE11-Feb-15 2:32