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

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.

How do you determine "Oldest" with this data? Is it the "Lowest" PVLICP as mentioned in your first sentence? — JNevill, May 02 '22 at 15:58

JNevill · Accepted Answer · 2022-05-02T16:57:09.773

0

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.

edited May 02 '22 at 16:57

answered May 02 '22 at 16:00

JNevill

46,980
4
38
63

@JNvill it doesn't do anything. It keeps all the records there. If I select SELECT PVISBN, PVWHS, MIN(PVLICP), PVRZNE , PVRLOC , PVAZNE , PVALOC ,PVLPRG . The problem is not just the MIN but it should keep only 1 record where 1) there is a value 2) the lowest value = oldest. – Brianski May 02 '22 at 16:15
when you add columns to the selection list and also add them to the GROUP BY clause, then you basically disable the grouping of PVLICP. If you cannot use MAX(PVRZNE) or MIN(PVRZNE) for each of those added columns (and leave them out of the GROUP BY clause), then you should have a look at the "ROW_NUMBER() OVER (PARTITION BY ... ORDER BY) ... WHERE row=1" – Gert-Jan May 02 '22 at 16:34
@Brianski I've updated the answer to show how to get the full row back using window functions as suggested by Gert-Jan – JNevill May 02 '22 at 16:57
@JNevill thank you very much. For some reason, I have another issue now, updated the question. – Brianski May 02 '22 at 18:43
In the future, if you could, please open a new question as you progress with your code. This way Stackoverflow visitors can see the distinct original problem and the solution on the one page. – JNevill May 03 '22 at 13:53

score 0 · Answer 2 · answered May 02 '22 at 16:18

0

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;

answered May 02 '22 at 16:18

Divya Varada

19
1

I am sorry this one keeps loading but no results. plus, i think this one will just filter out all blanks. I want to be sure we have a record if it is available. If for any reason there is one PVISBN I want to have it. And if multiple, then the lowest. – Brianski May 02 '22 at 16:26

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

2 Answers2