get distinct row - 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)

get distinct row

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-04 : 09:19:38

Hi,

Based on an answer to a question i posted here i create the following query:

if object_id('tempdb..#TempWinShuttle') is not null

    drop table #TempWinShuttle  

create table #TempWinShuttle (row  int ,itemnumber  int ,id nchar (10),order_id nvarchar (100)
,material nvarchar (50), billto_id nvarchar (100),shipto_id nvarchar (50)
,shipped_quantity int
,materialhidden nvarchar (50),Orderhidden nvarchar (50))

if @ALL=1 BEGIN
insert into #TempWinShuttle 
select DISTINCT 1 row
,0  AS itemnumber 
,'H' AS ID
,packlist.order_id as order_id
,'' as Material
,shipto.billto_id as billto_id
,shipto.shipto_id shipto_id 
,'' AS shipped_quantity
,''  as materialhidden
,packlist.order_id as Orderhidden
from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)


insert into #TempWinShuttle 
select DISTINCT 2 row
,ROW_NUMBER() OVER (ORDER BY packlist.order_id) *10 AS itemnumber 
,'D'
,packlist.order_id as order_id
,material   AS Material
,'' as billto_id
,'' shipto_id 
,'' AS shipped_quantity
,material  as materialhidden
,packlist.order_id as Orderhidden

from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)



insert into #TempWinShuttle 
select DISTINCT 3 row
,ROW_NUMBER() OVER (ORDER BY packlist.order_id) *10 AS itemnumber 
,'D1'
,packlist.order_id as order_id
,''  AS Material
,'' as billto_id
,'' shipto_id 
,cartonitem_quantity AS shipped_quantity
,material  as materialhidden
,packlist.order_id as Orderhidden

from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)
 END
 

select top 100 row as oldrow,id,order_id
,material, billto_id 
 ,shipto_id,shipped_quantity ,materialhidden,Orderhidden
,case when id='H' then 0 else newrow end as itemnumber 
from
(
select *,row_number()over (partition by  case when id='D' then order_id when id='H'  then 0 end,order_id
 order by id,Orderhidden)*10 as newrow from #TempWinShuttle

)t 
 
 order by order_id,row

The result is in the image. How can i remove the duplicate rows?
For example the row D with order id 6976 is repeated 4 times. It should show once and the itemnumber must be 10.
If order id 6976 had a different material than the itemnumbers would be 10,20.

How can i change the sql?
Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 09:22:50

your code looks like a real mess. can i ask meaning of DISTINCT 2 row,DISTINCT 3 row etc?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-04 : 09:34:34

So that i can order the rows so the H will always be the first followed by D then D1

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 10:50:30

quote:
Originally posted by collie

So that i can order the rows so the H will always be the first followed by D then D1

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

what has DISTINCT to do with order?

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-04 : 11:00:48

just to make sure no duplicate rows are returned.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-04 : 11:02:17

visakh16 this is basically what i need but i get duplicated rows
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139316[/url]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Sachin.Nand

2937 Posts

Posted - 2010-02-04 : 12:12:58

quote:
Originally posted by collie

visakh16 this is basically what i need but i get duplicated rows
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139316[/url]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Then I guess ur initial requirement totally changes.

declare @tbl as table(row int,id varchar(10),orderid int)
insert into @tbl
select 1,'H',1005 union all
select 2,'D',1005 union all
select 3,'D1',1005 union all
select 1,'H',1007 union all
select 2,'D',1007 union all
select 2,'D',1007 union all
select 2,'D',1007 union all
select 1,'H',1015 union all
select 2,'D',1015 union all
select 3,'D1',1015

select row as oldrow,id,orderid,case when id='H' then 0 else newrow *10 end as newrow from
(
select *,row_number()over (partition by case when id='D' then orderid when id='H' then 0 end,orderid order by id,orderid) as newrow from @tbl

)t where newrow=1 order by orderid,row

PBUH

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-02-06 : 15:23:52

I have the query below and get the reults in the attachment.
However, i need the result to be
ID NEWROW
H 0
D 10
D 20
D1 10
D1 20
H 0
D 10
D 20
D1 10
D1 20

How can i achieve this?
Thanks


if object_id('tempdb..#TempWinShuttle') is not null

    drop table #TempWinShuttle  

create table #TempWinShuttle (row  int ,itemnumber  int ,id nchar (10),orderid nvarchar (100)
,material nvarchar (50), billto_id nvarchar (100),shipto_id nvarchar (50)
,shipped_quantity int
,materialhidden nvarchar (50),Orderhidden nvarchar (50))

 
insert into #TempWinShuttle 
select DISTINCT 1 row
,0  AS itemnumber 
,'H' AS ID
,packlist.order_id as order_id
,'' as Material
,shipto.billto_id as billto_id
,shipto.shipto_id shipto_id 
,'' AS shipped_quantity
,''  as materialhidden
,packlist.order_id as Orderhidden
from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)
and packlist.order_id in ('1646','1013')

insert into #TempWinShuttle 
select DISTINCT 2 row
,ROW_NUMBER() OVER (ORDER BY packlist.order_id) *10 AS itemnumber 
,'D'
,packlist.order_id as order_id
,material   AS Material
,'' as billto_id
,'' shipto_id 
,'' AS shipped_quantity
,material  as materialhidden
,packlist.order_id as Orderhidden
from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)
and packlist.order_id in ('1646','1013')


insert into #TempWinShuttle 
select DISTINCT 3 row
,ROW_NUMBER() OVER (ORDER BY packlist.order_id) *10 AS itemnumber 
,'D1'
,packlist.order_id as order_id
,''  AS Material
,'' as billto_id
,'' shipto_id 
,cartonitem_quantity AS shipped_quantity
,material  as materialhidden
,packlist.order_id as Orderhidden
from packlist
inner join shipto on shipto.shipto_id=packlist.shipto_id
inner join carton c on c.packlist_id=packlist.packlist_id
inner join (select sum(cartonitem_quantity) cartonitem_quantity,cartonitem_style_code+'-'+cartonitem_color_code 
as material,packlist.order_id
from cartonitem 
inner join carton on carton.carton_id=cartonitem.carton_id
inner join packlist on packlist.packlist_id=carton.packlist_id
and packlist.order_id is not null
group by cartonitem_style_code+'-'+cartonitem_color_code 
,packlist.order_id) cartonitem on cartonitem.order_id=packlist.order_id
where packlist_status=1
 AND packlist.PACKLIST_ID NOT IN (select packlist_id from dbo.WinShuttleOTSReport)
and packlist.order_id in ('1646','1013')


select distinct row as oldrow,id,orderid+ '- OTS' AS orderid,shipto_id,billto_id,material,materialhidden,
shipped_quantity,
case when id='H' then 0 else newrow *10 end as newrow from
(
select *,row_number()over (partition by case when id='D' then orderid 
when id='H' then 0 end,orderid,itemnumber
 order by id,orderid) 
as newrow from #TempWinShuttle
 
)t where newrow=1 
order by orderid,row

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources