SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to keep unique item?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

247 Posts

Posted - 11/20/2012 :  09:26:38  Show Profile  Reply with Quote
Hi there,

See the following case: table1 has columns of Code, Model ... now the data as:

Code | Model | ...
ABC | N1
BBC | N2
XYX | N1
TTY | N3
HJY | N2
TTH | N4
....


Now I need select code with only each unique model N1, N2 ... once. For ABC and XYX both have N1 model, but only one is required. How can I do that? Thanks in advance.

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/20/2012 :  09:58:31  Show Profile  Reply with Quote
This will give unique Models with arbitrary code

SELECT t1.Code,t1.Model
FROM
(
SELECT Code,Model, [SingleValue] = ROW_NUMBER() OVER(PARTITION BY model ORDER BY code)
FROM yourTable
) t1

WHERE t1.SingleValue = 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

allan8964
Posting Yak Master

247 Posts

Posted - 11/20/2012 :  10:44:32  Show Profile  Reply with Quote
Beautiful code! It works perfect. Now I know a function can be returned to a variable and later the variable is used in WHERE clause. It works perfect, thank you so much Jim. Have a wonderful Thanksgiving!!!
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 11/20/2012 :  11:08:39  Show Profile  Reply with Quote
You're very welcome. Happy Thanksgiving to you, too.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.42 seconds. Powered By: Snitz Forums 2000