Sno Generation Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Sno Generation Problem

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-01-21 : 01:07:23

Table :sales_hdr

sid stotal
---- -----------
s001 15000
s002 12000

Table :Sales_dtl

sid itm_id qty
---- ------ -----------
s001 i001 2
s001 i002 10
s001 i003 100
s002 i022 1
s002 i033 125

Just the output should include sno
based on sales id(sid)

Tried:
select row_number() over (order by hdr.sid,itm_id,qty) as s_no,
hdr.sid,itm_id,qty
from sales_hdr hdr inner join sales_dtl dtl
on hdr.sid = dtl.sid

s_no sid itm_id qty
-------------------- ---- ------ -----------
1 s001 i001 2
2 s001 i002 10
3 s001 i003 100
4 s002 i022 1
5 s002 i033 125

Actual output should be :
s_no sid itm_id qty
-------------------- ---- ------ -----------
1 s001 i001 2
2 s001 i002 10
3 s001 i003 100
1 s002 i022 1
2 s002 i033 125

can any one help me?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-21 : 01:09:38

use rank() or dense_rank() functions

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-21 : 01:10:33

select row_number() over (partition by hdr.sid order by hdr.sid,itm_id,qty) as s_no,
hdr.sid,itm_id,qty
from sales_hdr hdr inner join sales_dtl dtl
on hdr.sid = dtl.sid

Jai Krishna

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-01-21 : 01:12:39

Thanks Jai krishna.its working fine.Txs a lot..

quote:
Originally posted by Jai Krishna

select row_number() over (partition by sid order by hdr.sid,itm_id,qty) as s_no,
hdr.sid,itm_id,qty
from sales_hdr hdr inner join sales_dtl dtl
on hdr.sid = dtl.sid

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-21 : 01:14:47

Welcome

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:43:42

quote:
Originally posted by bklr

use rank() or dense_rank() functions

how would it give you unique numbers for each sid group?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources