Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You will find that the following query outperforms every other answer given, as it works with a single scan. This simulates MS Access's First and Last aggregate functions, which is basically what you are doing.</p> <p>Of course, you'll probably have foreign keys instead of names in your CandyPreference table. To answer your question, it is in fact very much best if Candy and Name are foreign keys into another table.</p> <p>If there are other columns in the CandyPreferences table, then having a covering index that includes the involved columns will yield even better performance. Making the columns as small as possible will increase the rows per page and again increase performance. If you are most often doing the query with a WHERE condition to restrict rows, then an index that covers the WHERE conditions becomes important.</p> <p>Peter was on the right track for this, but had some unneeded complexity.</p> <pre><code>CREATE TABLE #CandyPreference ( [Name] varchar(20), Candy varchar(30), PreferenceFactor decimal(11, 10) ) INSERT #CandyPreference VALUES ('Jim', 'Chocolate', 1.0) INSERT #CandyPreference VALUES ('Brad', 'Lemon Drop', .9) INSERT #CandyPreference VALUES ('Brad', 'Chocolate', .1) INSERT #CandyPreference VALUES ('Chris', 'Chocolate', .5) INSERT #CandyPreference VALUES ('Chris', 'Candy Cane', .5) SELECT [Name], Candy = Substring(PackedData, 13, 30), PreferenceFactor = Convert(decimal(11,10), Left(PackedData, 12)) FROM ( SELECT [Name], PackedData = Max(Convert(char(12), PreferenceFactor) + Candy) FROM CandyPreference GROUP BY [Name] ) X DROP TABLE #CandyPreference </code></pre> <p>I actually don't recommend this method unless performance is critical. The "canonical" way to do it is OrbMan's standard Max/GROUP BY derived table and then a join to it to get the selected row. Though, that method starts to become difficult when there are several columns that participate in the selection of the Max, and the final combination of selectors can be duplicated, that is, when there is no column to provide arbitrary uniqueness as in the case here where we use the name if the PreferenceFactor is the same.</p> <p>Edit: It's probably best to give some more usage notes to help improve clarity and to help people avoid problems.</p> <ul> <li>As a general rule of thumb, when trying to improve query performance, you can do a LOT of extra math if it will save you I/O. Saving an entire table seek or scan speeds up the query substantially, even with all the converts and substrings and so on.</li> <li>Due to precision and sorting issues, use of a floating point data type is probably a bad idea with this method. Though unless you are dealing with extremely large or small numbers, you shouldn't be using float in your database anyway.</li> <li>The best data types are those that are not packed and sort in the same order after conversion to binary or char. Datetime, smalldatetime, bigint, int, smallint, and tinyint all convert directly to binary and sort correctly because they are not packed. With binary, avoid left() and right(), use substring() to get the values reliably returned to their originals.</li> <li>I took advantage of Preference having only one digit in front of the decimal point in this query, allowing conversion straight to char since there is always at least a 0 before the decimal point. If more digits are possible, you would have to decimal-align the converted number so things sort correctly. Easiest might be to multiply your Preference rating so there is no decimal portion, convert to bigint, and then convert to binary(8). In general, conversion between numbers is faster than conversion between char and another data type, especially with date math.</li> <li>Watch out for nulls. If there are any, you must convert them to something and then back.</li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POEfficient SQL 2000 Query for Selecting Preferred Candy
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErikE
    1. USErikE
    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. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONice. I'm very impressed with the single scan. Can you suggest an improvement on my method that still supports an arbitrary ORDER BY?
      singulars
      1. PO
      1. USPeter Radocchia
    2. COThanks, Peter! A complex CASE expression that determines which column(s) to put in the packed value, plus the character positions and lengths to pull the columns back out, would do the trick (More CPU still cheaper than more I/O). If you're really interested in this, comment again and I'll try work something up for you. Or, can you send me a message by visiting my profile? Either way.
      singulars
      1. PO
      1. USErikE
    3. CONow I understand what you were getting at before, that the correlated subquery is unnecessary. At first I thought it was a criticism of how I packed and unpacked the values. Yes, I'd be interested in seeing how you would pack more values. The general form, as it were. And no, I don't know how to send a private message on SO. I don't see an option for that on the profile page, and I don't see any contact information.
      singulars
      1. PO
      1. USPeter Radocchia
 

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