Databases
StackOverflow2013
Postsdbo
POSQL query to return one single record for each unique value in a column
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query to return one single record for each unique value in a column
    primarykey
    data
    text
    <p>I have a table in SQL Server 2000 that I am trying to query in a specific way. The best way to show this is with example data.</p> <p>Behold, <code>[Addresses]</code>:</p> <pre><code>Name Street City State -------------------------------------------------------- Bob 123 Fake Street Peoria IL Bob 234 Other Street Fargo ND Jim 345 Main Street St Louis MO </code></pre> <p>This is actually a simplified example of the structure of the actual table. The structure of the table is completely beyond my control. I need a query that will return a single address per name. It doesn't matter which address, just that there is only one. The result could be this:</p> <pre><code>Name Street City State -------------------------------------------------------- Bob 123 Fake Street Peoria IL Jim 345 Main Street St Louis MO </code></pre> <p>I found a similar question <a href="https://stackoverflow.com/questions/715804/query-to-return-1-instance-of-a-record-with-duplicates">here</a>, but none of the solutions given work in my case because I do not have access to <code>CROSS APPLY</code>, and calling <code>MIN()</code> on each column will mix different addresses together, and although I don't care which record is returned, it must be one intact row, not a mix of different rows.</p> <p>Recommendations to change the table structure will not help me. I agree that this table is terrible, (it's worse than shown here) but this is part of a major ERP database that I can not change.</p> <p>There are about 3000 records in this table. There is no primary key.</p> <p>Any ideas?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USrecursive
    plurals
    1. PL
      singulars
      1. LTLinked
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you give an idea how many records in your table? I have some ideas on doing this but may not be very fast if there are thousands / millions of records.
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. USRobin Day
    2. CODo you have any primary keys on this table?
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. USOrion Adrian
    3. CO~3000 records, and no PK, amazingly enough. I added this info to the question.
      singulars
      1. POSQL query to return one single record for each unique value in a column
      1. USrecursive
 

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