Query to Group Like Values in One Column, Pick 1st Value in Other Columns Where Values Are Different

[zgadson]

Hello,

Could use some help. I have a scenario where I need to group by a column where values are the same, then other columns need to choose the 1st value.

Basically, I need to group by MAAISL and MAASLOT (which make up my Location column on my sheet), then for any duplicates it finds in that column, I need the ITDSC1 field to return what would be the 1st value. I have 99% of the code already, but the problem is that when I exclude ITDSC1 from the Group by element of my SQL, it returns an error.

Here's my code:

SELECT

CONCAT(CONCAT(LTRIM(RTRIM(MAAISL)), '_'),LTRIM(RTRIM(MASLOT))) AS LOCATION,


CEILING(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MASLOT, 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''))) AS "SLOT.NUM",

LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MASLOT, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', ''))  AS "SLOT.LVL",

LTRIM(MAAISL) AS "AISLE",
CEIL(LTRIM(MADEPT)) AS "SLOT.DEPTH",
CEILING(LCSTOR) AS "STR.NUM",
RTRIM(LTRIM(STNAME)) AS "STR.NAME",
LTRIM(ITDSC1) AS "ITM.DESC",
LTRIM(LCITEM) AS "ITM.CODE",
LTRIM(ITPDCD) AS "ITM.GRP",
CEILING(ITINDP) AS "ITM.DPTH",
CEILING(ITINWD) AS "ITM.WDTH",
CEILING(ITINHI) AS "ITM.HGHT",
MAX(ITPALH) AS "PLT.STK.HGHT",

SUM(CASE WHEN ITUNQ3 = ' ' THEN CEIL(QTY/ITUNQ2) ELSE CEIL((QTY/ITUNQ2)/ITUNQ3) END) AS "PLT.QTY"

FROM LOCXSTORER
WHERE MAWHSE = '  2'
GROUP BY MAWHSE, MAAISL, MASLOT, LCSTOR, STNAME, ITPDCD, MADEPT,ITINDP,ITINWD,ITINHI
ORDER BY MAAISL ASC

As always, any help would be greatly appreciated.

Thanks!
Zach

 

[zgadson]

Really wish there was an option to delete posts. Figured this out by using MAX to filter out fields I don't want to group by.