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 |
|
magikminox
Starting Member
27 Posts |
 Posted - 2008-03-25 : 10:40:01
|
| I have a table likeTradeID ActionID58096 366358096 366478901 223578901 2236I want to select the only the TradeID with the highest ActionIDI tried using select distinct tradeid,actionid From cct Where ActionID = (SELECT MAX(ActionID) FROM cct1 WHERE cct1.TradeID = cct.TradeID) group by tradeid,actionidbut the result is not correctplease helpIn god we trust,everything else we test. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-25 : 10:45:01
|
select tradeid, actionid from (select tradeid, actionid, row_number() over (partition by tradeid order by actioid desc) AS recid from table1) as d where recid = 1
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-25 : 14:29:26
|
| AlsoSELECT TradeID,max(ActionID)FROM cctGROUP BY TradeID |
|
|
|
magikminox
Starting Member
27 Posts |
Posted - 2008-03-26 : 04:06:17
|
| Hi PesoI dont quite understand your query.Pliz explain a bit or maybe clarifythanks a lotIn god we trust,everything else we test. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-26 : 04:12:03
|
I can, if you first tell me which parts of Books Online you read and didn't understand about the ROW_NUMBER() function.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
SELECT highest value of duplicate field