CODEWITHSUNDEEP
mysqlsqlselect
CSharpDev
CSharpDev

Reputation: 869

SQL Select Max ID Where Condition Is Met

I have a table with 50-100k rows. I have an auto-increment ID field, and another field called Main_Port.

I want to select the last ID where Main_Port = x. And I want it to be efficient.

What I'm using right now is:

SELECT max(MANIFEST_ID)FROM MANIFEST_MSS.dbo.Manifest where Main_Port = x

Is this the best way to do it?

The table has potential to grow to 500k+ rows

Upvotes: 2

Views: 5167

Answers (5)

EBarr
EBarr

Reputation: 12036

Without knowing more about your data structure and indexing structure it's tough to say "this will be efficient. The query you use : 

SELECT max(MANIFEST_ID)
FROM   MANIFEST_MSS.dbo.Manifest 
WHERE  Main_Port = x

Could be quite efficient, depending ....

  • Is there an index on Manifest.MainPort ? or (Manifest.MainPort, ManifestID desc) ?
  • What sort of density are we talking on Main_Port? Meaning, for a given main_port how many distinct manifest ID's are there?

To add some perspective, 500k rows isn't that big in database terms. You've seen a few variations on queries here, and one may be marginally faster than another, but real speed is going to come down to having a good index ( Main_Port, ManifestID desc). Alternatley, if you really want to dig deep you'll want to materialize a summary view. Here is a decent explanation of the mechanics of materialized views.

Upvotes: 3

Darj
Darj

Reputation: 1403

SELECT MANIFEST_ID FROM MANIFEST_MSS.dbo.Manifest where Main_Port = x ORDER BY MANIFEST_ID DESC LIMIT 1

Seems to be the other way, but I don't know if it would be more efficient than yours

Upvotes: 2

Rohit Vyas
Rohit Vyas

Reputation: 1969

Is your Manifest_ID column is auto increment column then you can achive this below query also:

 SELECT TOP 1 MANIFEST_ID FROM MANIFEST_MSS.dbo.Manifest WHERE Main_Port = X order by    MANIFEST_ID DESC

Upvotes: 1

Diego
Diego

Reputation: 36176

MAX doesnt give you the last, it gives you the bigger value. And it is bad because it has to scan all rows to get the bigger value.

I would do this:

SELECT *
FROM MANIFEST_MSS.dbo.Manifest
where Main_Port = x limit 1
order by ID desc

Upvotes: 2

Sandeep Bansal
Sandeep Bansal

Reputation: 6404

You can do it using Order By if you want, although using MAX is fine.

SELECT MANIFEST_ID FROM MANIFEST_MSS.dbo.Manifest where Main_Port = x ORDER BY MANIFEST_ID DESC LIMIT 1

Upvotes: 2

Related Questions