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.
| Author |
Topic |
|
Jay
Starting Member
1 Post |
 Posted - 2002-05-13 : 20:58:00
|
| Hi, I have a table named 'test' with three fields 'Cname','CLoc','Depth' respectively. If we insert the data likeInsert into test values ('AA','CA',0)Insert into test values ('BB','CA',1)Insert into test values ('CC','CA',2)Insert into test values ('XX','WA',1)Insert into test values ('YY','WA',2)Insert into test values ('ZZ','WA',4)I need to get output in a single SELECT statement as follows:'CC','CA',2'ZZ','WA',4 ....basically i need to get the 'Cname' for Maximum 'Depth' field.Any help greatly appreciatedthanksJay |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-13 : 21:08:09
|
| According to your desired results, you need Cloc instead of Cname:SELECT A.* FROM Test A INNER JOIN(SELECT Cloc, Max(Depth) MaxDepth FROM Test GROUP BY Cname) BON A.Cloc=B.Cloc AND A.Depth=B.MaxDepthEdited by - robvolk on 05/13/2002 21:09:33 |
 |
|
|
|
|
|
|
|
SQL query