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

Question

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) (I need to get only the first row from 2 that I am getting back).

TableSeven AS (
SELECT PVISBN, PVWHS, PVLICP,  PVRZNE, PVRLOC, PVAZNE, PVALOC, PVLPRG,
         ROW_NUMBER() OVER (PARTITION BY PVISBN, PVRZNE ORDER BY PVLICP --, PVRLOC, PVAZNE, PVALOC, PVLPRG 
         ASC) AS rn
   FROM [REPIT].[LEVYDTA].[WHSPDVT]
   WHERE PVSPDT BETWEEN @Last2WeekDATE AND @LWDate 
--AND PVISBN='0164556221'
) , 

TableTwelve AS (
SELECT PVISBN, PVWHS, PVLICP,  PVRZNE, PVRLOC, PVAZNE, PVALOC, PVLPRG, rn
FROM TableSeven
WHERE rn = 1
), 

I keep getting 2 rows and should get only the 1st one

If someone may have an idea, I will appreciate.

Answer 1

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

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

---

To your comment, if you are wanting all columns in the output, then consider window functions (also called Analytics Functions or Ordered Analytics Functions) to help identify the min() for a group/window (PVISBN in this case) and then filter:

SELECT *
FROM 
  (
    SELECT yourtable.*, ROW_NUMBER() OVER (PARTITION BY PVISBN ORDER BY PVLICP ASC) as rn
    FROM yourtable
  ) dt
WHERE rn = 1;

That will generate a row_number, starting at 1 for each row for each distinct PVISBN (you can run just the subquery to see how that looks). Then we just filter for rn of 1, which will be the record for that PVISBN with the lowest PVLICP.

Answer 2

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;