mySql query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

mySql query question

Author

Topic

pablowerk
Starting Member

3 Posts

Posted - 2008-09-12 : 16:47:06

I am sorry about posting a mySql question here, but I have received good help in prior questions with mssql 2000 so I am hoping there are a few other people who occasionally have to cross into the mySql relm...

I have this table,

CREATE TABLE `paw_tags_master_status` (
  `paw_tags_master_statusid` int(11) NOT NULL auto_increment,
  `paw_tags_masterlistid` int(11) NOT NULL,
  `paw_tags_statustypeid` int(11) NOT NULL,
  `adddate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`paw_tags_master_statusid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=28 ;

-- 
-- Dumping data for table `paw_tags_master_status`
-- 

INSERT INTO `paw_tags_master_status` (`paw_tags_master_statusid`, `paw_tags_masterlistid`, `paw_tags_statustypeid`, `adddate`) VALUES 
(1, 1, 2, '2008-09-04 16:21:13'),
(2, 901, 2, '2008-09-04 16:22:24'),
(3, 902, 2, '2008-09-04 16:23:41'),
(9, 1, 3, '2008-09-05 10:02:02'),
(10, 2, 2, '2008-09-05 10:05:15'),
(11, 2, 3, '2008-09-05 10:05:45'),
(12, 3, 2, '2008-09-05 12:29:42'),
(13, 3, 3, '2008-09-05 12:30:39'),
(14, 445, 2, '2008-09-09 20:17:59'),
(15, 445, 3, '2008-09-09 20:18:11'),
(16, 556, 2, '2008-09-09 22:36:02'),
(17, 556, 3, '2008-09-09 22:37:41'),
(18, 113, 2, '2008-09-11 09:43:56'),
(19, 113, 3, '2008-09-11 09:45:52'),
(20, 123, 2, '2008-09-11 10:10:27'),
(21, 123, 3, '2008-09-11 10:10:59'),
(22, 125, 2, '2008-09-11 10:12:09'),
(23, 125, 3, '2008-09-11 10:12:28'),
(24, 126, 2, '2008-09-11 10:17:43'),
(25, 126, 3, '2008-09-11 10:17:55'),
(26, 127, 2, '2008-09-11 10:21:46'),
(27, 127, 3, '2008-09-11 10:22:07');

I am trying to create a query that would output the distinct `paw_tags_masterlistid` values along with their most recent `paw_tags_statustypeid` value

I am trying to get the results back to look like this using one query.

paw_tags_master_statusid, paw_tags_masterlistid, paw_tags_statustypeid, adddate
9, 1, 3, 2008-09-05 10:02:02
2, 901, 2, 2008-09-04 16:22:24
3, 902, 2, 2008-09-04 16:23:41
11, 2, 3, 2008-09-05 10:05:45

Any help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 03:11:37

Do a self INNER JOIN with grouped MAX value.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 03:42:22

something like below in sql server

SELECT t.*
FROM paw_tags_master_status t
INNER JOIN (SELECT MAX(paw_tags_master_statusid) AS MaxStatus,
            paw_tags_masterlistid
            FROM paw_tags_master_status 
            GROUP BY paw_tags_masterlistid) t1
ON t1.paw_tags_masterlistid=t.paw_tags_masterlistid
AND t1.MaxStatus=t.paw_tags_master_statusid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources