Need help with an SQL query in SQLserver

Hakan Axheim

7y1.9kSQL

Hi,

I want to make a SQL question that selects the row with the highest incrementid. The current sql query is made with a subquery. I don't want any subquerys regarding to performance.

Data consists by. The last column value is increment id.

RID OwnerRID CreatedBy CreatedDate ValidFromDate ValidToDate IncrementId

BB7B6D1A-20FA-E811-A92B-1C4D701E2313 25C71200-60AE-443D-A0B7-424A7EDEA24C JMeterTest 2018-12-07 14:01:00 2017-12-01 12:00:00 2017-12-01 12:15:00 1118

689CB599-7DFC-E811-A92B-1C4D701E2313 57627ABA-51F3-4692-B3DE-0BBDD196C828 JMeterTest 2018-12-10 14:15:00 2017-12-01 12:00:00 2017-12-01 12:15:00 1241

699CB599-7DFC-E811-A92B-1C4D701E2313 57627ABA-51F3-4692-B3DE-0BBDD196C828 JMeterTest 2018-12-10 14:15:00 2017-12-01 12:00:00 2017-12-01 12:15:00 1242

I have tried to make an SQL query but it does not work:

SELECT PN.RID

FROM part.Participation PN

LEFT JOIN part.Participation PN1 ON (PN.OwnerRID = PN1.Ownerrid AND PN1.IncrementId > PN.IncrementId)

INNER JOIN @PARTICIPATION PARAM_PN ON (PARAM_PN.RID = PN.OwnerRID);

Can someone give me a tip!

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

sudipta sanyal7y ago
Accepted answer
Hi
Please try this

SELECT PN1.RID
FROM part.Participation PN1
INNER JOIN @PARTICIPATION PARAM_PN ON PARAM_PN.RID = PN1.OwnerRID
LEFT OUTER JOIN part.Participation PN2 ON PN1.OwnerRID = PN2.Ownerrid
AND PN2.IncrementId > PN1.IncrementId
WHERE PN2.RID IS NULL

+1
Hakan Axheim7y ago
I think this SQL statement will work but is it good enough regarding to performance:

SELECT PN1.RID

FROM part.Participation PN1

INNER JOIN @PARTICIPATION PARAM_PN ON PARAM_PN.RID = PN1.OwnerRID

WHERE NOT EXISTS (SELECT RID FROM part.Participation PN2 WHERE PN1.OwnerRID = PN1.Ownerrid AND PN2.IncrementId > PN1.IncrementId)

Is NOT EXISTS good for performance?
0
Vignesh Mani7y ago
https://stackoverflow.com/questions/6881424/how-can-i-select-the-row-with-the-highest-id-in-mysql
0
Sourav Mukherjee7y ago
If you just want the Max RID value, then the below query will work.

SELECT MAX(RID) FROM [part].[Participation]
0
Ramesh Palanivel7y ago
Hi Hakan,

You can do achieve it several ways,

use MAX to get the maximum of RID
SELECT MAX(RID) FROM [PART.PARTICIPANT]
You achieve this it in this way also
SELECT top 1 RID FROM [PART.PARTICIPANT] ORDER BY RID DESC
0
Hakan Axheim7y ago
These are the columns in the table part.Participation

RID
OwnerRID
CreatedBy
CreatedDate
ValidFromDate
ValidToDate
IncrementId

and RID is primary key
0
Ramesh Palanivel7y ago
Hi Hakan,

Please attach your table details clearly. Then only we can easily able to provide the valueable solution.
0