Sql Query for Different result

Question

Hello Friends,

I am using mysql for database. I have to find the distinct latest result for a customer having different values for ContentPrvdr field. I am using following sql query:

SELECT  distinct ContentPrvdr,LocalDatabaseId,Website,BusID,LastUpdated,
UserCat1Rank_Local,UserCat1Count_Local,Citations,PhotoCount,
VideoCount,Cat_Count FROM local_database WHERE CMCustomerID=10  
ORDER BY LocalDatabaseId,LastUpdated LIMIT 0,3

to find the result,but it will return three result having same value for ContentPrvdr.But i want different value results for ContentPrvdr. Here are the sample data for test.

LocalDatabaseId     CMCustomerID    FranchiseName   ContentPrvdr    BusName     ConvBusName     KeyWBizName     KeyWCat     Website     LocationNmbr    PhoneLoc    StreetLoc   Cat_Count   Description_Local   Citations   PhotoCount  VideoCount  UserContent 
41  15  2 For 1 Pizza Co    bing    2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   http://st1.map  1   3232699421  3480 E CESAR E CHAVEZ AVENUE    1       0   0   0   0
41  15  2 For 1 Pizza Co    bing    2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   NULL    1   3232699421  3480 E CESAR E CHAVEZ AVENUE    1       0   0   0   0
56  15  2 For 1 Pizza Co.   Google  2 For 1 Pizza Co.   2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   Not Specified   1   2137494515  2528 S. FIGUEROA STREET 2       0   3   0   0
56  15  2 For 1 Pizza Co.   Google  2 For 1 Pizza Co.   2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   Not Specified   1   2137494515  2528 S. FIGUEROA STREET 2   Fresh N Ho  23  2       1
65  15  2 For 1 Pizza Co    Google  2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   Not Specified   1   3232699421  3480 EAST CESAR E CHAVEZ AVENUE 1       0   0   0   0
65  15  2 For 1 Pizza Co    Google  2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   Not Specified       3232699421‎ 3480 EAST CESAR E CHAVEZ AVENUE 1       25  0   0   1
126 15  2 For 1 Pizza Co    yellopages  2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   http://www.yellow   1   5628610936  5720 IMPERIAL HWY STE Q 2   EATING PLACE    0   0   0   0
126 15  2 For 1 Pizza Co    yellopages  2 For 1 Pizza Co    2 FOR 1 PIZZA CO    2 For 1 Pizza Co, Los Angeles, CA   Pizza Restaurant, Los Angeles, CA   http://www.yello    1   5628610936  5720 IMPERIAL HWY STE Q 2   EATING PLACE    0   0   0   0

Please Some body help me how can i get this result with distinct ContentPrvdr values .

Thanks in advance

Answer 1

You'll probably want to do something like:

SELECT *
FROM
  (
    SELECT ContentPrvdr, LocalDatabaseId, MAX(LastUpdated) AS Updated
    FROM local_database
    WHERE CMCustomerID = 10
    GROUP BY ContentPrvdr, LocalDatabaseId
    ORDER BY Updated DESC
  ) SQ
INNER
JOIN local_database ld
ON SQ.ContentPrvdr = ld.ContentPrvdr
AND SQ.LocalDatabaseId = ld.LocalDatabaseId
AND SQ.Updated = ld.LastUpdated
LIMIT 0, 3

I've specifically structured the query this way, with not all the columns listed in the "SQ" block so that the core of the logic can be seen. the columns that you put in the SELECT statement in "SQ" (and thus GROUP BY" and also "JOIN ON") will need to be whichever ones are in the "local_database" table and identify the rows uniquely

Answer 2

Try something like:

SELECT *
FROM (
    SELECT
        `ContentPrvdr`,
        `LocalDatabaseId`,
        `LastUpdated`
    FROM `local_database` AS `inner`
    WHERE `CMCustomerID`=10
    ORDER BY `LastUpdated` DESC
) AS `outer`
GROUP BY `ContentPrvdr`
ORDER BY `LocalDatabaseId`, `LastUpdated`
LIMIT 0,3

If you want to get the latest value of a column named in the GROUP BY clause, you need to sort it in a sub-query, because MySQL doesn't appear to have any way to specify that to the GROUP BY clause itself.

Answer 3

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 is unique on your database as it doesn't appear to have any obvious primary key.

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

This is the data I used to test that the query works:

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);

And this is the result I get for this data:

10, 2, 1, 2, 6
10, 1, 2, 2, 1
10, 4, 4, 4, 8

If this is not correct, please suggest adjustments to the test data and/or expected result to show what you do want.