Select * FROM table where 2nd column is unique

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)

Select * FROM table where 2nd column is unique

Author

Topic

lovray
Starting Member

9 Posts

Posted - 2009-01-12 : 19:57:27

I have a Table:

myKey ProdNumber UPCnumber
1 333 UPC_11
2 333 UPC_44
3 444 UPC_22

One product can have multiple UPC's, I'm generating a product feed but I only need one UPC per productNumber, preferable the first UPC. For example I need a select statement which will give me this:

1 333 UPC_11
3 444 UPC_22

I can't figure this out for the life of me: I've tried:

SELECT myKey, DISTINCT partNumber, UPCnumber FROM myTable;

and It still won't work, no matter the order i place the column names. Please help. Thanks Much. -George.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-12 : 20:04:01

You can use the ROW_NUMBER() function to achieve this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-13 : 00:14:48

[code]
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY ProdNumber ORDER BY UPCnumber) AS Seq,*
FROM Table
)t
WHERE Seq=1
[/code]

lovray
Starting Member

9 Posts

Posted - 2009-01-15 : 17:49:50

Nice!! I wouldn't have been able to do it with out that code example! Thanks Much - George.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-15 : 22:53:22

declare @temp table (myKey int, ProdNumber int,UPCnumber varchar(32))
insert into @temp
select 1, 333, 'UPC_11' union all
select 2, 333, 'UPC_44' union all
select 3, 444, 'UPC_22'

select * from
( select *,rank() over ( partition by prodnumber order by mykey ) as rn from @temp ) as p
where rn = 1

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 00:06:58

Hi Dear,

I hope the below query will help you to fix the issue.

Query:
SELECT MIN(MYKEY)
,PRODNUMBER
,MIN(UPCNUMBER)
FROM DUMMY
GROUP BY PRODNUMBER
Change the Table Name and try.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-16 : 00:08:40

quote:
Originally posted by thiyagu_rind

Hi Dear,

I hope the below query will help you to fix the issue.

Query:
SELECT MIN(MYKEY)
,PRODNUMBER
,MIN(UPCNUMBER)
FROM DUMMY
GROUP BY PRODNUMBER
Change the Table Name and try.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

Do not use this solution. It is not guaranteed to be accurate. You could and probably will be pulling the MIN values from different rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 00:10:46

quote:
Originally posted by Nageswar9

declare @temp table (myKey int, ProdNumber int,UPCnumber varchar(32))
insert into @temp
select 1, 333, 'UPC_11' union all
select 2, 333, 'UPC_44' union all
select 3, 444, 'UPC_22'

select * from
( select *,rank() over ( partition by prodnumber order by mykey ) as rn from @temp ) as p
where rn = 1

will return all instances of first UPC in case value repeats.

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 00:14:06

Hi Man,

Try the below query

Query :
SELECT MYKEY,PRODNUMBER,UPCNUMBER
FROM DUMMY WHERE MYKEY IN (
SELECT MIN(MYKEY) FROM DUMMY
GROUP BY PRODNUMBER )

I hope Now Tara Kizer will not raise a red flag.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-16 : 01:12:24

quote:
Originally posted by thiyagu_rind

Hi Man,

Try the below query

Query :
SELECT MYKEY,PRODNUMBER,UPCNUMBER
FROM DUMMY WHERE MYKEY IN (
SELECT MIN(MYKEY) FROM DUMMY
GROUP BY PRODNUMBER )

I hope Now Tara Kizer will not raise a red flag.

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

No, just use row_number() function. The above solution is wrong and inefficient.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 01:51:15

Dear Tara keizer,

The solution may be inefficient, but it is not a wrong solution. Can you pls explain, how it is wrong?

Regards
Thiyagarajan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-16 : 02:09:10

Because your solution could match the wrong rows together.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2009-01-16 : 02:13:33

The Above soln posted using Min() is not wrong as far iam concerned.

But Row_number(),Rank() are preferable the best options and using min can be opted as least option. So, Min() is also an answer but less efficient. And he would have replied as just other alternative options for above post

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-16 : 02:14:45

Query:
SELECT MIN(MYKEY)
,PRODNUMBER
,MIN(UPCNUMBER)
FROM DUMMY
GROUP BY PRODNUMBER
Change the Table Name and try.

Above solution looks correct to me. I dont think it will match wrong rows

Rahul Shinde

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 02:16:47

Dear Tara Keizer,

I would like to know when it will return wrong rows. please use the statements and tell me when it returns wrong results.

Queries

create table dummy(myKey int, ProdNumber int,UPCnumber varchar(32))
insert into dummy
select 1, 333, 'UPC_11' union all
select 2, 333, 'UPC_44' union all
select 3, 444, 'UPC_22' union all
select 4, 555, 'UPC_20' union all
select 5, 999, 'UPC_25' union all
select 6, 444, 'UPC_21' union all
select 7, 555, 'UPC_18' union all
select 8, 666, 'UPC_19'

Code Snippet:

select * from dummy order by prodnumber,mykey
GO
SELECT MYKEY,PRODNUMBER,UPCNUMBER
FROM DUMMY WHERE MYKEY IN (
SELECT MIN(MYKEY) FROM DUMMY
GROUP BY PRODNUMBER )

Check the results and get back to me.

Regards
Thiyagarajan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-16 : 02:47:39

Yes it's correct.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-16 : 03:28:29

Dear Tara Kizer,

Thank you.... Just to know where am wrong... Nothing

Regards
Thiyagarajan
www.sqlhunt.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 09:20:16

quote:
Originally posted by ra.shinde

Query:
SELECT MIN(MYKEY)
,PRODNUMBER
,MIN(UPCNUMBER)
FROM DUMMY
GROUP BY PRODNUMBER
Change the Table Name and try.

Above solution looks correct to me. I dont think it will match wrong rows

Rahul Shinde

its wrong because you cant guarantee MIN(MYKEY) & MIN(UPCNUMBER) occurs in same record. so it wont give OP's reqd output unless UPCnumber & myKey values go sequentially (either ascending or descending) for a value of ProdNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources