StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You probably want something like this. I'm assuming that (ContentPrvdr, LocalDatabaseId, LastUpdated, CMCustomerID) is unique. If this is not the case, you need to specify something that <em>is</em> unique on your database as it doesn't appear to have any obvious primary key.</p> <pre><code>SELECT T4.* FROM ( SELECT T2.ContentPrvdr, T2.LocalDatabaseId, MIN(T2.LastUpdated) AS LastUpdated FROM ( SELECT ContentPrvdr, MIN(LocalDatabaseId) AS LocalDatabaseId FROM local_database WHERE CMCustomerID = 10 GROUP BY ContentPrvdr) AS T1 JOIN local_database AS T2 ON T1.ContentPrvdr = T2.ContentPrvdr AND T1.LocalDatabaseId = T2.LocalDatabaseId WHERE CMCustomerID = 10 GROUP BY ContentPrvdr, LocalDatabaseId ) AS T3 JOIN local_database AS T4 ON T3.ContentPrvdr = T4.ContentPrvdr AND T3.LocalDatabaseId = T4.LocalDatabaseId AND T3.LastUpdated = T4.LastUpdated WHERE CMCustomerID = 10 ORDER BY LocalDatabaseId, LastUpdated LIMIT 3 </code></pre> <p>This is the data I used to test that the query works:</p> <pre><code>CREATE TABLE local_database ( CMCustomerID int NOT NULL, ContentPrvdr int NOT NULL, LocalDatabaseId int NOT NULL, LastUpdated int NOT NULL, Website int NOT NULL); INSERT INTO local_database (CMCustomerID, ContentPrvdr, LocalDatabaseId, LastUpdated, Website) VALUES (10, 1, 2, 2, 1), (11, 1, 2, 2, 1), (11, 1, 1, 1, 2), (11, 1, 2, 1, 3), (10, 2, 2, 2, 4), (10, 2, 1, 3, 5), (10, 2, 1, 2, 6), (11, 3, 3, 3, 7), (10, 4, 4, 4, 8), (10, 5, 5, 5, 9); </code></pre> <p>And this is the result I get for this data:</p> <pre><code>10, 2, 1, 2, 6 10, 1, 2, 2, 1 10, 4, 4, 4, 8 </code></pre> <p>If this is not correct, please suggest adjustments to the test data and/or expected result to show what you do want.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload