When you have multiple subfields for a particular marc field, you can specify that they appear in the same order as they do in the marc record. (Without specifying an order, you will get alphabetical order.) You can do this by including the "srs_marctab.line" field in your query, and using it in an "order by" statement within a string_agg function.
The example below gets the subject headings for a set of instance hrids. The subfields are specified (a, x, y and z), and the order of the subject headings is specified by the "order by" statement within the string_agg function.
When there are multiples of a given marc field (such as the 650 field), you can specify that the subject headings should come from just the FIRST occurrence of that field. Do this by specifying "sm.ord = '1'" in the WHERE criteria in the "lc" subquery.
WITH lc AS
(SELECT DISTINCT
sm.instance_hrid,
sm.field,
sm.sf,
sm.line,
sm.content
FROM srs_marctab sm
WHERE sm.instance_hrid BETWEEN '6403921' AND '6404000'
AND sm.field LIKE '6%%'
AND sm.sf IN ('a','x','y','z')
AND sm.ind2 = '0'
AND sm.ord = '1'
ORDER BY sm.instance_hrid, sm.sf, sm.line
)
SELECT
lc.instance_hrid,
lc.field,
STRING_AGG (lc.content, ' -- ' order by lc.instance_hrid, lc.sf, lc.line) AS lc_subject
FROM lc
WHERE lc.instance_hrid::INTEGER >= 6403921 AND lc.instance_hrid::INTEGER <= 6404000
GROUP BY
lc.instance_hrid,
lc.field
ORDER BY lc.instance_hrid::INTEGER
;
Result example:
