Databases
StackOverflow2013
Postsdbo
POOrder query results by...?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POOrder query results by...?
    primarykey
    data
    text
    <p>First up, I should say that I'm finding it very hard to express the following problem in a few short words, hence the awful question title. Suggestions to improve are very welcome. </p> <p>Now to the real question...</p> <p>Given the following sample, unordered data for customers and invoices...</p> <p><strong>Edit</strong> I've created a <a href="http://sqlfiddle.com/#!3/d2642" rel="nofollow">SQL Fiddle</a> for the following</p> <p><strong>Customer data</strong></p> <pre><code>customer_id name ------------------ 1 Gary 2 Jeremy 3 Marcia 4 Danielle </code></pre> <p><strong>Invoice data</strong></p> <pre><code>invoice_id customer_id created_date amount ------------------------------------------------ 1 1 2008-01-01 500.00 2 1 2011-01-01 600.00 3 1 2012-01-01 100.00 4 1 2012-01-01 550.00 5 2 2008-01-01 600.00 6 2 2012-01-01 200.00 7 2 2013-01-01 1000.00 8 3 2012-01-01 300.00 9 3 2013-01-01 100.00 10 3 2009-01-01 250.00 11 4 2010-01-01 300.00 12 4 2011-01-01 700.00 13 4 2012-01-01 500.00 </code></pre> <p>...how could a query be written to return the data in the following way...</p> <ol> <li>The oldest invoice in the first row. If more than 1 invoice of the same age, then, of those invoices with the same age, the invoice of the greatest amount. If more than 1 invoice of the same age and amount, sorting becomes irrelevant.</li> <li>The next oldest invoice <em>for the same customer as the previous row</em>, in the second row. Again, if more than 1 invoice of the same age, the invoice of the greatest amount. If more than 1 invoice of the same age and amount, sorting becomes irrelevant.</li> <li>Repeating #2 until there are no more invoices for that customer.</li> <li>The next oldest invoice for a different customer. If more than 1 invoice of the same, then, of those invoice with the same age, the invoice of the greatest amount. If more than 1 invoice of the same age and amount, sorting becomes irrelevant.</li> <li>Repeat #2 for the same customer as #4.</li> <li>Repeating #5 until there are no more invoices for that customer.</li> <li>Repeat #4, #5, #6</li> </ol> <p>So, for the sample data above, the desired results would be...</p> <pre><code>customer_name invoice_id created_date amount -------------------------------------------------- Jeremy 5 2008-01-01 600.00 &lt;-- this is the joint "oldest" invoice with id 1 but has a greater amount. Jeremy 6 2012-01-01 200.00 &lt;-- this is the next "oldest" invoice for the same customer as the previous row. Jeremy 7 2013-01-01 1000.00 Gary 1 2008-01-01 500.00 &lt;-- no more invoice for previous customer, so this is the next "oldest" invoice for a new customer Gary 2 2011-01-01 600.00 Gary 4 2012-01-01 550.00 &lt;-- same age as inv_id 3 but larger amount Gary 3 2012-01-01 100.00 Marcia 10 2009-01-01 250.00 Marcia 8 2012-01-01 300.00 Marcia 9 2013-01-01 100.00 Danielle 11 2010-01-01 300.00 Danielle 12 2011-01-01 700.00 Danielle 13 2012-01-01 500.00 </code></pre> <p>To give the wider context for this question, the results will be used to chase payment for invoices, oldest and most "expensive" being the highest priority but then also to see all invoices for a customer grouped together.</p> <p>P.S. I'm working with MS SQL Server 2008.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSmallest Druid
    1. USSmallest Druid
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POOrder query results by...?
      1. USJohnHC
      1. VTFavorite
    2. VO
      singulars
      1. POOrder query results by...?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI forgot customer. I fixed my query. Customers are sorted by name.
      singulars
      1. POOrder query results by...?
      1. USGrzegorz W
    2. COI updated my answer. I didn't know sqlfiddle.com. It's awesome :). Thx for sharing.
      singulars
      1. POOrder query results by...?
      1. USGrzegorz W
 

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