question

SUN avatar image
SUN asked

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
AD, 1000, 5000, 10, 635
BW, 1100, 5010, 15, 200
AD, 1000, 5020, 10, 201
AD, 1000, 5030, 20, 350
BW, 1000, 5040, 10, 250
BW, 1100, 5040, 15, 50
AD, 1100, 5050, 15, 100

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?

t-sqljoinsaggregates
10 |1200

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

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered

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
10 |1200

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 ·
ThomasRushton avatar image
ThomasRushton answered

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
10 |1200

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 ·
cmapowers avatar image
cmapowers answered

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

10 |1200

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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