How to get the earliest record and how to covert wide table to a long table in SQL

[BusyBee123]

I have two tables. My first one looks like this:

id	District	DateSub	Data
1​	Kent	10/14/2022​	6​
2​	Kent	10/16/2022​	9​
3​	Kent	10/17/2022​	10​
4​	Olympia	10/18/20222	3​
5​	Olympia	10/16/2022​	5​
6​	Seattle	10/14/2022​	9​
7​	Seattle	10/15/2022​	7​

How do I make it look like this:

id	District	DateSub	Data
1​	Kent	10/14/2022​	6​
5​	Olympia	10/16/2022​	5​
6​	Seattle	10/14/2022​	9​

The second one looks like this:

id	Gender	IsBillingual	PeffLevel
1​	Male	Bill	L1
2​	Female	NotB	L2
3​	X	Bill	L3
4​	Male	NotB	L3
5​	Female	Bill	L4
6​	Female	NotB	L1

How can I make it look like this:

id	Group	PeffLevel
1​	Male	L1
1​	Bill	L1
2​	Female	L2
2​	NotB	L2
3​	X	L3

 

[smozgur]

Welcome to the MrExcel Message Board!

One way of doing this is using a sub-select query as shown below.
FirstTable is the table name that you need to change with yours.

SELECT FirstTable.*
FROM FirstTable
INNER JOIN
  (SELECT
     District, MIN(DateSub) AS minDateSub
   FROM
     FirstTable
   GROUP BY
    District) AS earliestRecord
ON FirstTable.District = earliestRecord.District
    AND FirstTable.DateSub = earliestRecord.minDateSub

Basically, we join the main table with itself as grouped by the minimum date value on the District and DateSub fields.

Please note that if you have the same date for the same District, and if it is the earliest date, then there will be two records listed for the same District.

Similar logic could be easily applied to the second table, however, I don't see a date field in your second data to get the "earliest" record. If you could be more specific about how you'd like to "filter" the second table, then we could adapt the same query accordingly.