Comparing dates to choose most recent

[SofiaMaria]

Hi there,

I have a database where I want several fields to be distinct and I managed to get that working fine, however I still get several release dates for the same version number of a product. I want to have only the most recent date to be returned though.

SELECT DISTINCT [K1k3 licenses].SITE_ID, [K1k3 licenses].CO_NAME, [K1k3 licenses].PROD_NAME, [K1k3 licenses].PROD_RELEASE, [K1k3 licenses].SUPP_EFFECTIVE_DATE
FROM [K1k3 licenses];

I know it must be possible and I have tried to get it working for the past 3 days.

I tried to create a view and then I could rejoin that with the original table, but I still don't know how I can compare the dates to select the most recent one

CREATE VIEW query1 AS
SELECT DISTINCT [K1k3 licenses].SITE_ID, [K1k3 licenses].CO_NAME, [K1k3 licenses].PROD_NAME, [K1k3 licenses].PROD_RELEASE
FROM [K1k3 licenses];

Any help would be much appreciated,

Sofia

 

[Remou]

Perhaps:

SELECT k.SITE_ID, k.CO_NAME, k.PROD_NAME,
       Max(k.PROD_RELEASE)
FROM [K1k3 licenses] k
GROUP BY k.SITE_ID, k.CO_NAME, k.PROD_NAME

 

[SofiaMaria]

I will try that. Thank you

 

[fneily]

You have a table that looks like this:
SiteID
Co_Name
Prod_Name
Prod_Release
Supp_Effective_Date

Have you noticed it's not normalized?

 

[SofiaMaria]

Hi fneily,

I know the table is not normalized. These are only a few fields.

Thing is I am trying to help a friend out and that is how his company has the table. I am just trying to make his job easier and cutting down on stuff he needs to go through.