Database

1-Sep-08 12:42

hi,

i have a team_standing_table that is ordered by points and wins etc.
i would like to know the row number or position of a particular team.
what is the row number that has man utd? or chealsea?

(How to get the team in 1st,2nd, 12th etc i already know not the issue here)

Thanks D'Oh! | :doh:

nelsonpaixao@yahoo.com.br

Re: row number or index

Mycroft Holmes1-Sep-08 17:29

how to set a unique number to the group of resultset

1-Sep-08 17:29

assuming SQL Server 2005, add the following column

ROW_NUMBER() OVER(ORDER BY Points)

Never underestimate the power of human stupidity
RAH

Re: row number or index

ahsan sarfraz8-Sep-08 4:56

ahsan sarfraz

8-Sep-08 4:56

SELECT ROW_NUMBER() OVER (ORDER BY team_name ASC) AS ROWID, * from teams
where team_name like '%man utd%'

ROWID will give u the row number

chilll
Wink | ;)

remember me in your prayers
take care
FEEAMANALLAH

AHSAN SARFRAZ
UNIVERSITY OF ENGINEERING AND TECHNOLOGY TAXILA PAKISTAN
COMPUTER SYSTEM ENGINEERING DEPARTEMENT

G Nathan1-Sep-08 3:03

G Nathan

1-Sep-08 3:03

For each group I wants to set an UniqueId like this.

ID UniqueId

3 1001
3 1001

4 1002
4 1002

5 1003
5 1003

6 1004
6 1004

7 1005
7 1005

How can I.

Thanks in advance.

G Nathan

Re: how to set a unique number to the group of resultset

Blue_Boy1-Sep-08 4:49

Blue_Boy

1-Sep-08 4:49

I don't understand you!

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.

Re: how to set a unique number to the group of resultset

Tamer Oz1-Sep-08 8:19

Tamer Oz

1-Sep-08 8:19

Use unique identifier newId(), or write a cursor, or check address http://databases.aspfaq.com/database/how-do-i-return-row-numbers-with-my-query.html[^]

Plain weirdness (solved) [modified]

hairy_hats1-Sep-08 0:47

hairy_hats

1-Sep-08 0:47

Hi all,

I have a table containing a 4-byte int column, the largest value of which is 1354552. If I read that value into a C++ int using Carlos Antollini's ADO library here on CP, it returns -21704. The column started out as a bigint but got cut back, but none of the values in it were too big for an int.

Any ideas please?

Solved - there's already a patch on the project page. Note to self: search first, ask later.

Last modified: 40mins after originally posted --

How to use formulas in select statement

Rejjj31-Aug-08 16:48

Rejjj

31-Aug-08 16:48

Hi,
I am working in an application, where only select statement can be used. This is to create a report.

I need one column, where the data should be calculated according to a formula.
Base salary * no of months.

If the salary is same throughout the year it is base salary * 12.
If it changes in b/w, say 4 months, then it is (base salary * 4) + (new base salary * 8)

Another report has the details of the salary of all the months for that person. I can pull that column in this report. But the final report should have only one row for one person

Please help me with this.

Thanks a lot in advance... Smile | :)

Re: How to use formulas in select statement

Ashfield31-Aug-08 21:05

Ashfield

31-Aug-08 21:05

Try creating a function to return the value. The performance won't be great, but it will do the job.

Bob
Ashfield Consultants Ltd

Re: How to use formulas in select statement

Mycroft Holmes1-Sep-08 17:35

1-Sep-08 17:35

You can do it as a sub select. Trying to do it the way you describe is the most difficult methos and wuld require the UDF.

(Select Sum(Salary) from Table where Year = 2007) Salary

Never underestimate the power of human stupidity
RAH

VERY HUGE SP

nelsonpaixao31-Aug-08 12:49

31-Aug-08 12:49

Hi,

I am using virtual tables a lot (declare @my_table ...). Sometimes i have columns that accept null so inner join don´t work because doesn´t appear all values that are suposse to appear, and sometimes not either isnull() cleans that, if you know what i mean.
So, other day i got a +200 store procedure by use of virtual table. But it worked fine.
Now i have to copy a part of it to use in a bigger one. Dead | X|

Can anyone help me here OMG | :OMG:

nelsonpaixao@yahoo.com.br

Mycroft Holmes31-Aug-08 16:12

31-Aug-08 16:12

You want someone to do your coding? Or do you need instruction on copy paste?

I don't understand the 200+ reference (lines?)

Why don't you try breaking the proc down to sub procs. The bit you need to copy, cut it out of the original and make it produce the same results as the current code calling it as a sub proc. Then just include it in the "bigger" proc, you can also do this as a UDF that returns a table used by multiple procs.

Never underestimate the power of human stupidity
RAH

Paul Conrad2-Sep-08 19:16

Paul Conrad

2-Sep-08 19:16

Mycroft Holmes wrote:
You want someone to do your coding?

That's the OP's usual M.O. Hmmm | :|

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon

"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham

Tamer Oz1-Sep-08 8:21

Tamer Oz

1-Sep-08 8:21

I did not understand the main problem but try using left join or right join to show all values.

nelsonpaixao1-Sep-08 12:37

1-Sep-08 12:37

that not what i asked. the sp work. just wanted a comment to know if it happens to other people as well

nelsonpaixao@yahoo.com.br

Mycroft Holmes1-Sep-08 17:37

1-Sep-08 17:37

nelsonpaixao wrote:
to know if it happens to other people as well

All the bloody time - extracting a part of a process from a large proc so it can be reused is a fundemental part of the development cycle. Identifying the bits that can be reused is where the art of development come into play.

Never underestimate the power of human stupidity
RAH

routines in sql

nelsonpaixao31-Aug-08 12:28

31-Aug-08 12:28

Hi,

In c# i do this like everbody

routineA(string mystring, int myint);
routineB();
...

There is any equivalent to this in sql server?
I try to trigger some store procedures inside others but i can´t call variables inside.

create procedure C

as

begin

exec procedure B here i create a @table and fill it
...
...here i can´t do select´s or anything else
...

end

go

thanks

nelsonpaixao@yahoo.com.br

Re: routines in sql

Blue_Boy31-Aug-08 12:43

Blue_Boy

31-Aug-08 12:43

Are you trying to call stored procedure from within another stored procedure?

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.

Re: routines in sql

nelsonpaixao31-Aug-08 12:51