CODEWITHSUNDEEP
sqlsqlite
aadu
aadu

Reputation: 3254

Large SELECT queries seem to return a different order to identical but smaller SELECT queries?

I am performing an SQLite query to get all the image paths for a bunch of image ids. The order the ids are in needs to be maintained. If I query 5 ids, 5 paths are returned in the correct order. However if I keep the query the exact same but query 72,000 ids, the order I receive back is different?

Why is this happening? I've copy and pasted the 2 queries and the 2 results (obviously minus the full 72,000 ids and results).

Does this make sense to anyone?

Query (With 5 IDs)

SELECT Path FROM Images WHERE MicrosoftId IN ('78003','78010','78080','78149','78196')

Result (This is correct!)

"~/ImagesPath/36/20160818_082235_000.jpg"
"~/ImagesPath/36/20160818_082904_000.jpg"
"~/ImagesPath/36/20160818_092336_000.jpg"
"~/ImagesPath/36/20160818_101818_000.jpg"
"~/ImagesPath/36/20160818_105621_000.jpg"

Query (With 72,000 IDs)

SELECT Path FROM Images WHERE MicrosoftId IN (('78003','78010','78080','78149','78196','78229','78231','78236','78237','78238','78248','78250'...etc 72,000 more)

Result (This is incorrect! Why did the order change?)

"~/ImagesPath/36/20160818_050623_000.jpg"
"~/ImagesPath/36/20160818_050710_000.jpg"
"~/ImagesPath/36/20160818_050844_000.jpg"
"~/ImagesPath/36/20160818_050449_000.jpg"
"~/ImagesPath/36/20160818_050757_000.jpg"
etc... to 72,000

EDIT: How would you do a JOIN on these two queries?

SELECT MicrosoftId FROM TagsMSCV WHERE name IN ('" + some vars + "') GROUP BY MicrosoftId ORDER BY COUNT(MicrosoftId) DESC

and 

SELECT Path FROM Images WHERE MicrosoftId IN ('" + results from above query + "')

Upvotes: 1

Views: 26

Answers (3)

Joakim Danielson
Joakim Danielson

Reputation: 51973

SELECT Path FROM Images i
JOIN TagsMSCV t ON i.MicrosoftId = t.MicrosoftId
WHERE t.name IN ('" + some vars + "') GROUP BY t.MicrosoftId ORDER BY
COUNT(t.MicrosoftId) DESC

This join should do the trick

Upvotes: 1

amphetamachine
amphetamachine

Reputation: 30595

One solution is to organize your query into a JOIN:

select * from
    (
        select 78003 as mid
        union select 78010
        union select 78080
        union select 78149
        union select 78196
        # and so on...
    ) mids
    inner join Images on (Images.MicrosoftId = mids.mid);

That way, the query should use the ordering of the constructed mids table.

If you want to be extra sure about the ordering, you could tack on an ordering column to your constructed table:

select * from
    (
        select 78003 as mid, 1 as midOrder
        union select 78010, 2
        union select 78080, 3
        union select 78149, 4
        union select 78196, 5
        # and so on...
    ) mids
    inner join Images on (Images.MicrosoftId = mids.mid)
    order by midOrder

Upvotes: 1

Mureinik
Mureinik

Reputation: 311518

If you don't explicitly specify an order by clause, there's no guarantee on the order the rows will be returned in. The database is free to return them in whatever order would be most efficient. Even if you do happen to get the result in the order you want, you can't count on it - any number of circumstances (e.g., rebuilding indexes, or just a row that happened to be on some cache) may change the order the next time you run the quetry.

TL;DR, if you care about the order of rows, you should add an order by clause:

SELECT   Path 
FROM     Images
WHERE    MicrosoftId IN ('78003','78010','78080','78149','78196', /* etc ...*/)
ORDER BY MicrosoftId

Upvotes: 1

Related Questions