need a query for below scenario

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

need a query for below scenario

Author

Topic

rocky123
Starting Member

4 Posts

Posted - 2012-04-01 : 00:04:52

let the table name be: table
which contains 4 column A,B,C,D
below is the values in the table

A B C D
1 1 1 1
1 1 1 b
1 1 2 b
1 1 3 b

now we should take distinct of A,B,C then the values will be
A B C D
1 1 1 1 (the D values should be the 1st distinct value which is 1)
1 1 2 b
1 1 3 b

now we should take distinct of A,B,D from the derived output then

A B C D
1 1 1 1
1 1 2 b (the C values should be the 1st distinct value which is 2)

can anybody give a query for this??... even if there are 40 column this similar pattern of data shoud be retrived :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-01 : 00:24:07

[code]
SELECT t.*
FROM table t
INNER JOIN (SELECT A,B,D,MIN(C) AS MinC
FROM table
GROUP BY A,B,D
)t1
ON t1.A = t. A
AND t1.B = t.B
AND t1.D = t.D
AND t1.MinC = t.C
[/code]

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

rocky123
Starting Member

4 Posts

Posted - 2012-04-01 : 00:59:52

quote:
Originally posted by visakh16

SELECT t.*
FROM table t
INNER JOIN (SELECT A,B,D,MIN(C) AS MinC
FROM table
GROUP BY A,B,D
)t1
ON t1.A = t. A
AND t1.B = t.B
AND t1.D = t.D
AND t1.MinC = t.C

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

rocky123
Starting Member

4 Posts

Posted - 2012-04-01 : 01:07:14

quote:
Originally posted by visakh16

SELECT t.*
FROM table t
INNER JOIN (SELECT A,B,D,MIN(C) AS MinC
FROM table
GROUP BY A,B,D
)t1
ON t1.A = t. A
AND t1.B = t.B
AND t1.D = t.D
AND t1.MinC = t.C

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

Hi Visakh,
Thanks for the quick reply.But the query should retrieve the a,b,d distinct data from the intermediate a,b,c distinct data with its corresponding d value.Whereas ur query retrieve the a,b,d distinct from the main table instead of the derived a,b,c distinct dataset.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-01 : 01:15:35

[code]
;with tbl
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY A, B, C ORDER BY D ASC) AS Rn,*
FROM table
)

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY A,B,D ORDER BY C ASC) AS Seq,*
FROM tbl
WHERE Rn=1
)t
WHERE Seq=1
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources