Hard to explain question, possibly SELECT DISTINCT?

[rollOrDie]

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

 

I have a table, with 3 fields. One of the fields [portId] contains a numeric value [which can be any number, however multiple records may use the same number in some cases].

 

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!

[fenway]

How do you define "first".

[rollOrDie]

Sorry I should have said. I meant I want to limit the result to 1 record for each number. So by first, Im guessing MySQL would select the first one it came across and ignore the rest that follow for that number?

[bluejay002]

i think what fenway is trying to say is that what are your parameters that would make it first? saym do you  have a field 'date'? if so, then order by that field in ascending order.

[rollOrDie]

Oh. maybe the following SQL query will help to explain what I need. Basically it needs to be equivalent to this:

SELECT filename FROM tblPort WHERE numeric = 2 LIMIT 1

The only thing is, it needs to be executed for every numeric value [which could be anything], not just 2.

Does that help?

[bluejay002]

we do get that. But its hard to sort something without an auto increment variable or date. those were usually the basis for us to come up with an ordering... otherwise your table has some other sort of tagging.