This is quite hard to explain, but Ill give it a shot.

I have a table, with 3 fields. One of the fields contains a numeric value [let's say it can either be 1, 2, 3 or 4 in this case].

I need to produce an SQL statement that will select another field [in this case; 'filename'], but only one for each of the numbers that I mentioned before.

e.g. lets say there are 2 records for '1', and 3 records for '4', I only want to retrieve the first record from '1' and the first record from '4'.

I think it may be possible using the

SELECT DISTINCT

command, but Im not too sure how??

Any help would be great!

Select Filename From Table Where Numeric = 1 Limit 1
Union All Select Filename From Table Where Numeric = 2 Limit 1
Union All Select Filename From Table Where Numeric = 3 Limit 1
Union All Select Filename From Table Where Numeric = 4 Limit 1

Thanks. I should have mentioned however, that the numeric field could have a limitless amount of numbers, not necesarrily just up to 4.

Is there an SQL command that could compensate for this? or would it just be best to create a PHP loop to do it?

Can you write custom aggregates in mysql? How, exactly, are you defining the first match?

Hmmm sorry I dont follow you?

Here is the SQL if that helps!

--
-- Table structure for table `tblportimg`
--

CREATE TABLE `tblportimg` (
  `portImgId` int(15) NOT NULL auto_increment,
  `portId` int(10) NOT NULL,
  `portImgFile` varchar(15) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`portImgId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=61 ;

--
-- Dumping data for table `tblportimg`
--

INSERT INTO `tblportimg` (`portImgId`, `portId`, `portImgFile`) VALUES
(49, 2, '26.jpg'),
(50, 2, '27.jpg'),
(51, 3, '28.jpg'),
(52, 1, '29.jpg');

Thanks.

DISTINCT may not work for a single query as it applies across the field list so if you have the same filename with 2 different IDs one will be missed.

GROUP BY should work though

SELECT portId,portImgFile FROM tblportimg GROUP BY portId

Originally Posted by chrishirst

DISTINCT may not work for a single query as it applies across the field list so if you have the same filename with 2 different IDs one will be missed.

GROUP BY should work though

SELECT portId,portImgFile FROM tblportimg GROUP BY portId

Wayyyy it worked! Thanks a lot