Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If you simply don't care what <code>col2</code> value is returned </p> <pre><code>select a.Col1,MAX(b.Col2) AS Col2 from a inner join b on b.Col4=a.Col3 GROUP BY a.Col1 </code></pre> <p>If you do want a random value you could use the approach below.</p> <pre><code> ;WITH T AS (SELECT a.Col1, b.Col2 ROW_NUMBER() OVER (PARTITION BY a.Col1 ORDER BY (SELECT NEWID()) ) AS RN FROM a INNER JOIN b ON b.Col4 = a.Col3) SELECT Col1, Col2 FROM T WHERE RN = 1 </code></pre> <p>Or alternatively use a CLR Aggregate function. This approach has the advantage that it eliminates the requirement to sort by <code>partition, newid()</code> an example implementation is below.</p> <pre><code>using System; using System.Data.SqlTypes; using System.IO; using System.Security.Cryptography; using Microsoft.SqlServer.Server; [Serializable] [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)] public struct Random : IBinarySerialize { private MaxSoFar _maxSoFar; public void Init() { } public void Accumulate(SqlString value) { int rnd = GetRandom(); if (!_maxSoFar.Initialised || (rnd &gt; _maxSoFar.Rand)) _maxSoFar = new MaxSoFar(value, rnd) {Rand = rnd, Value = value}; } public void Merge(Random group) { if (_maxSoFar.Rand &gt; group._maxSoFar.Rand) { _maxSoFar = group._maxSoFar; } } private static int GetRandom() { var buffer = new byte[4]; new RNGCryptoServiceProvider().GetBytes(buffer); return BitConverter.ToInt32(buffer, 0); } public SqlString Terminate() { return _maxSoFar.Value; } #region Nested type: MaxSoFar private struct MaxSoFar { private SqlString _value; public MaxSoFar(SqlString value, int rand) : this() { Value = value; Rand = rand; Initialised = true; } public SqlString Value { get { return _value; } set { _value = value; IsNull = value.IsNull; } } public int Rand { get; set; } public bool Initialised { get; set; } public bool IsNull { get; set; } } #endregion #region IBinarySerialize Members public void Read(BinaryReader r) { _maxSoFar.Rand = r.ReadInt32(); _maxSoFar.Initialised = r.ReadBoolean(); _maxSoFar.IsNull = r.ReadBoolean(); if (_maxSoFar.Initialised &amp;&amp; !_maxSoFar.IsNull) _maxSoFar.Value = r.ReadString(); } public void Write(BinaryWriter w) { w.Write(_maxSoFar.Rand); w.Write(_maxSoFar.Initialised); w.Write(_maxSoFar.IsNull); if (!_maxSoFar.IsNull) w.Write(_maxSoFar.Value.Value); } #endregion } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POselecting a distinct combination of 2 columns in SQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMartin Smith
    1. USMartin Smith
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POselecting a distinct combination of 2 columns in SQL
      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. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthe second value is not a number then??? max does not work!!
      singulars
      1. PO
      1. USAsha B
    2. CO@Asha - `Max` works on strings. It gives you the alphabetically last. What datatype is your column?
      singulars
      1. PO
      1. USMartin Smith
    3. COyup. I am aware that max works but then 1 a 1 b 1 z and 2 a 2 b 2 z will return 1 z and 2 z right
      singulars
      1. PO
      1. USAsha B
 

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