Distinct rows from "non-distinct" data?

[fugifox]

I have a table with data which consists of:

123 Mike 33333
123 Jones 88888
456 Smith 55555
456 Peter 7777

and I would like to show distinct rows of the first column based on the minimum value of the third column. Specifically, I would like a query to show only

123 Mike 33333
456 Smith 55555

I've tried any possible combination of the distinct/distinctrow clause I could come up with without any success.

I would appreciate any help.

 

[John Big Booty]

You will not be able to do this in one step you will need to first create a query that selects only your two numeric values and groups by the first and finds the minimum of the second. You will then use the results of that query linked back to your table by the minimum of your second number to collect the name and add that to your results. See the attached for an example.

 

[fugifox]

You will not be able to do this in one step you will need to first create a query that selects only your two numeric values and groups by the first and finds the minimum of the second. You will then use the results of that query linked back to your table by the minimum of your second number to collect the name and add that to your results. See the attached for an example.

Thnx a million!

And what if we have a situation where the third column is not unique?
Common in join tables.
Then the data would like

123 Mike 33333
123 Jones 88888
456 Smith 55555
456 Peter 7777
789 Julie 33333

I suppose the answer is to dynamically create a unique key via a query?
Something like
SELECT table.col1 & table.col3 AS key

 

[John Big Booty]

Yes you could do that, see the attached for an example.

However there is no guaranteeing that, that will continue to work once you get more records, so a PK on your data is certainly going to make things easier. Have a look at the second attached sample (db1A)

 

[ByteMyzer]

fugifox,

There IS a way to do this with a single SQL statement.

Assume a table like the following:

MyTable
-------
Field1 Field2 Field3
--------------------
123    Mike  33333
123    Jones 88888
456    Smith 55555
456    Peter 7777
789    Julie 33333

Your SQL statement would look like this (substitute the highlighted text with actual table/field names):

SELECT T1.*
FROM [B][I]MyTable[/I][/B] AS T1
WHERE T1.[B][I]Field3[/I][/B] = (
    SELEC MIN(T2.[B][I]Field3[/I][/B])
    FROM [B][I]MyTable[/I][/B] AS T2
    WHERE T2.[B][I]Field1[/I][/B] = T1.[B][I]Field1[/I][/B]);

Your results should look like the following:

MyQuery
-------
Field1 Field2 Field3
--------------------
123    Mike  33333
456    Smith 55555
789    Julie 33333