CODEWITHSUNDEEP
mysqlsql
Jake
Jake

Reputation: 3486

How to only select unique user_id from SQL

This is my sql:

SELECT `activity`.* 
FROM `activity`,`exchange`  
WHERE `activity`.`id` != 0 
AND `activity`.`type` !=6 
AND (`activity`.`type` = 4 OR `activity`.`type` = 5)
AND (`exchange`.`id` = `activity`.`user_id`) 
AND (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

Currently I get repeated user_id in the results. How can I get non-repeating user_ids from this so they are distinct?

Exchange is the user table.

Data:

CREATE TABLE IF NOT EXISTS `activity` (
  `id` bigint(255) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(255) NOT NULL,
  `type` int(2) NOT NULL,
  `link` varchar(255) NOT NULL,
  `time` int(50) NOT NULL,
  `add_text` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12583 ;

--
-- Dumping data for table `activity`
--

INSERT INTO `activity` (`id`, `user_id`, `type`, `link`, `time`, `add_text`) VALUES
(12567, 11615, 4, 'Aprender-Español&lang=es&discussionID=1131&t=viewDiscussion', 1360941086, 'for everyone wants to learn spanish'),
(12560, 6091, 4, 'Random-topics&lang=&discussionID=1130&t=viewDiscussion', 1360936869, 'Valentine''s Day'),
(12542, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=966&page=', 1360923369, 'AMIGA ALEMANA :)'),
(12541, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1036&page=', 1360923340, 'Want to re-learn Spanish'),
(12540, 11603, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360923203, 'Quiero practicar mi espanol!'),
(12539, 11603, 5, 'Cultura-y-Sociedad---Español&t=viewDiscussion&discussionID=106&page=', 1360922991, '¿Estás de acuerdo con que se hagan corridas de toro?'),
(12532, 11604, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1112&page=', 1360920608, 'Looking for a French Penpal!'),
(12527, 11495, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360909410, 'Why are you learning English?'),
(12503, 11100, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360876079, 'Why are you learning English?'),
(12502, 11100, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360876051, 'Why are you learning English?'),
(12491, 9641, 5, 'Culture-and-Society---English&t=viewDiscussion&discussionID=1129&page=', 1360874309, 'Rock music groups from your country?'),
(12490, 1874, 4, 'Culture-and-Society---English&discussionID=1129&t=viewDiscussion', 1360870935, 'Rock music groups from your country?'),
(12478, 11609, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1112&page=', 1360856071, 'Looking for a French Penpal!'),
(12448, 11604, 4, 'Aide-demandée,-en-français&lang=es&discussionID=1128&t=viewDiscussion', 1360832732, 'Apprendre le français'),
(12442, 11604, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1123&page=', 1360832130, 'Français!'),
(12433, 11605, 5, 'Learning-English&t=viewDiscussion&discussionID=54&page=', 1360830795, 'Why are you learning English?'),
(12406, 887, 5, 'Learning-English&t=viewDiscussion&discussionID=1127&page=', 1360796193, 'silver'),
(12405, 11600, 4, 'Learning-English&lang=&discussionID=1127&t=viewDiscussion', 1360796100, 'silver'),
(12399, 11596, 4, 'Apprendre-le-Français&lang=es&discussionID=1126&t=viewDiscussion', 1360794605, 'Je voudrais apprendre le française'),
(12384, 11595, 5, 'Learning-English&t=viewDiscussion&discussionID=173&page=', 1360791086, 'What is your favourite song? '),
(12349, 11578, 5, 'Learning-English&t=viewDiscussion&discussionID=478&page=', 1360771365, 'What kind of music do you like and why?'),
(12306, 11578, 5, 'Culture-and-Society---English&t=viewDiscussion&discussionID=1012&page=', 1360708606, 'Why are you learning a foreign language?'),
(12292, 6091, 5, '&t=viewDiscussion&discussionID=330&page=', 1360693956, 'Which day of the week do you hate the most? Why?'),
(12262, 11567, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360661625, 'Quiero practicar mi espanol!'),
(12238, 11544, 5, 'Learning-English&t=viewDiscussion&discussionID=909&page=', 1360648815, 'What instrument(s) do you play?'),
(12195, 887, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=1077&page=', 1360624305, 'Resources to Learn French'),
(12135, 6091, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=471&page=', 1360599702, 'Connaissez-vous le Verlan?'),
(12134, 2933, 5, 'Travel,_Live_and_Study_Abroad_-_English&t=viewDiscussion&discussionID=1124&page=', 1360596914, 'Would you like to make an exchange?'),
(12131, 3934, 5, 'Apprendre-le-Français&t=viewDiscussion&discussionID=471&page=', 1360595290, 'Connaissez-vous le Verlan?'),
(12123, 11543, 5, 'Aprender-Español&t=viewDiscussion&discussionID=1114&page=', 1360587427, 'Quiero practicar mi espanol!');

SQL Fiddle Schema

Upvotes: 1

Views: 501

Answers (4)

sgeddes
sgeddes

Reputation: 62861

This should be close -- gets the max time associated with a user and activity:

SELECT `activity`.* 
FROM `exchange`
    JOIN `activity` ON `exchange`.`id` = `activity`.`user_id`
    JOIN (
        Select User_Id, MAX(Time) timeMax
        FROM Activity
        WHERE `activity`.`id` != 0 
            AND `activity`.`type` !=6 
            AND (`activity`.`type` = 4 OR `activity`.`type` = 5)
        GROUP BY User_Id
    ) maxActivity ON `activity`.`user_id` = maxActivity.User_Id 
            AND `activity`.`Time` = maxActivity.timeMax
WHERE (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

Here's your Fiddle back: http://sqlfiddle.com/#!2/693e5/10

BTW -- No need for Activity.Type != 6 -- Just use Activity.Type IN (4,5) -- or keep the OR above.

SELECT `activity`.* 
FROM `exchange`
    JOIN `activity` ON `exchange`.`id` = `activity`.`user_id`
    JOIN (
        Select User_Id, MAX(Time) timeMax
        FROM Activity
        WHERE `activity`.`id` != 0 
            AND `activity`.`type` IN (4, 5)
        GROUP BY User_Id
    ) maxActivity ON `activity`.`user_id` = maxActivity.User_Id 
            AND `activity`.`Time` = maxActivity.timeMax
WHERE (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

Upvotes: 1

Scotch
Scotch

Reputation: 3226

Would this be something that you could use?

    Select user_id, max(time), add_text FROM activity GROUP BY user_id

Upvotes: 0

user8128167
user8128167

Reputation: 7696

If the user id is a unique integer, you might consider making the user id the primary key for your table.

Upvotes: 0

CR41G14
CR41G14

Reputation: 5594

Can you use the Distinct or Group BY? OR use Joins in your FROM to join the tables? Maybe if you post some data it will be more clear

SELECT DISTINCT `activity`.* 
FROM `activity`,`exchange`  
WHERE `activity`.`id` != 0 
AND `activity`.`type` !=6 
AND (`activity`.`type` = 4 OR `activity`.`type` = 5)
AND (`exchange`.`id` = `activity`.`user_id`) 
AND (`exchange`.`photo` != 0) 
ORDER BY `activity`.`id` DESC  
LIMIT 4

Upvotes: 0

Related Questions