modify sql query in such a way that it will select distinct records

Question

0.00/5 (No votes)

See more:

i am using the following sql query..

C#

string query = "select * from (select ROW_NUMBER() OVER (ORDER BY style_no) rn ,* from add_product) a where rn BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1";

it is giving records something like this...

C#

Style_no               Text             Age
JC001                  Text1            15
JC001                  Text2            18
JC001                  Text5            19
JC002                  Text1            80
JC002                  Text8            45
JC002                  Text9            60

But i want results something like this :

C#

JC001                   Text1              15
JC002                   Text1              80

Each row with same style_no column must be selected only once. i hope you understand what i am saying.

i want to modify above query in such a way that it should work like as it is.. only change i want is that it should select the records only once where style_no is more then once.

Posted 23-Apr-15 20:17pm

Harpreet_125

Updated 23-Apr-15 20:19pm

v3

Add a Solution

Comments

Tomas Takac 24-Apr-15 2:36am

What should be then in Text and Age columns? What' the rule? Select rows where Text = Text1?

Maciej Los 24-Apr-15 3:16am

My virtual 5!

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)

Sinisa Hajnal · Accepted Answer · 2015-04-23T20:40:00

SQL

SELECT * FROM  (
    SELECT ROW_NUMBER() OVER (ORDER BY style_no) rn, style_no, text, age FROM 
    (
-- take everything and rank it by style_no
        SELECT ROW_NUMBER() OVER (ORDER BY style_no) rank, style_no, text, age
        FROM add_product
    ) ranking
    WHERE rank = 1 -- take only one of those (you didn't specify any ordering)
) paging
where rn BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1;

Or you could play with SELECT TOP 1 * FROM ....but I think the above is cleaner.