SQL Server Performance Forum – Threads Archive

Duplicated records

Hello, guys.
With statement like this:
select * from table1 where exists
(select * from table1 as t
where t.column1 = table1.column1 and
t.int1 > table1.int1)
as many duplicated records as I have. What I mean is that if I have
1 1 1 1
1 1 1 2
1 1 1 3
1 1 1 4
then I would get back: 1 1 1 2
1 1 1 3
1 1 1 4 How do I get back only the record 1 1 1 4 ? Thanks once again for your help.

---

Not sure, if I understand, but what about using MAX and GROUP BY? —
Frank
http://www.insidesql.de

---

Assuming that the int1 column is the last one, it is strange that you should get back 1 1 1 4, though there is no 1 1 1 5, and not 1 1 1 1 eventhough there are 3 records that have a greater value on the int1 column.

---

Is next code what are you looking for (using FrankKalis idea)? select column1, column2, column3, max(int1) as int1
from table1
group by column1, column2, column3
order by 1, 2, 3

---

]]>