Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You can use common table expressions [CTE's], indicated by <code>WITH</code>, to break the logic down into logical steps. First we'll build the summary rows, then we'll assign a ranking to the rows within each group, then pick out the ones that with the highest count of records.</p> <p>Let's say we want to know which flavor of each item sells the most frequently on each date (perhaps assuming a record is quantity one). </p> <pre><code>WITH s as ( SELECT itemID, saleDate, flavor, count(*) as tally FROM sales GROUP BY itemID, saleDate, flavor ), r as ( SELECT itemID, saleDate, flavor, tally, RANK() OVER (PARTITION BY itemID, saleDate ORDER BY tally desc) as pri FROM s ) SELECT itemID, saleDate, flavor, tally FROM r WHERE pri = 1 </code></pre> <p>Here the names "s" and "r" refer to the result set from their respective CTE's. These names can then be used as to represent a table in another part of the statement.</p> <p>The pri column will have the RANK() of tally value on the summary row from the first section "s" within the window of itemID and saleDate. Tally is descending, because we want the largest value first, which will get a RANK() of 1. Then in the main SELECT we simply pick those summary records which were first in their partition.</p> <p>By using RANK() or DENSE_RANK() we could get back multiple flavors for an itemID, saleDate, if they are tied for first place. This could be eliminated by replacing RANK() with ROW_NUMBER(), but it would arbitrarily pick one of the tied flavors as a winner, and this may not be correct answer for the problem at hand.</p> <p>If we had a sales quantity column in the table, we could replace COUNT(*) with SUM(salesqty) and find what had sold the most units.</p>
    singulars
    1. This table or related slice is empty.
    1. POCalculate Mode - "Highest frequency row" DB2
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USWarrenT
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCalculate Mode - "Highest frequency row" DB2
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COhey, Thanks for your reply, That kind of solves the issue..Is there a way to break the tie as well?? If there are two rows at rank 1??
      singulars
      1. PO
      1. USuser1521554
    2. COThanks..Got it working..Took the average of the result..
      singulars
      1. PO
      1. USuser1521554
    3. COWell breaking the tie might depend on the specifics of your situation. If you could describe your scenario, I *might* have some idea. The scenario I made up for the answer above is kinda lame :o)
      singulars
      1. PO
      1. USWarrenT
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload