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

Question

question

SUN asked Jun 7, '10

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-sql joins aggregates

10 |1200

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

Answer 1 · 2010-06-07T08:03:17Z

Magnus Ahlkvist answered Jun 7, '10

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

10 |1200

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

ThomasRushton ♦♦ commented · Jun 11, 2010 at 11:57 AM

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 ·

Answer 2 · 2010-06-11T11:56:27Z

ThomasRushton answered Jun 11, '10

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

10 |1200

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

ThomasRushton ♦♦ commented · Jun 11, 2010 at 11:58 AM

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

0 ·

Answer 3 · 2010-06-14T18:07:33Z

cmapowers answered Jun 14, '10

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

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

question