how to get records on the basis of two distinct columns

Question

3.00/5 (2 votes)

See more:

if i have a table containing three columns, with some records, I want to select records in which column 1 and column3 comes once if they are multiple times

That means distinct column1 and column3

For Better Understanding

XML

column1 column2 column3
user1   a1  category1
user2   a2  category2
user1   a3  category2
user1   a4  category1
user3   a5  category2


Output

column1 column2 column3
user1   a1  category1
user2   a2  category2
user1   a3  category2
user3   a5  category2

Posted 1-Apr-14 9:23am

binadi007

Updated 1-Apr-14 10:02am

Herman<T>.Instance

v3

Add a Solution

Comments

Richard C Bishop 1-Apr-14 15:26pm

You literally used the word to use in your query(distinct).

binadi007 1-Apr-14 15:29pm

distinct check on the basis of the first column if available 2nd column then distinct check according to 2nd column and so on
thats why i m not able to get right output
I think view be solution but not getting it from view
a little thinking about stored procedure can anyone help me regarding it

Richard C Bishop 1-Apr-14 15:30pm

I see, yeah that is tricky.

binadi007 1-Apr-14 15:30pm

can anyone write a snippet for better understanding......thanks in advance

Maciej Los 1-Apr-14 15:35pm

Please, see my answer. Sample data would be helpfull. Please, improve your question. Use "Improve question" widget.

binadi007 1-Apr-14 15:41pm

Please See The Question again as i put an example now

binadi007 1-Apr-14 15:59pm

I think the solution of it is stored procedure and no other way (i think so, may be view will help little bit).....

1 solution

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)

Herman<T>.Instance · Accepted Answer · 2014-04-01T09:40:00

Solution 2

You can count via ROW_NUMBER() function and only ROW_NUMBER() with value = 1 you need to select.

Your query will look something like:

SQL

Select Column1, Column2, Column3
FROM
(
    SELECT ROW_NUMBER() over (Partition By Column1, Column3 Order by Column1, Column3) as Rownr, Column1, Column2, Column3
    FROM Table1
) as DataSet
where Rownr = 1

Posted 1-Apr-14 9:40am

Herman<T>.Instance

Updated 1-Apr-14 11:16am

v3

Comments

binadi007 1-Apr-14 15:49pm

I didn't get the output that i mentioned above as an example
Even I got a column with name Rownr and value in it be 1

Herman<T>.Instance 1-Apr-14 16:00pm

Did you execute both selects ? Or only the inner? The inner statement pre selects your data, the outer statement the requested columns.

Herman<T>.Instance 1-Apr-14 16:02pm

you were right. The columns Column1, Column2, Column3 were not set in the inner select. See my changes

binadi007 1-Apr-14 16:06pm

in this a problem is that when running internal select command rownr column added extra with value 1,2,3 and so on and when execute both select command then output will only one record having rownr = 1

Herman<T>.Instance 1-Apr-14 16:16pm

I changed my query, so both queries have the three needed columns. But you need both statements in the way I have created. Only then you will see your three columns as distinct. So Select and SubSelect and not two selects after each other.

Maciej Los 1-Apr-14 16:10pm

Looks perfect!
+5!

binadi007 1-Apr-14 16:21pm

I execute it thrice but the snippet you provide only returns first row and nothing more than that

Herman<T>.Instance 1-Apr-14 17:17pm

added Partitioning to the query (see code). I do not have your DB so i cannot see the results as you can. Please inform me the result after test.

binadi007 1-Apr-14 16:26pm

can you tell me do you get the right output on your system as well?

Herman<T>.Instance 1-Apr-14 17:26pm

created a database, table and added your data. The result with the above query with partitioning is:
Column1 Column2 Column3
user1 a1 category1
user1 a3 category2
user2 a2 category2
user3 a5 category2

binadi007 2-Apr-14 1:59am

@digimanus thank you very much, really it works now not worked yesterday
Its really a cool snipet
@digimanus can u help me in telling which book (free as well as paid) i refer to understand sql
Thank You Once Again @digimanus For Your Help

Herman<T>.Instance 2-Apr-14 2:42am

Hi Binadi007,

do you understand what is happening in the snippet? What the effect of partition is when using the Row_Number function?

Learning good SQL? just SQL! There are several unleashed books that can really help you, but by doing SQL you learn the most. That's the good thing about codeproject. There is always someone that had the same issue earlier. Glad I could help you.

binadi007 2-Apr-14 11:46am

@digimanus, initially I didn't understand what was goin on but now i know.....the thing is Row_Number() assign number on the basis of the column that partition by and rest of the snippet is to get the record where row number be 1

Herman<T>.Instance 3-Apr-14 4:05am

Yep that's it