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 2005 Forums
 Transact-SQL (2005)
 Grouping

Author  Topic 

dnf999
Constraint Violating Yak Guru

253 Posts
Posted - 2010-03-02 : 11:58:15
Hi I have a table with data which I need to assign a unique ID based on date, currency and ledger.

i.e.

Date, Currency, Ledger, Amount Required_ID
1/1/00 USD 5 123.14 1
1/1/00 USD 5 100.00 1
1/1/00 USD 5 100.03 1
1/1/02 USD 5 100.03 2
1/1/02 USD 5 100.03 2
1/1/03 USD 5 100.03 3


Now I require the Required_ID field to populate as in the example above. I have tried to use:

ROW_NUMBER() OVER(PARTITION BY DATE, CURRENCY, LEDGER ORDER BY date, currency, ledger) AS Required_ID

but that gives a unique row number per grouping row.

Any help much appreciated...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-02 : 12:03:58
DENSE_RANK() OVER(PARTITION BY CURRENCY, LEDGER ORDER BY date) AS Required_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-02 : 12:09:23
select *,DENSE_RANK()OVER(ORDER BY date) as Amount Required_ID from yourtable

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-02 : 12:21:28
quote:
Originally posted by Idera

select *,DENSE_RANK()OVER(ORDER BY date) as Amount Required_ID from yourtable

PBUH


see the difference



SELECT *,
DENSE_RANK() OVER(PARTITION BY curr, Led ORDER BY dt) AS Required_ID_Visakh,
DENSE_RANK()OVER(ORDER BY dt) as Required_ID_Idera
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03' , 'GBP' , 6 , 145.28  UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t
ORDER BY curr desc,dt

output
----------------------------
dt	curr	Led	Amt	Required_ID_Visakh	Required_ID_Idera
1/1/00	USD	5	123.14	1	     1
1/1/00	USD	5	100.03	1	     1
1/1/00	USD	5	100.00	1	     1
1/1/02	USD	5	100.03	2	     2
1/1/02	USD	5	100.03	2	     2
1/1/03	USD	5	100.03	3	     3
1/1/02	GBP	6	75.00	1	     2
1/1/02	GBP	6	243.03	1	     2
1/1/03	GBP	6	145.28	2	     3




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-02 : 12:25:54
quote:
Originally posted by visakh16

quote:
Originally posted by Idera

select *,DENSE_RANK()OVER(ORDER BY date) as Amount Required_ID from yourtable

PBUH


see the difference



SELECT *,
DENSE_RANK() OVER(PARTITION BY curr, Led ORDER BY dt) AS Required_ID_Visakh,
DENSE_RANK()OVER(ORDER BY dt) as Required_ID_Idera
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03' , 'GBP' , 6 , 145.28  UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t
ORDER BY curr desc,dt

output
----------------------------
dt	curr	Led	Amt	Required_ID_Visakh	Required_ID_Idera
1/1/00	USD	5	123.14	1	     1
1/1/00	USD	5	100.03	1	     1
1/1/00	USD	5	100.00	1	     1
1/1/02	USD	5	100.03	2	     2
1/1/02	USD	5	100.03	2	     2
1/1/03	USD	5	100.03	3	     3
1/1/02	GBP	6	75.00	1	     2
1/1/02	GBP	6	243.03	1	     2
1/1/03	GBP	6	145.28	2	     3




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




Oh I missed the proper requirement of OP.

SELECT *,
DENSE_RANK() OVER(PARTITION BY curr, Led ORDER BY dt) AS Required_ID_Visakh,
DENSE_RANK()OVER(ORDER BY dt,curr,led) as Required_ID_Idera
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03' , 'GBP' , 6 , 145.28  UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t

dt     curr Led         Amt                                     Required_ID_Visakh   Required_ID_Idera
------ ---- ----------- --------------------------------------- -------------------- --------------------
1/1/00 USD  5           123.14                                  1                    1
1/1/00 USD  5           100.03                                  1                    1
1/1/00 USD  5           100.00                                  1                    1
1/1/02 GBP  6           75.00                                   1                    2
1/1/02 GBP  6           243.03                                  1                    2
1/1/02 USD  5           100.03                                  2                    3
1/1/02 USD  5           100.03                                  2                    3
1/1/03 GBP  6           145.28                                  2                    4
1/1/03 USD  5           100.03                                  3                    5



PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-02 : 12:30:06
still we're not sure what OP's looking at. it may be either your last suggestion or mine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-02 : 12:34:18
At first glance I to thought that your solution is right but then I thought maybe the Op wants it otherwise.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-02 : 12:37:22
lets wait until he comes back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-02 : 12:38:17
quote:
Originally posted by visakh16

lets wait until he comes back

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Yup

PBUH
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-02 : 14:59:34
[code]SELECT *,
DENSE_RANK() OVER(ORDER BY dt, curr, led) AS Required_ID_Ms65g
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'GBP', 6, 145.28 UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t
ORDER BY dt, curr, led
/*
dt curr Led Amt Required_ID_Ms65g
------ ---- ----------- --------------------------------------- --------------------
1/1/00 USD 5 100.00 1
1/1/00 USD 5 100.03 1
1/1/00 USD 5 123.14 1
1/1/02 GBP 6 75.00 2
1/1/02 GBP 6 243.03 2
1/1/02 USD 5 100.03 3
1/1/02 USD 5 100.03 3
1/1/03 GBP 6 145.28 4
1/1/03 USD 5 100.03 5
(9 row(s) affected)
*/
[/code]
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-02 : 23:32:33
So how is your o/p different than mine?

PBUH
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-03 : 01:23:52
quote:
Originally posted by Idera

So how is your o/p different than mine?

PBUH


Oh I did not see your modified query! sorry.

EDIT: write missed 'see'
Go to Top of Page

dnf999
Constraint Violating Yak Guru

253 Posts
Posted - 2010-03-03 : 01:51:16
Hi all,

Thanks for all your replies. This bit of code worked a treat:

DENSE_RANK() OVER(ORDER BY dt, curr, led) AS Required_ID_Ms65g

Thanks!
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-03 : 01:57:46
quote:
Originally posted by ms65g

SELECT *,
DENSE_RANK() OVER(ORDER BY dt, curr, led) AS Required_ID_Ms65g
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'GBP', 6, 145.28 UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t
ORDER BY dt, curr, led
/*
dt     curr Led         Amt                                     Required_ID_Ms65g
------ ---- ----------- --------------------------------------- --------------------
1/1/00 USD  5           100.00                                  1
1/1/00 USD  5           100.03                                  1
1/1/00 USD  5           123.14                                  1
1/1/02 GBP  6           75.00                                   2
1/1/02 GBP  6           243.03                                  2
1/1/02 USD  5           100.03                                  3
1/1/02 USD  5           100.03                                  3
1/1/03 GBP  6           145.28                                  4
1/1/03 USD  5           100.03                                  5
(9 row(s) affected)
*/



Highlighted in blue color(ORDER BY dt, curr, led) does this necessary to have this at the end,cos order by items menetined in the select colmn level itself..

dont mistake me,iam beginner.. :)
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-03 : 02:02:39
On basis of the initial requirement the OP wanted to assign unique id's based on date, currency and ledger.So the order by clause you marked in blue becomes irrelevant in this case.

PBUH
Go to Top of Page

haroon2k9
Constraint Violating Yak Guru

328 Posts
Posted - 2010-03-03 : 02:05:13
quote:
Originally posted by Idera

On basis of the initial requirement the OP wanted to assign unique id's based on date, currency and ledger.So the order by clause you marked in blue becomes irrelevant in this case.

PBUH


Yeah..Thank you very much for your immediate Response.
Go to Top of Page

Sachin.Nand

2937 Posts
Posted - 2010-03-03 : 02:06:27
quote:
Originally posted by haroon2k9

quote:
Originally posted by Idera

On basis of the initial requirement the OP wanted to assign unique id's based on date, currency and ledger.So the order by clause you marked in blue becomes irrelevant in this case.

PBUH


Yeah..Thank you very much for your immediate Response.


You are welcome.

PBUH
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-03 : 09:34:29
quote:
Originally posted by ms65g

SELECT *,
DENSE_RANK() OVER(ORDER BY dt, curr, led) AS Required_ID_Ms65g
FROM
(
select '1/1/00' as dt, 'USD' AS curr, 5 AS Led, 123.14 AS Amt UNION ALL
select '1/1/00', 'USD', 5, 100.00 UNION ALL
select '1/1/00', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/02', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'USD', 5, 100.03 UNION ALL
select '1/1/03', 'GBP', 6, 145.28 UNION ALL
select '1/1/02', 'GBP', 6, 75.00 UNION ALL
select '1/1/02', 'GBP', 6, 243.03
)t
ORDER BY dt, curr, led
/*
dt     curr Led         Amt                                     Required_ID_Ms65g
------ ---- ----------- --------------------------------------- --------------------
1/1/00 USD  5           100.00                                  1
1/1/00 USD  5           100.03                                  1
1/1/00 USD  5           123.14                                  1
1/1/02 GBP  6           75.00                                   2
1/1/02 GBP  6           243.03                                  2
1/1/02 USD  5           100.03                                  3
1/1/02 USD  5           100.03                                  3
1/1/03 GBP  6           145.28                                  4
1/1/03 USD  5           100.03                                  5
(9 row(s) affected)
*/



Is DENSE_RANK a standard function or have you moved away from your earlier standards?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139575

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts
Posted - 2010-03-03 : 11:01:18
quote:
Originally posted by visakh16
Is DENSE_RANK a standard function or have you moved away from your earlier standards?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139575

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




JOE CELKO:

"With additional of OLAP functions in SQL-99, life becomes very easy"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2010-03-03 : 11:04:09
quote:
Originally posted by ms65g

quote:
Originally posted by visakh16
Is DENSE_RANK a standard function or have you moved away from your earlier standards?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139575

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




JOE CELKO:

"With additional of OLAP functions in SQL-99, life becomes very easy"

Exactly

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site