Query Needed - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Needed

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-05 : 06:03:39

If there are three columns

Die_Sequence X Y
1 2 5
2 2 5
3 2 5
4 2 6
5 2 7
6 2 8
7 2 8

and I want get distinct rows on X and Y with largest Die_Sequence i.e., the resulting record set should be

Die_Sequence X Y
3 2 5
4 2 6
5 2 7
7 2 8

what would be the query?

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-05 : 06:21:29

Something like this should do it:

SELECT MAX(t1.die_sequence), t1.x, t1.y
FROM tablename t1
JOIN
(
SELECT DISTINCT x, y
FROM tablename
) t2 ON t1.x = t2.x AND t1.y = t2.y
GROUP BY t1.x, t1.y

Raymond

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-05 : 06:57:20

How can I delete the the other rows except the MAX Die_Sequence. I just want to delete the duplicate rows except the MAX Die_Sequence

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-08-05 : 07:05:46

Delete the original table where the die_sequence is not in this result set.

DELETE t3
FROM tablename t3
LEFT JOIN
(
SELECT die_sequence=MAX(t1.die_sequence), t1.x, t1.y
FROM tablename t1
JOIN
(
SELECT DISTINCT x, y
FROM tablename
) t2 ON t1.x = t2.x AND t1.y = t2.y
GROUP BY t1.x, t1.y
) t4 ON t3.die_sequence = t4.die_sequence
WHERE t4.die_sequence IS NULL

Raymond

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-08-05 : 07:39:19

Thanks :), I did it this way

delete o from tblA o
where unit_number = 10 and die_sequence <> (select MAX(die_sequence)
from tblA i where i.unit_number = o.unit_number and
i.wafer_sequence = o.wafer_sequence and i.x = o.x and i.y = o.y)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources