SQL Syntax or code syntax suggestions.

Question

question

rcportillo asked Dec 15, '22

SQL Syntax or code syntax suggestions.

is the a funtion of code syntax to grab only records with a particular data field record.

for example permit Numbe 246172 is the same but I only want the typeno '2' record only

sql query

capture.jpg (13.7 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2022-12-20T08:49:38Z

anthony.green answered Dec 20, '22

So your desired output would be
Ctr-246172 with TypeNo 2
and
Ctr-234280 with TypeNo 1

So you only want the greatest TypeNo per individual permit?

For that you would want to use ROW_NUMBER() and a CTE to then filter on the maximum typeno

;WITH CTE AS
(
SELECT permit_number, subdivcol, typeno, ROW_NUMBER() OVER (PARTITION BY permit_number ORDER BY typeno DESC) AS RN
FROM <yourtable>
)
SELECT permit_number, subdivcol, typeno
FROM CTE WHERE RN = 1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question