# question

## How to use group by, union, order by in a main and subquires?

Please explain how to get the details from the following tables!

``````Table1
Loc, ItemCode, DayEnd, Line, TotQty
BW, 1100, 5010, 15, 200
BW, 1000, 5040, 10, 250
BW, 1100, 5040, 15, 50

Table2
ItemCode, Desc, Base
1000, XXXXXXXXXXXXXXXXX, 10
1100, YYYYYYYYYYYYYYYYY, 20
``````

here the output should be grouped by Loc, ItemCode and Order by DayEnd, and the TotQty from the last record if ordered by DayEnd+Line

``````the expected records should look like:
Loc, ItemCode, DayEnd, Line, TotQty, Desc, Base
AD, 1000, 5030, 20, 350, XXXXXXXXXXXXXXX, 10
AD, 1100, 5050, 15, 100, YYYYYYYYYYYYYYY, 20
BW, 1000, 5040, 10, 250, XXXXXXXXXXXXXXX, 10
BW, 1100, 5040, 15, 50, YYYYYYYYYYYYYYY, 20
``````

How to write the query?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

If I don't misunderstand your question, I'd say it's as easy as:

``````SELECT Loc, T1.ItemCode, Dayend, Line, TotQty, Desc, Base
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.ItemCode = T2.ItemCode
ORDER BY Loc, ItemCode
``````
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

By my reckoning, you missed out the filtering - SUN says "here the output should be grouped by Loc, ItemCode and Order by DayEnd, and the TotQty from the last record if ordered by DayEnd+Line"
0 Likes 0 ·

Here's the query you want. I'm sure it can be done better, but...

``````SELECT t1.loc,
t1.itemcode,
t1.dayend,
t1.line,
t1.totqty,
t2.descr,
t2.base
FROM   table1 t1
INNER JOIN table2 t2
ON t1.Itemcode = t2.ItemCode
AND t1.Dayend = (SELECT MAX(dayend)
FROM   table1 t3
WHERE  t3.Itemcode = t1.Itemcode
AND t3.Loc = t1.Loc)
ORDER  BY t1.Loc,
t1.Itemcode
``````
1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

hmm. doesn't take the "Line" bit into account, but still seems to work with the test data supplied.
0 Likes 0 ·

I rewrote previous SQL to a JOIN instead of a co-related sub-query and the result is what described. Something I learned.

SELECT t1.loc, t1.itemcode, t1.dayend, t1.line, t1.totqty, t2.descLIKE, t2.base FROM table1 t1 JOIN(SELECT ItemCode, Loc, MAX(dayend) mxdayend FROM table1 GROUP BY Loc, ItemCode) t3 ON(t1.Itemcode = t3.Itemcode) AND(t3.Loc = t1.Loc) JOIN table2 t2 ON t1.Itemcode = t2.ItemCode

WHERE t1.Dayend = mxdayend

ORDER BY t1.Loc, t1.Itemcode

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.