Hi folks,
Here is a result of a union query
Country City Region User Designation Status Date Trainer_Name Training_Date Supervisor_Name Promotion _Date
USA ABC 1b MRTX Manager Apr1,2021 ARB Jan 1 ,2021
USA ABC 1B MRTX Manager Apr1, 2021 DDD Feb1 , 2021
Expected Result set is:
Country City Region User Designation Status Date Trainer_Name Training_Date Supervisor_Name Promotion _Date
USA ABC 1b MRTX Manager Apr1,2021 ARB Jan 1 ,2021 DDD Feb1 , 2021
Using Aggregate function Maximum does not work on Trainer Name , Trainer Date , Supervisor Name and Promotion Date columns.
Result is still 2 rows instead of one.
Thanks in advance!
Quote from: actcognosuser on 18 Feb 2022 11:23:57 AM
Hi folks,
Here is a result of a union query
Country City Region User Designation Status Date Trainer_Name Training_Date Supervisor_Name Promotion _Date
USA ABC 1b MRTX Manager Apr1,2021 ARB Jan 1 ,2021
USA ABC 1B MRTX Manager Apr1, 2021 DDD Feb1 , 2021
Expected Result set is:
Country City Region User Designation Status Date Trainer_Name Training_Date Supervisor_Name Promotion _Date
USA ABC 1b MRTX Manager Apr1,2021 ARB Jan 1 ,2021 DDD Feb1 , 2021
Using Aggregate function Maximum does not work on Trainer Name , Trainer Date , Supervisor Name and Promotion Date columns.
Result is still 2 rows instead of one.
Thanks in advance!
Hi,
Are there typos in the data you posted? I notice Region is 1b in the first row and 1B in the second row? The values in Status Date appear slightly different too? If the values in the first six items are not all identical, you won't get a single grouped row. Also check that there are no leading/trailing spaces in the values that might not be visible.
Cheers!
MF.
Hi MF,
Sorry that was a typo. The first six fields are identical in both rows.
Quote from: actcognosuser on 20 Feb 2022 03:16:06 PM
Hi MF,
Sorry that was a typo. The first six fields are identical in both rows.
Hi,
Provided all six of the first items are identical, using Detail Aggregation of Maximum on the other four should produce the results you are looking for. If it isn't, there's something else going on. Have you checked there are no leading/trailing spaces in the first six items in either row? Are these 10 items the only items in the query?
Cheers!
MF.
Same question about the dates: Are they the same on both? But those were clearly not pasted in. The date formats there are not something I've seen.
What are the results from each of the queries that are the sources for the UNION?
View the generated SQL, copy and paste it into a query tool (like SSMS for MS SQL Server), and figure out what's going on.
...or are those date columns not a date data type? maybe a character type?