Select * with Distinct on Some

[bdichiara]

How can I select all fields in a table, but only want distinctive records based on 2 or 3 fields?

Like:

I want to display: all fields, but distinct on f_name, l_name, and extension

_______________
_brian.

 

[rac2]

How could that be?

Suppose you have two rows with the same l_name, f_name, and extension. The other columns might be make and model with different values.

For example

Homer Simpson 5202 Ford Taurus
Homer Simpson 5202 Toyota Camry

DISTINCT works like this

SELECT DISTINCT l_name, f_name, extension
FROM MyTable

This query yields

Homer Simpson 5202

Which car will you show, the Ford or the Toyota?

 

[bdichiara]

hmm... good point. well, in my situation, it's a list of employees, and the only time they'd be in there twice is if they're listed under 2 departments, like:
Bob Jones - Executives
Bob Jones - Lending

The only reason they're listed twice is to make them easier to find. Some people would look under Executives and others may look under Lending, so we put them in both places.

In the case that i'm trying now, I actually won't be displaying the department, but I will be displaying their photo, and unfortunately, they're photo won't be the same value in the database, however it will be the same photo, so the one associated with the first record will be fine.

I guess is there a way to select the first of each group, kinda like Distinct?

_______________
_brian.

 

[r937]

min(photo) should do

r937.com | rudy.ca

 

[bdichiara]

well, that actually led me to Group by, and I think that's going to work for this situation. I'm not quite sure how group by works in any other circumstance, but for mine, i'm able to group by f_name, l_name, and ext.

_______________
_brian.