Remove duplicate in sql select.

Question

5.00/5 (1 vote)

See more:

Have a function

SQL

SELECT DISTINCT keyid, field1, field2
FROM fnFunction('PARAMETER')

returns

SQL

1           DATA1         DATA2
1           DATA2         DATA3
2           DATA1         DATA3

what i want is remove the duplicate
remove this

SQL

1           DATA1         DATA2

or this, either the two

SQL

1           DATA2         DATA3

as long as the keyid has no duplicate

removing the 2 fields will solve the problem but query must return those 2 value in my application(vb.net)

Posted 10-Apr-14 0:59am

hansoctantan

Updated 10-Apr-14 1:01am

v2

Add a Solution

2 solutions

Solution 1

I'm not sure about the keyid field, but for the others you can use a UNION

Select field1 from fn('PARAMETER')
 UNION
Select field2 from fn('PARAMETER')

If you add the keyid field then you'll get (in general) more records, You may wish to dump this into a temp table and process that with a second query - this depending upon what is in the keyid field.

A view (instead of temp table) may be a better choice, depending upon how you use the data.

Posted 10-Apr-14 1:21am

W∴ Balboos, GHB

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Accepted Answer · 2014-04-10T01:32:00

Solution 2

Hi,

If you're using SQL Server 2005 or greater, then this should work:

SQL

SELECT  keyid, field1, field2
FROM    (SELECT keyid, field1, field2,
                ROW_NUMBER() OVER (PARTITION BY keyid ORDER BY field1, field2) AS RowNumber
         FROM fnFunction('PARAMETER')
         ) AS sub
WHERE   sub.RowNumber = 1;

I am assuming that fnFunction is a table-valued function.

Posted 10-Apr-14 1:32am

Andrius Leonavicius

Comments

hansoctantan 10-Apr-14 7:45am

thanks, it works

Andrius Leonavicius 10-Apr-14 7:47am

You're welcome. Thank you.