OK, earlier I was trying to use a query that I would normaly use in SQL to grab the lowest valued 'OP' for each 'Facility Code'/'Order Number' pairing. This is contained in the bottom half of the union query shown below.
Dim Query As String = "SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
"[Mfg Due Date],[Planner],[Standard efficiency],[TBC ],[Std run labor time],[Std mach time],[Std setup hrs] " & _
"From [" & sheetName & "] WHERE [Facility Code] LIKE 'R%' " & _
"UNION " & _
"SELECT m.[Facility Code],m.[Mach],m.[Part Number],m.[Part Name],m.[Order No#],m.[OP],m.[Status],m.[Order Qty],m.[Qty Comp]," & _
"m.[Mfg Due Date],m.[Planner],m.[Standard efficiency],m.[TBC ],m.[Std run labor time],m.[Std mach time],m.[Std setup hrs] " & _
"FROM " & _
"(SELECT s.[Facility Code],s.[Mach],s.[Part Number],s.[Part Name],s.[Order No#],s.[OP],s.[Status],s.[Order Qty],s.[Qty Comp]," & _
"s.[Mfg Due Date],s.[Planner],s.[Standard efficiency],s.[TBC],s.[Std run labor time],s.[Std mach time],s.[Std setup hrs], " & _
"row_number() over(partition by s.[Facility Code],s.[Order No#] ORDER BY s.[OP] asc) as rn " & _
"FROM [" & sheetName & "] s " & _
"WHERE s.[Facility Code] LIKE 'J%' ) m " & _
"WHERE m.rn =1 "
My problem is that this is reading from an Excel spreadsheet and fails. After going some googling I believe that the row_number() command is not compatible.
I need to accomplish the same output without it, I tried to group the records, but this query fails as well. I get an error due to the fields that are not in the 'group by' not being an aggregate function.
Dim Query As String = "SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
"[Mfg Due Date],[Planner],[Standard efficiency],[TBC],[Std run labor time],[Std mach time],[Std setup hrs] " & _
"From [" & sheetName & "] WHERE [Facility Code] LIKE 'R%' " & _
"UNION " & _
"SELECT [Facility Code],[Mach],[Part Number],[Part Name],[Order No#],[OP],[Status],[Order Qty],[Qty Comp]," & _
"[Mfg Due Date],[Planner],[Standard efficiency],[TBC],[Std run labor time],[Std mach time],[Std setup hrs] " & _
"From [" & sheetName & "] WHERE [Facility Code] LIKE 'J%' " & _
"GROUP BY [Facility Code],[Order No#] ORDER BY [Facility Code],[Order No#],[OP] asc"
Any help would be apreciated, thanks in advance!
If you want to use a field, that is not in the GROUP BY, you have to use the Function FIRST.
SELECT FIRST(a) FROM tbl GROUP BY b;
Of course, if you have different values of a inside this, it will take only the first one. Throwing away all others. I don't know the data, therefore I don't know if this is an issue.