Generate sno - 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

Generate sno

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-16 : 04:55:06

Hot to Generate sno based on invoice:


select invoice_no,invoice_date,invoice_amount from invoice_dtl


 invoice_no          invoice_date   invoice_amount 
 FASINV/000006/0809	30 Apr 2008	2765659.38000000
 FASINV/000009/0809	30 Jun 2008	1230304.92000000
 FASINV/000009/0809	30 Jun 2008	1230304.92000000
 RSL/UK/08-09/001	01 Apr 2008	   9517.50000000

output should be :


sno  invoice_no invoice_date   invoice_amount 
1 FASINV/000006/0809 30 Apr 2008 2765659.38000000
2 FASINV/000009/0809 30 Jun 2008 1230304.92000000
2 FASINV/000009/0809 30 Jun 2008 1230304.92000000
3 RSL/UK/08-09/001   01 Apr 2008    9517.50000000

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-16 : 05:10:56

declare @temp table (invoice_no varchar(32), invoice_date varchar(32), invoice_amount decimal(18,2))
insert into @temp
select 'FASINV/000006/0809' ,'30 Apr 2008', 2765659.38000000 UNION ALL
select 'FASINV/000009/0809', '30 Jun 2008', 1230304.92000000 UNION ALL
select 'FASINV/000009/0809', '30 Jun 2008', 1230304.92000000 UNION ALL
select 'RSL/UK/08-09/001', '01 Apr 2008' , 9517.50000000

select dense_rank() over ( order by invoice_no) AS sno,* from @temp

jbp_j
Starting Member

24 Posts

Posted - 2009-02-16 : 05:11:45

hi,

Try this,

select * from ( select row_number() over ( partition by invoice_no,invoice_date order by invoice_no ) as sno,* from Yourtable)
as t

jbp_j
Starting Member

24 Posts

Posted - 2009-02-16 : 05:16:21

[quote]Originally posted by jbp_j

hi,

Try this,

select * from ( select dense_rank() over ( order by invoice_no ) as sno,* from yourtable)
as t

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-16 : 05:21:51

if u using Sql Server 2005 then use row_number() function as above suggested,,,,,,,,,,
else
If u r working in sql 2000 den
jus add a column to ur Table which is unique clustered like any thing,,,or any column which is always unique in this,,n write query as...

SELECT col1,col2, col3,
(SELECT COUNT(*) FROM test25 e2 WHERE e2.col1 <= e.col1) AS rownumber
FROM test25 e
ORDER BY custid

Here col1 is unique clustered column in ur table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-16 : 05:31:10

quote:
Originally posted by ashishashish

if u using Sql Server 2005 then use row_number() function as above suggested,,,,,,,,,,
else
If u r working in sql 2000 den
jus add a column to ur Table which is unique clustered like any thing,,,or any column which is always unique in this,,n write query as...

SELECT col1,col2, col3,
(SELECT COUNT(*) FROM test25 e2 WHERE e2.col1 <= e.col1) AS rownumber
FROM test25 e
ORDER BY custid

Here col1 is unique clustered column in ur table.

it should be dense_rank not row_number

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-16 : 05:36:35

txs jbp_j ,its working fine.

quote:
Originally posted by jbp_j

[quote]Originally posted by jbp_j

hi,

Try this,

select * from ( select dense_rank() over ( order by invoice_no ) as sno,* from yourtable)
as t

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 05:39:42

no need of using derived table
u can directly use
select dense_rank() over ( order by invoice_no ) as sno,* from yourtable

jbp_j
Starting Member

24 Posts

Posted - 2009-02-16 : 05:42:17

Welcome

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-16 : 07:06:03

Mean if the data is in Sql Server 2000 den wat will u do...
i think Rank(),Dense_Rank(),Row_Number().....
are in use for Sql 2005 or later......

quote:
Originally posted by bklr

no need of using derived table
u can directly use
select dense_rank() over ( order by invoice_no ) as sno,* from yourtable

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 07:15:37

hi ashish,
i had given that query for this query
select * from ( select dense_rank() over ( order by invoice_no ) as sno,* from yourtable)
as t
no need of derived table
just simply use
select dense_rank() over ( order by invoice_no ) as sno,* from yourtable

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-16 : 07:53:43

