Auto Incrementing Number without using Identity

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Auto Incrementing Number without using Identity

Author

Topic

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 08:02:51

Hey all

I have a bit of a problem that i cant get my head around. I have to do a report with "Rankings", no the data for these reports are collaborated into a single table with a report id (report id is the identifing id in a relationship of 1 to many). Now the problem comes in that I dont want to use a cursor to do this, any ideas on how i can have a "1..2..3" ranking per report for certain criteria?

Any and all idea's welcome :)

WARNING: Running on cold coffee!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 08:05:12

Give us DDL!

Otherwise, try something like

select mt1.*, (select count(*) from mytable mt2 where mt2.pk <= mt1.pk)
from mytable mt1

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-18 : 08:06:33

Can you post the table structure and some sample data with expected output?

Chirag

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 08:27:21

Table Structure

TheIncrementedNumber|ReportID|JoinCodeStatus|JoinCode|MTDSales
1|1|0|'N00001'|300000
2|1|0|'N00002'|250000
3|1|0|'N00003'|245000
1|2|0|'N00001'|900500
2|2|0|'N00005'|900000
3|2|0|'N00002'|850000

TheIncrementedNumber is the one i want to create, it is done in order of the MTDSales DESC....

Hope that helps, and thanks for the quick replies :)

WARNING: Running on cold coffee!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 08:51:47

[code]-- Prepare test data
DECLARE @Test TABLE (ReportID INT, JoinCodeStatus INT, JoinCode VARCHAR(6), MTDSales INT)

INSERT @Test
SELECT 1, 0, 'N00001', 300000 UNION ALL
SELECT 1, 0, 'N00002', 250000 UNION ALL
SELECT 1, 0, 'N00003', 245000 UNION ALL
SELECT 2, 0, 'N00001', 900500 UNION ALL
SELECT 2, 0, 'N00005', 900000 UNION ALL
SELECT 2, 0, 'N00002', 850000

-- Do the work
SELECT (SELECT COUNT(DISTINCT MTDSales) FROM @Test t2 WHERE t2.ReportID = t1.ReportID AND t2.MTDSales >= t1.MTDSales) TheIncrementedNumber,
t1.*
FROM @Test t1
ORDER BY 2,
1[/code]How do you treat ties? Ie, there are two MTDSales with same value?

Peter Larsson
Helsingborg, Sweden

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 09:07:13

Thanks for the code...

now ties are a completely different issue

If there are 2 with the same MTD Sales then the count would be as follows

1|1|0|'N00001'|300000
2|1|0|'N00002'|250000
2|1|0|'N00003'|250000
4|1|0|'N00004'|245000

Nice and complex hehe :)

Trying your code out, thanks for the help so far

WARNING: Running on cold coffee!

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 09:20:59

Awesome code, thank you, I never would have thought of doing it that way... thanks so much. Now onto the 'tied' values

WARNING: Running on cold coffee!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 09:25:13

[code]-- Prepare test data
DECLARE @Test TABLE (ReportID INT, JoinCodeStatus INT, JoinCode VARCHAR(6), MTDSales INT)

INSERT @Test
SELECT 1, 0, 'N00001', 300000 UNION ALL
SELECT 1, 0, 'N00002', 250000 UNION ALL
SELECT 1, 0, 'N00004', 250000 UNION ALL
SELECT 1, 0, 'N00003', 245000

-- Do the work
SELECT (SELECT 1 + COUNT(*) FROM @Test t2 WHERE t2.ReportID = t1.ReportID AND t2.MTDSales > t1.MTDSales) TheIncrementedNumber,
t1.*
FROM @Test t1
ORDER BY 2,
1[/code]

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 09:27:03

A 1
B 2
B 2
D 4 (occupy ranks)

WARNING: Running on cold coffee!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 09:30:32

See previous post.

Peter Larsson
Helsingborg, Sweden

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 10:03:36

Thanks for the code, may i ask what were the other forms of ranking that you showed earlier?

Thanks again, you have made my life happy today (jobs of been a simpleton)

WARNING: Running on cold coffee!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-18 : 10:07:53

Sports results often ties with

A - 1
B - 2
B - 2
C - 4

Economic ranking often ties with

A - 1
B - 2
B - 2
C - 3

and I have only come across "late ranking" once in my career

A - 1
B - 3
B - 3
C - 4

Peter Larsson
Helsingborg, Sweden

jonathans
Starting Member

40 Posts

Posted - 2006-09-18 : 10:13:57

Thanks :)

This code still stuffs with my head at its simplicity.

I commend you on your knowledge.

WARNING: Running on cold coffee!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources