Distinct record from table. - 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

Distinct record from table.

Author

Topic

hspatil31
Posting Yak Master

182 Posts

Posted - 2011-12-13 : 01:08:35

Dear All,

I am having following output. In this I have joined two table. In that my first all 3 columns value is same but 4th one is different.
Now in this situation I want first record only.
Can anybody plz tell me how to do this ?

Query:

SELECT * FROM XYZ X INNER JOIN ABC A
ON X.Column1 = a.Column1

Column1 Column2 column3 column4
A AAA AAAA WW
A AAA AAAA QQ

Thanks and Regard's
Harish Patil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 01:38:56

On what basis you tell you want first record from second table? there's no concept of first and last in sql table so you've to specify first,last in terms of order of another column by means of ORDER BY clause.
Anyways, one thing you can do is use either of below and see if its what you want


SELECT X.Column1, X.Column2, X.column3  ,MAX(Column4) AS Column4
FROM XYZ X INNER JOIN ABC A
ON X.Column1 = a.Column1
GROUP BY X.Column1, X.Column2, X.column3 

or


SELECT X.Column1, X.Column2, X.column3  ,MIN(Column4) AS Column4
FROM XYZ X INNER JOIN ABC A
ON X.Column1 = a.Column1
GROUP BY X.Column1, X.Column2, X.column3

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

sunnyleoneo
Starting Member

19 Posts

Posted - 2011-12-13 : 02:09:46

Hi,

you can use ROW_NUMBER(). I have used sample data from my side.
It will be good if you can provide structure and data for both the table.

CREATE TABLE #xyz (Column1 varchar(20), Column2 varchar(20), column3 varchar(20))
INSERT INTO #xyz
(Column1, Column2, column3)
SELECT 'A', 'AAA', 'AAAA'
UNION ALL
SELECT 'A', 'AAA', 'AAAA'

CREATE TABLE #abc (Column1 varchar(20), Column2 varchar(20))
INSERT INTO #abc
(Column1, Column2)
SELECT 'A', 'WW'
UNION ALL
SELECT 'A', 'QQ'

SELECT * FROM (SELECT ROW_NUMBER() OVER( PARTITION BY x.Column1,x.Column2,x.column3 ORDER BY x.Column1,x.Column2,x.column3 ) sequence,x.Column1,x.Column2,x.column3,a.Column2 Column4 FROM #XYZ X INNER JOIN #ABC A
ON X.Column1 = a.Column1
)t WHERE t.sequence =1
GROUP BY X.Column1, X.Column2, X.column3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources