# Distinct number in column with max value in another column

I have a list of numbers in column 1 that have duplicates. I want to pull distinct numbers from column 1 but also have it match the highest value in column 2.

Example:
16900 72000
16900 37560
16900 82000
16900 47557

I want the data to only show:
16900 82000

``````SELECT TOP 1 column1, column2
FROM myTable
ORDER BY column2 DESC
``````

If it's just 2 columns:

``````SELECT column1, MAX(column2) as highest
FROM myTable
GROUP BY column1
``````

If you have more than those 2 columns and only want the row with the highest column2 value:

``````;WITH CTE AS (SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2 DESC
FROM myTable)
SELECT column1, column2, column3
FROM CTE
WHERE rn=1
``````

My table is thousands and thousands of lines...with different values in each

16900
16901
16902
etc...
but repeated throughout

HOWEVER, column 2 tracks the unit's mileage which has been tracked over and over so that number is different for each unit

16900 375000
16900 372576
16900 380221
16901 275505
16901 267799
16905 172471
16901 281122

When I put in that formula it only pulled 1 unit (have over 1,000 units)

I will need all distinct numbers in column 1 and all max values for each of those distinct numbers so it returns that most recent miles for each unit.

IF it helps...another column has dates, if for some reason I can pull the distinct number and the latest date instead of max number

Hope that makes it more clear

Okay, sorry, I'm only 2 days old with SQL....
I used your solution and edited it...
My data had more than I needed so I limited it to active units by limiting the date by >45050

Select distinct HUUNIT, max(huhub)
from source.as400_ilfile_hubfile
where hudate >45050
group by HUUNIT

Works well...thank you!!