Sorry Sir...
Its My fault.....
Very Sorry Again

quote:
Originally posted by bklr

hi ashish,
i had given that query for this query
select * from ( select dense_rank() over ( order by invoice_no ) as sno,* from yourtable)
as t
no need of derived table
just simply use
select dense_rank() over ( order by invoice_no ) as sno,* from yourtable

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 08:00:36

quote:
Originally posted by ashishashish

Sorry Sir...
Its My fault.....
Very Sorry Again

k cool

........ ashish

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 08:17:43

In SQL 2000.

SELECT invoice_no, invoice_date,invoice_amount,
(SELECT COUNT(distinct invoice_no) FROM Table WHERE invoice_no <= t.invoice_no) AS s_no
FROM Table t
order by invoice_no

nasa2rana
Starting Member

3 Posts

Posted - 2009-02-17 : 03:08:44

Hi ..
select * from
(
select row_number() over(order by invoice_no,invoice_date,invoice_amount)from
(
(Select invoice_no,
invoice_date,
invoice_amount
from invoice_dtl
group by invoice_no,invoice_date,invoice_amount
) as v1
) as v2
join invoice_dtl
on
(invoice_dtl.invoice_no = v2.invoice_no) and (invoice_dtl.invoice_date = v2.invoice_date) and (invoice_dtl.invoice_amount = v2.invoice_amount)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 03:36:45

hi nasa2rana,
in this case row_number will not work use dense_rank function as above query given the solution please check them once

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 09:08:50

quote:
Originally posted by nasa2rana

Hi ..
select * from
(
select row_number() over(order by invoice_no,invoice_date,invoice_amount)from
(
(Select invoice_no,
invoice_date,
invoice_amount
from invoice_dtl
group by invoice_no,invoice_date,invoice_amount
) as v1
) as v2
join invoice_dtl
on
(invoice_dtl.invoice_no = v2.invoice_no) and (invoice_dtl.invoice_date = v2.invoice_date) and (invoice_dtl.invoice_amount = v2.invoice_amount)

have you tested this for posted data?

nasa2rana
Starting Member

3 Posts

Posted - 2009-02-18 : 05:44:43

sorry for previous query.
Please check this query on sql 2005 databse

select v2.sl_no,invoice_dtl.* from
(
select row_number() over(order by invoice_no,invoice_date,invoice_amount) as sl_no,* from

(Select invoice_no,
invoice_date,
invoice_amount
from invoice_dtl
group by invoice_no,invoice_date,invoice_amount
) as v1
) as v2
join invoice_dtl
on
(invoice_dtl.invoice_no = v2.invoice_no) and (invoice_dtl.invoice_date = v2.invoice_date) and (invoice_dtl.invoice_amount = v2.invoice_amount)

nasa2rana
Starting Member

3 Posts

Posted - 2009-02-18 : 06:02:36

check this query in sql 2000 database

select v2.sl_no,invoice_dtl.* from
(
select (select count(*) from
(select distinct * from invoice_dtl) as v3
where v3.invoice_no <= v1.invoice_no) as sl_no,* from
(
Select invoice_no,
invoice_date,
invoice_amount
from invoice_dtl
group by invoice_no,invoice_date,invoice_amount
) as v1
) as v2
join invoice_dtl
on
(invoice_dtl.invoice_no = v2.invoice_no) and (invoice_dtl.invoice_date = v2.invoice_date) and (invoice_dtl.invoice_amount = v2.invoice_amount)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-18 : 07:53:09

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 08:45:02

quote:
Originally posted by nasa2rana

sorry for previous query.
Please check this query on sql 2005 databse

select v2.sl_no,invoice_dtl.* from
(
select row_number() over(order by invoice_no,invoice_date,invoice_amount) as sl_no,* from

(Select invoice_no,
invoice_date,
invoice_amount
from invoice_dtl
group by invoice_no,invoice_date,invoice_amount
) as v1
) as v2
join invoice_dtl
on
(invoice_dtl.invoice_no = v2.invoice_no) and (invoice_dtl.invoice_date = v2.invoice_date) and (invoice_dtl.invoice_amount = v2.invoice_amount)

still you wont get same number when you use row_number()

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources