Special Distinct - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Special Distinct

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2008-09-15 : 23:35:38

Hi
I have this table called mydata

-----------------------------------
___xval___|___gcode__|___dord___|___dname__|
_<NULL>___|____A_____|____10____|Mark______|
_<NULL>___|____A_____|____20____|Thomas____|
_<NULL>___|____C_____|____30____|Ken_______|
_<NULL>___|____B_____|____40____|Lili______|
_<NULL>___|____B_____|____50____|Paul______|
_<NULL>___|____B_____|____60____|Sally_____|
_<NULL>___|____B_____|____70____|Richard___|
_<NULL>___|____C_____|____80____|Matt______|
_<NULL>___|____C_____|____90____|Joey______|
_<NULL>___|____C_____|___100____|Samantha__|
_<NULL>___|____A_____|___110____|Ali_______|
_<NULL>___|____A_____|___120____|Nancy_____|
_<NULL>___|____A_____|___130____|David_____|
_<NULL>___|____A_____|___140____|John______|
_<NULL>___|____C_____|___150____|Caroline__|
_<NULL>___|____C_____|___160____|Kate______|
_<NULL>___|____C_____|___170____|Adam______|
_<NULL>___|____C_____|___180____|Ahmed_____|
_<NULL>___|____A_____|___190____|Nora______|
_<NULL>___|____A_____|___200____|William___|
-----------------------------------

as you can see it has gcode values repeated in different places
I want to fill xval column with gcode distinct ordered by dord

so i get this result

-----------------------------------
___xval___|___gcode__|___dord___|___dname__|
___1______|____A_____|____10____|Mark______|
___1______|____A_____|____20____|Thomas____|
___2______|____C_____|____30____|Ken_______|
___3______|____B_____|____40____|Lili______|
___3______|____B_____|____50____|Paul______|
___3______|____B_____|____60____|Sally_____|
___3______|____B_____|____70____|Richard___|
___4______|____C_____|____80____|Matt______|
___4______|____C_____|____90____|Joey______|
___4______|____C_____|___100____|Samantha__|
___5______|____A_____|___110____|Ali_______|
___5______|____A_____|___120____|Nancy_____|
___5______|____A_____|___130____|David_____|
___5______|____A_____|___140____|John______|
___6______|____C_____|___150____|Caroline__|
___6______|____C_____|___160____|Kate______|
___6______|____C_____|___170____|Adam______|
___6______|____C_____|___180____|Ahmed_____|
___7______|____A_____|___190____|Nora______|
___7______|____A_____|___200____|William___|
-----------------------------------

i can do it using Cursor but the requirement is to be done without Cursors

who can help me doing this? and how?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-15 : 23:52:51

yes. make use of the row_number() or rank() or maybe dense_rank() with over() function

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 23:53:03

[code]UPDATE t
SET t.xval=cnt.Count + 1
FROm YourTable t
OUTER APPLY (SELECT TOP 1 dord
FROM YourTable
WHERE dord <t.dord
ORDER BY dord DESC)prev
OUTER APPLY (SELECT COUNT(DISTINCT gcode)AS Count
FROM YourTable
WHERE dord<t.dord
AND (gcode <>t.gcode OR dord<=prev.dord)cnt[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-16 : 00:04:58

here's a sample


Declare  @mytable table(xval int, Gcode varchar(2), dord int)
insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700



Update a
set xval = a.myrank
from
(
select dense_rank() over (order by gcode) as myrank,*
from @mytable 
) a

select * from @Mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-16 : 00:19:01

I just noticed the GCode can not be grouped due to the grouping is in the order combined with the Gcode. Use visak's solution, it's very good. There were a couple very minor syntax errors, but to save you the headache here it is with sample data.


Declare  @mytable table(xval int, Gcode varchar(2), dord int)
insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900


UPDATE t
SET t.xval= cnt.[Count] + 1
FROm @mYTABLE t
OUTER APPLY (SELECT TOP 1 dord
             FROM @Mytable
             WHERE dord <t.dord
             ORDER BY dord DESC)prev
OUTER APPLY (SELECT COUNT(DISTINCT gcode)AS Count
             FROM @Mytable
             WHERE dord<t.dord
             AND (gcode <>t.gcode OR dord<=prev.dord)) cnt

select * from @Mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 00:31:52

quote:

Originally posted by Vinnie881

I just noticed the GCode can not be grouped do to the grouping is in the order. Use visak's solution. There were a couple very minor syntax errors, but to save you the headache here it is corrected


Declare  @mytable table(xval int, Gcode varchar(2), dord int)
insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900


UPDATE t
SET t.xval= cnt.[Count] + 1
FROm @mYTABLE t
OUTER APPLY (SELECT TOP 1 dord
             FROM @Mytable
             WHERE dord <t.dord
             ORDER BY dord DESC)prev
OUTER APPLY (SELECT COUNT(DISTINCT gcode)AS Count
             FROM @Mytable
             WHERE dord<t.dord
             AND (gcode <>t.gcode OR dord<=prev.dord)) cnt

select * from @Mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

i just tested it again and seems like it needs some retweaking

UPDATE t
SET t.xval= cnt.[Count] + 1
FROm @mYTABLE t
OUTER APPLY (SELECT TOP 1 dord
             FROM @Mytable
             WHERE dord <t.dord
             AND gcode <> t.gcode
             ORDER BY dord DESC)prev
OUTER APPLY (SELECT COUNT(DISTINCT gcode)AS Count
             FROM @Mytable
             WHERE dord<t.dord
             AND (gcode <>t.gcode OR dord<=prev.dord)) cnt

output
-----------------------------
xval        Gcode dord
----------- ----- -----------
1           a     100
1           a     200
1           a     300
2           b     400
2           b     500
3           c     600
4           d     700
5           a     800
5           a     900

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 02:57:00

[code]Declare @mytable table(xval int, Gcode varchar(2), dord int)
insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900 Union all
select 'd',1100 Union all
select 'a',1300 Union all
select 'a',1440 Union all
select 'a',1530 union all
select 'b',1540 union all
select 'b',1660 union all
select 'c',1875 union all
select 'd',1998 Union all
select 'a',2200 Union all
select 'a',2265 Union all
select 'd',2315 Union all
select 'a',2366 Union all
select 'a',3440

UPDATE r
SET r.xval= res.cnt
FROm @mYTABLE r
cross apply
(SELECT COUNT(Gcode)AS cnt FROM
(SELECT t.Gcode,prev.dord,min(t.dord) AS mindord
FROM @mYTABLE t
OUTER APPLY (SELECT TOP 1 dord
FROM @Mytable
WHERE dord <t.dord
AND gcode <> t.gcode
ORDER BY dord DESC)prev
GROUP BY t.Gcode,prev.dord)tmp
WHERE tmp.mindord <=r.dord)res

select * from @Mytable
xval Gcode dord
----------- ----- -----------
1 a 100
1 a 200
1 a 300
2 b 400
2 b 500
3 c 600
4 d 700
5 a 800
5 a 900
6 d 1100
7 a 1300
7 a 1440
7 a 1530
8 b 1540
8 b 1660
9 c 1875
10 d 1998
11 a 2200
11 a 2265
12 d 2315
13 a 2366
13 a 3440
[/code]

nice123ej
Starting Member

48 Posts

Posted - 2008-09-16 : 05:12:43

thx visakh16 u r genius

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 05:17:47

quote:
Originally posted by nice123ej

thx visakh16 u r genius

you're welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 05:36:14

Also see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=110706

Declare  @mytable table(xval int, Gcode varchar(2), dord int, primary key (dord))

insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900 Union all
select 'd',1100 Union all
select 'a',1300 Union all
select 'a',1440 Union all
select 'a',1530 union all
select 'b',1540 union all
select 'b',1660 union all
select 'c',1875 union all
select 'd',1998 Union all
select 'a',2200 Union all
select 'a',2265 Union all
select 'd',2315 Union all
select 'a',2366 Union all
select 'a',3440 


declare @gcode varchar(2), @xval int

select top 1	@xval = 1,
		@gcode = gcode
from		@mytable
order by	dord

update	@mytable
set	@xval = xval = case when gcode <> @gcode then @xval + 1 else @xval end,
	@gcode = gcode

select	*
from	@mytable

E 12°55'05.63"
N 56°04'39.26"

nice123ej
Starting Member

48 Posts

Posted - 2008-09-16 : 06:12:50

i liked the last solution,
very smart

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-16 : 06:35:49

It works as long as there is a clustered index over dord.

E 12°55'05.63"
N 56°04'39.26"

nice123ej
Starting Member

48 Posts

Posted - 2008-09-16 : 19:52:50

what if there is no index over dord? then how to resolve it?

nice123ej
Starting Member

48 Posts

Posted - 2008-09-16 : 20:24:11

will this work?

update @mytable
set @xval = xval = case when tbl.gcode <> @gcode then @xval + 1 else @xval end,
@gcode = tbl.gcode
from @mytable tbl
order by tbl.dord

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-16 : 20:46:37

You can't have a order by on a update statement, that is why there is a clustered index added to the table to try to keep the order.

Your query would work assuming that you set the variables <> to null prior. Also regarding this method; in the articles I read it suggest throwing in a anchor variable in to ensure the index is used. Since peso did not include one, it is likley it is not needed, but I just figured I'd relay my understanding (Also Note articles with this method normally state that newer versions of sql might not work in the same manor, so this type of query is not always a perfect solution)


Declare  @mytable table(xval int, Gcode varchar(2), dord int, primary key (dord))

insert into @mytable(gcode,dord)
select 'a',100 union all
select 'a',200 union all
select 'a',300 union all
select 'b',400 union all
select 'b',500 union all
select 'c',600 union all
select 'd',700 Union all
select 'a',800 Union all
select 'a',900 Union all
select 'd',1100 Union all
select 'a',1300 Union all
select 'a',1440 Union all
select 'a',1530 union all
select 'b',1540 union all
select 'b',1660 union all
select 'c',1875 union all
select 'd',1998 Union all
select 'a',2200 Union all
select 'a',2265 Union all
select 'd',2315 Union all
select 'a',2366 Union all
select 'a',3440 

declare @xval int,@Gcode varchar(2),@dord int
set @xval = 0
set @gCODE = ''

update tblac
set @xval = xval = case when tbl.gcode <> @gcode then @xval + 1 else @xval end
,@gcode = tbl.gcode
,@dord = tbl.dord
from @mytable tbl

select * from @mytable

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 01:42:52

If you just a permanent table in your real situation, kust query hint the update statement with tablock and index use.
That approach is documented in Books Online.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources