Query Problem using Select Distinct

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)

Query Problem using Select Distinct

Author

Topic

sqleucac
Starting Member

2 Posts

Posted - 2008-12-15 : 22:28:56

Hi guys,

Good day!

I have a problem with this query:

select Distinct ProdID,ProdName,SeqNo
from tblProduct where CompanyCode='05'
order by SeqNo asc

Result:
ProdID ProdName SeqNo
ABC ABC 1
ABC ABC 2
ABC ABC 3
XYZ XYZ 4
XYZ XYZ 5
MMM MMM 6
MMM MMM 7

All i wanted is get only distinct ProdID,ProdName
with any Minimum SeqNo I want to get a result like below since they
the same prodid but different seqno only.

ProdID ProdName SeqNo
ABC ABC 1
XYZ XYZ 4
MMM MMM 7

Please anybody have suggestions.
Please help...Thanks in advance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 23:18:34

select prodid,prodname,SeqNo from
(select row_number() over(partition by Prodid,Prodname order by SeqNo) as seq,* from @t
)t
where t.seq =1
order by SeqNo

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-15 : 23:28:54

another solution

declare @table table ( ProdID varchar(30) , ProdName varchar(30), SeqNo int)
insert into @table
select 'ABC', 'ABC', 1 union all
select 'ABC', 'ABC', 2 union all
select 'ABC', 'ABC', 3 union all
select 'XYZ', 'XYZ', 4 union all
select 'XYZ', 'XYZ', 5 union all
select 'MMM', 'MMM', 6 union all
select 'MMM', 'MMM', 7

select prodid,prodname,seqno from
( select prodid,prodname,min(seqno) as seqno
from @table
group by prodid,prodname
) t
order by t.seqno

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-15 : 23:43:00

select prodid,prodname,min(seqno) from @table group by prodid,prodname order by min(seqno)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 00:17:30

select prodid,prodname,seqno from
( select *,rank() over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1 order by seqno

I Struggle For Excellence

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-16 : 00:20:13

then this is also another answer

select prodid,prodname,seqno from
( select *,dense_rank() over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1 order by seqno

sqleucac
Starting Member

2 Posts

Posted - 2008-12-16 : 00:24:30

Hi guys,

Thanks for your help...I got it working.
You're all great.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-16 : 00:26:09

quote:
Originally posted by sqleucac

Hi guys,

Thanks for your help...I got it working.
You're all great.

Welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 00:26:47

Welcome

I Struggle For Excellence

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 00:33:03

Try this also,

select prodid,prodname,seqno from
( select *,ntile(4) over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1
order by seqno

I Struggle For Excellence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 00:59:08

quote:
Originally posted by Nageswar9

Try this also,

select prodid,prodname,seqno from
( select *,ntile(4) over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1
order by seqno

I Struggle For Excellence

i dont think ntile will give you correct result. did you test this before posting?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 01:05:13

yes, It is worked and it gives the correct result and whats ur doubt ?

I Struggle For Excellence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 01:09:31

quote:
Originally posted by Nageswar9

yes, It is worked and it gives the correct result and whats ur doubt ?

I Struggle For Excellence

try for this dataset and see


declare @table table ( ProdID varchar(30) , ProdName varchar(30), SeqNo int)
insert into @table
select 'ABC', 'ABC', 1 union all 
select 'ABC', 'ABC', 2 union all 
select 'ABC', 'ABC', 3 union all 
select 'ABC', 'ABC', 4 union all 
select 'ABC', 'ABC', 5 union all 
select 'ABC', 'ABC', 6 union all 
select 'ABC', 'ABC', 7 union all 
select 'ABC', 'ABC', 8 union all 
select 'ABC', 'ABC', 9 union all 
select 'ABC', 'ABC', 10 union all 
select 'ABC', 'ABC', 11 union all 
select 'XYZ', 'XYZ', 12 union all 
select 'XYZ', 'XYZ', 13 union all 
select 'MMM', 'MMM', 14 union all 
select 'MMM', 'MMM', 15 union all
select 'ABC', 'ABC', 16 union all 
select 'ABC', 'ABC', 17 union all 
select 'XYZ', 'XYZ', 18 union all 
select 'XYZ', 'XYZ', 19 union all
select 'XYZ', 'XYZ', 20 union all 
select 'XYZ', 'XYZ', 21 union all
select 'XYZ', 'XYZ', 22 union all 
select 'XYZ', 'XYZ', 23 union all
select 'XYZ', 'XYZ', 24 union all 
select 'XYZ', 'XYZ', 25 union all
select 'MMM', 'MMM', 26 union all 
select 'MMM', 'MMM', 27 union all
select 'MMM', 'MMM', 28 union all 
select 'MMM', 'MMM', 29 union all
select 'MMM', 'MMM', 30 union all 
select 'MMM', 'MMM', 31 union all
select 'MMM', 'MMM', 32 union all 
select 'MMM', 'MMM', 33

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 01:24:57

select prodid,prodname,seqno from
( select *,ntile(14) over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1
order by seqno

I Struggle For Excellence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 01:28:20

quote:
Originally posted by Nageswar9

select prodid,prodname,seqno from
( select *,ntile(14) over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1
order by seqno

I Struggle For Excellence

how will you decide how many groups you should divide it at run time .you cannot guarantee how many records of each group will come in data. so NTILE is definitely not a solution here. just use ROW_NUMBER() as suggested by Sodeep

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-16 : 01:30:35

quote:
Originally posted by visakh16

quote:
Originally posted by Nageswar9

select prodid,prodname,seqno from
( select *,ntile(14) over(partition by prodname order by seqno) as 'rn' from @table ) as t
where t.rn = 1
order by seqno

I Struggle For Excellence

how will you decide how many groups you should divide it at run time .you cannot guarantee how many records of each group will come in data. so NTILE is definitely not a solution here. just use ROW_NUMBER() as suggested by Sodeep

Ok. Thank You

I Struggle For Excellence

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 01:32:39

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources