quote:

---

Originally posted by visakh16

SELECT t1.*
FROM Scenario t1
INNER JOIN (SELECT col1,MAX(col5) AS col5
FROM Scenario
GROUP BY col1)t2
ON t2.col1=t1.col1
AND t2.col5=t1.col5

---

quote:

---

Originally posted by Vaishu

HI

Please help I didn't get right solution for this problem. So I am posting again with full details.
Scenario

col1---col2------col3----col4-----col5------col5
123-----AB--------WE-----Name------Add------Prod1
123-----AB--------DC-----Name------Add------Pro512
123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

How do I select Distinct row by col1 from the above scenario. Expected result will be

123-----AB--------WE-----Name------Add------Prod1
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------DC-----Name------Add------Pro512
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78

OR

123-----AB--------FR-----Name------Add------Prt78
389-----AB--------DC-----Name------Add------Prt78
482-----AB--------DC-----Name------Add------Prt78


Please Help.

---

quote:

---

Originally posted by Vaishu

Hi

Why Do I have use Col5. It is just sample how the data will be in the table.
IN SHORT IF I GET MORE THAN ONE SAME VALUE IN col1, I NEED TO SELECT ANY ONE OF THE ROW WHICH HAS MORE THAN ONE VALUE IN col1

quote:

---

Originally posted by visakh16

SELECT t1.*
FROM Scenario t1
INNER JOIN (SELECT col1,MAX(col5) AS col5
FROM Scenario
GROUP BY col1)t2
ON t2.col1=t1.col1
AND t2.col5=t1.col5

---

---

SELECT t.Col1,
t.col2,
t.col3,
t.col4,
t.col5
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col1) AS RowNo,
Col1,
col2,
col3,
col4,
col5
FROM Scenario
)t
WHERE t.RowNo=1

quote:

---

Originally posted by Vaishu

Hi

Why Do I have use Col5. It is just sample how the data will be in the table.
IN SHORT IF I GET MORE THAN ONE SAME VALUE IN col1, I NEED TO SELECT ANY ONE OF THE ROW WHICH HAS MORE THAN ONE VALUE IN col1

quote:

---

Originally posted by visakh16

SELECT t1.*
FROM Scenario t1
INNER JOIN (SELECT col1,MAX(col5) AS col5
FROM Scenario
GROUP BY col1)t2
ON t2.col1=t1.col1
AND t2.col5=t1.col5

---

---

-- create our table 
create table foo (col1 int, col2 char(2), col3 char(2)
	,col4 char(4),col5 char(3),col6 varchar(6))


-- insert data
insert into foo
	select 123,'AB','WE','Name','Add','Prod1'
union all select
	123,'AB','DC','Name','Add','Pro512'
union all select
	123,'AB','FR','Name','Add','Prt78'
union all select
	389,'AB','DC','Name','Add','Prt78'
union all select
	482,'AB','DC','Name','Add','Prt78'




-- do the work
 SELECT t1.*
FROM foo t1
INNER JOIN (SELECT col1,MAX(col3) AS col3
FROM foo
GROUP BY col1)t2
ON t2.col1=t1.col1
AND t2.col3=t1.col3

-- results
482	AB	DC	Name	Add	Prt78
389	AB	DC	Name	Add	Prt78
123	AB	WE	Name	Add	Prod1

-- Using code from Visakh
-- do the work
 SELECT t1.*
FROM foo t1
INNER JOIN (SELECT col1,MAX(col5) AS col5
FROM foo
GROUP BY col1)t2
ON t2.col1=t1.col1
AND t2.col5=t1.col5

-- results
482	AB	DC	Name	Add	Prt78
389	AB	DC	Name	Add	Prt78
123	AB	FR	Name	Add	Prt78
123	AB	WE	Name	Add	Prod1
123	AB	DC	Name	Add	Pro512

quote:

---

Originally posted by DonAtWork

why would you not want to use an inner join?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

---