Select only one record where information is fully provided and/or the lowest value in it (SQL)-CodePudding

I have such a situation. I need to make a code work so that it selects only one PVISBN (Item Number) based on PVLICP (license plate) (should take only those that have value, and if multiple values are there then take the lowest)

SELECT PVISBN, PVWHS, PVLICP,  MAX(PVRZNE) AS PVRZNE, MAX(PVRLOC) AS PVRLOC, MAX(PVAZNE) AS PVAZNE, MAX(PVALOC) AS PVALOC, MAX(PVLPRG) AS PVLPRG 
FROM [REPIT].[LEVYDTA].[WHSPDVT]
WHERE PVSPDT BETWEEN @Last2WeekDATE AND @LWDate 
AND PVISBN='0164556221'

GROUP BY PVISBN, PVWHS, PVLICP

What I currently have, but I need to have only 1 line per one item. I need to leave only that record where there is value in PVLICP. If there are multiple records in PVLICP column for the same item number then i need to take the oldest one.

To see what I have

If someone may have an idea, I will appreciate.

CodePudding user response：

Assuming "Oldest" and "Lowest" is the same, you can use aggregation:

SELECT PVISBN, MIN(PVLICP) as PVLICP 
FROM yourtable
GROUP BY PVISBN;

CodePudding user response：

I think this would be helpful for you

SELECT PVISBN, PVWHS, MIN(PVLICP) as PVLICP , ------------ FROM tablename WHERE PVLICP IS NOT NULL or PVLICP <> '' group by PVISBN;