An Alternative for DISTINCT AND GROUP BY by using ROW_NUMBER()

Question

Post reply

An Alternative for DISTINCT AND GROUP BY by using ROW_NUMBER()

ASFSDJG

Mr or Mrs. 500

Points: 500
More actions
April 27, 2011 at 5:01 am

#236388

An Alternative for Distinct AND GROUP BY using row_number()
How can I generate distinct values from table without using Distinct and GROUP BY
example
Column1Column2 Column3
123
143
163
231
251
Result SET
Column1
1
2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Hunterwood SSCrazy Points: 2074 More actions · Answer 1

Hi,

why don't you want to use DISTINCT or GROUP BY?

/Markus

ASFSDJG Mr or Mrs. 500 Points: 500 More actions · Answer 2

Yes I got the solution !!

SELECT Column1

FROM

(SELECT ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column1) As RNO, Column1

from

<Table_Name>)A WHERE RNO = 1

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

ASFSDJG (4/27/2011)
Yes I got the solution !!
SELECT Column1
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column1) As RNO, Column1
from
<Table_Name>)A WHERE RNO = 1

Here's another solution.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

krisgomathi SSC Journeyman Points: 95 More actions · Answer 4

Awesome solution, this really worked!!!:-):-):-):-):-):-D

An Alternative for DISTINCT AND GROUP BY by using ROW_NUMBER()

Cookies on SQLServerCentral