sql query problem

pitwi

I have the following query in my own photo database:

select distinct left(Nr, 8) as Date, Ort from Fotos where Typ='D' order by left(Nr, 8) desc, Ort

Where Nr is a unique string with date + a hyphen + a sequential number in the form yyyymmdd-xxx. Ort is a string, the location of the photo. This works fine so far and I get a list like this:

20220717 Lindabrunn, Symposion
20220717 Feistritzsattel
20220717 Pernitz, Schärfthal
20220715 Mannswörth
20220715 Wienerbergteich
20220715 Zentralfriedhof
20220712 V
20220710 Himberg

But what I really want is the first Nr for a date and a location like this:

20220717-001 Lindabrunn, Symposion
20220717-045 Feistritzsattel
20220717-103 Pernitz, Schärfthal
20220715-001 Mannswörth
20220715-009 Wienerbergteich
20220715-033 Zentralfriedhof
20220712-001 V
20220710-001 Himberg

Can someone help? Thanks

Richard Deeming

Assuming SQL Server, try something like this:

WITH cteOrderedData As
(
SELECT
Nr,
Ort,
ROW_NUMBER() OVER (PARTITION BY Left(Nr, 8), Ort ORDER BY Nr) As RN
FROM
Fotos
WHERE
Type = 'D'
)
SELECT
Nr,
Ort
FROM
cteOrderedData
WHERE
RN = 1
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jsc42

Do you really need something as complex as a CTE? Couldn't you simply do the following (off the top of my head, not tested; so caveat emptor):

SELECT TOP (100) PERCENT
Nbr,
Ort
FROM
Fotos
WHERE
Type = 'D'
ORDER BY
SUBSTRING(Nbr, 1, 8) DESC,
Ort

Richard Deeming

The problem is that the OP wants to extract the first record for each unique 8-character prefix, whereas your code will return all records for each prefix. :) For example, given the input data:

Nbr	Ort
20220717-001	A
20220717-002	B
20220717-003	C

Your code would return all three, whereas the OP only wants the first one.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

jsc42

Thanks. I hadn't realised that. OP's sample output has multiple 20220717 dates (minus the suffixes) in it, so I had read it as 'sort all by date + Ort, ignoring the sequence no after the date'/