SQL Select all first items in a list of rows identified by Ids and filtered by a specific Type in an-CodePudding

I need to create a table keyed by an ID where the values of one of the columns in the new table are the earliest values entered into the column of another table where the rows share the same ID and have a specific type label.

For example, say I want the Name and first Value entered for each fruit with an entry type A:

These are the tables I have:

TABLE1

|Key|ID|Name  |
|:--|:-:|-----:|
|1  |1  |Cherry|
|2  |2  |Grape |

TABLE2

|Key|ID|Value|EntryNum|EntryType|
|:--|:-:|:---:|:-----:|--------:|
|1  |1  |21   |1      |A        |
|2  |1  |32   |2      |B        |
|3  |1  |4    |3      |B        |
|4  |1  |15   |4      |A        |
|5  |2  |3    |1      |B        |
|6  |2  |8    |2      |A        |
|7  |2  |16   |3      |B        |

And this is the result that I want:

TABLE3

|ID|Name  |EarliestEntry|
|:-|:----:|------------:|
|1 |Cherry|21           |
|2 |Grape |8            |

I've attempted the following query but it just returns the same value for all EarliestEntry:

SELECT TABLE1.ID, TABLE2.Name,
   (SELECT Value FROM (SELECT ROW_NUMBER() OVER (ORDER BY TABLE2.EntryNum)
   as row_num, Value FROM TABLE2
   WHERE TABLE2.ID = TABLE1.ID AND TABLE2.EntryType = 'A')
   AS sub
   WHERE row_num = 1) AS EarliestEntry
INTO TABLE3
FROM TABLE2
INNER JOIN TABLE1 ON TABLE1.ID = TABLE2.ID
GROUP BY TABLE1.ID, TABLE2.Type, TABLE2.EntryNum

I would greatly appreciate help on this. Thank you

CodePudding user response：

If you wanted to use the ROW_NUMBER function then you would need to put that on TABLE1 and add a partition by like so:

WITH rn AS(
 SELECT a.Key, ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY a.EntryNum) AS rn
 FROM TABLE2 AS a
)
SELECT b.Name, a.Value AS EarliestValue
FROM TABLE2 AS a
INNER JOIN TABLE1 AS b ON b.ID = a.ID
INNER JOIN rn AS rn ON rn.key = a.key
WHERE rn.rn = 1

In your example you skipped the PARTITION BY clause so you just get a number for all values in TABLE2. Instead of a number per ID in ascending order for Value.

CodePudding user response：

Based on your description of the three tables TABLE1, TABLE2 and TABLE3.

I modified a little bit your script. Thank of Dale K remark, I explain in some words the solution : the field TABLE2.Name shown in the first select was wrong, because [name] belongs to TABLE1, so the right syntax for this is TABLE1.name. And in the GROUP BY clause the field TABLE2.Type might be replaced by TABLE1.name to repect aggregation criteria. So the script becomes :

 SELECT DISTINCT table1.id, table1.name,
  (SELECT Value FROM (SELECT ROW_NUMBER() OVER (ORDER BY table2.EntryNum)
   as row_num, Value FROM table2
   WHERE table2.id = table1.id AND table2.EntryType = 'A')
   AS sub
   WHERE row_num = 1) AS EarliestEntry
   INTO table3
FROM table2
INNER JOIN table1 ON table1.id = table2.id
GROUP BY table1.id, table1.name, table2.entrynum;

Here, you can verify the output with fiddle