How to get distinct rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get distinct rows

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 05:57:28

Hi All,

How to get distinct rows from table without using distinct keyword

Any one please tell me,,,

Thanks in Advance
Sureshkumar

Suresh Kumar

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-02-25 : 06:00:32

Use Group by

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 06:17:19

Thanks for your reply Me.Senthil

How to get distinct rows based on a single column, when used distinct keyword in select query with retrieving multiple columns

ex: select distinct colA, colB, colC from table
now I want to fetch distinct rows from the table based on colA, but not on colB and colC.

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 06:24:55

Post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 06:48:05

here is the sample data

colA colB colC
101 1 A
102 2 B
102 3 C
103 4 D
104 5 E
104 6 F
104 7 G
105 8 H

expected o/p

colA colB colC
101 1 A
102 2 B
103 4 D
104 5 E
105 8 H

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 07:36:11

select colA,min(colB) as colB,min(colC) as colC from your_table
group by colA

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 08:20:28

Hi Madhivanan

The query is working for me. But I did not understand what the query actually do.
Can you please explain me the query.

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 08:47:13

quote:
Originally posted by soori457

Hi Madhivanan

The query is working for me. But I did not understand what the query actually do.
Can you please explain me the query.

Suresh Kumar

For each value of colA, take minimum values for colB and colC

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:00:41

quote:
Originally posted by madhivanan

select colA,min(colB) as colB,min(colC) as colC from your_table
group by colA

Madhivanan

Failing to plan is Planning to fail

wont work correctly unless colB and colC always goes in a sequential manner. If not in sequence, then the value returned may not be the ones in same row.

To be on safer side, this can be used


SELECT ColA,ColB,ColC
FROM
(
SELECT ColA,ColB,ColC,
ROW_NUMBER() OVER (PARTITION BY ColA ORDER BY colB ASC) AS Seq
FROM Table
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 09:09:35

Ohh..
Actually am looking for rows with distinct colA
For each value of colA, I should get the exact row, but not the minimum values of colB and colC. Otherwise I'll get the wrong data

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 09:13:58

quote:
Originally posted by soori457

Ohh..
Actually am looking for rows with distinct colA
For each value of colA, I should get the exact row, but not the minimum values of colB and colC. Otherwise I'll get the wrong data

Suresh Kumar

try mine and let me know if that will suit your need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 10:27:14

Thanks Visakh16 and Madhivanan for your queries.

Visakh, Your query is working and am able to get the exact records now.
But am getting the result as like we print, but not as a result set.
how can we get the result set

Thankyou

quote:
Originally posted by visakh16

quote:
Originally posted by soori457

Ohh..
Actually am looking for rows with distinct colA
For each value of colA, I should get the exact row, but not the minimum values of colB and colC. Otherwise I'll get the wrong data

Suresh Kumar

try mine and let me know if that will suit your need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 10:29:43

quote:
Originally posted by soori457

Thanks Visakh16 and Madhivanan for your queries.

Visakh, Your query is working and am able to get the exact records now.
But am getting the result as like we print, but not as a result set.
how can we get the result set

Thankyou

quote:
Originally posted by visakh16

quote:
Originally posted by soori457

Ohh..
Actually am looking for rows with distinct colA
For each value of colA, I should get the exact row, but not the minimum values of colB and colC. Otherwise I'll get the wrong data

Suresh Kumar

try mine and let me know if that will suit your need

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Suresh Kumar

what you mean by 'the result as like we print, but not as a result set'?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 10:39:07

Means, the result am getting as not in table format. Am getting the result as below format in Results pane.

colA colB colB
----------- ---------------- --------------------------
22017 737493 test
22687 737423 Need help designing a BJT

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-25 : 11:02:42

quote:
Originally posted by soori457

Means, the result am getting as not in table format. Am getting the result as below format in Results pane.

colA colB colB
----------- ---------------- --------------------------
22017 737493 test
22687 737423 Need help designing a BJT

Suresh Kumar

thats because you've selected results as text option. just change option to results to grid and see

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

soori457
Yak Posting Veteran

85 Posts

Posted - 2010-02-25 : 12:45:09

can you please tell me how to change the results option to grid

Suresh Kumar

Sachin.Nand

2937 Posts

Posted - 2010-02-25 : 13:09:24

Just press Cntrl+D before executing the query.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources