Databases
StackOverflow2013
Postsdbo
POselect distinct does not list first entry, select does, why?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POselect distinct does not list first entry, select does, why?
    primarykey
    data
    text
    <p>I have a strange issue with select distinct, when i use the following code:</p> <pre><code>SELECT T2.cited_pat_publn_id, T2.cited_publn_auth, T2.cited_publn_nr, T2.cited_publn_kind, T2.cited_appln_id, T2.cited_publn_date, T3.citing_pat_publn_id, T3.citing_publn_auth, T3.citing_publn_nr, T3.citing_publn_kind, T3.citing_appln_id, T3.citing_publn_date FROM (tls211_pat_publn T0, albert_my_ipc T1, albert_my_cited T2, albert_my_citing T3) WHERE T1.appln_id=T0.appln_id AND T2.cited_pat_publn_id=T0.pat_publn_id AND T3.citing_pat_publn_id=T2.citing_pat_publn_id AND TRIM(T0.publn_nr)="6174338" limit 1,5000 </code></pre> <p>i get all citing_pat_publn_ids i wanted...</p> <p>but when i use select distinct the first id is not listed...</p> <p>thank you for any suggestion!</p> <p>Result with only select(only posted here the first 4 rows..):</p> <pre><code>cited_pat_publn_id cited_publn_auth cited_publn_nr cited_publn_kind cited_appln_id cited_publn_date citing_pat_publn_id citing_publn_auth citing_publn_nr citing_publn_kind citing_appln_id citing_publn_date 62554677 US 6174338 B1 48980223 16.01.2001 59255528 US 8048557 B2 45960521 01.11.2011 62554677 US 6174338 B1 48980223 16.01.2001 59255528 US 8048557 B2 45960521 01.11.2011 62554677 US 6174338 B1 48980223 16.01.2001 59255528 US 8048557 B2 45960521 01.11.2011 62554677 US 6174338 B1 48980223 16.01.2001 59255528 US 8048557 B2 45960521 01.11.2011 </code></pre> <p>result with select distinct (only posted here the first 2rows, there are more):</p> <pre><code>cited_pat_publn_id cited_publn_auth cited_publn_nr cited_publn_kind cited_appln_id cited_publn_date citing_pat_publn_id citing_publn_auth citing_publn_nr citing_publn_kind citing_appln_id citing_publn_date 62554677 US 6174338 B1 48980223 16.01.2001 61120611 US 6844106 B2 47693584 18.01.2005 62554677 US 6174338 B1 48980223 16.01.2001 65427309 US 6517975 B1 51553339 11.02.2003 </code></pre> <p>there is no citing_pat_publn_id=45960521 </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1679802
    1. USuser1679802
    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. This table or related slice is empty.
    1. COcould you provide at least your fist row returned in the result set for both attempts? or better include your desired output as a whole.
      singulars
      1. POselect distinct does not list first entry, select does, why?
      1. USDrCopyPaste
    2. COhow can you define the first ID? you are using LIMIT but you are not using an ORDER BY
      singulars
      1. POselect distinct does not list first entry, select does, why?
      1. USfthiella
    3. COOh, thank you! that could it be :) I try it and let you know.
      singulars
      1. POselect distinct does not list first entry, select does, why?
      1. USuser1679802
 

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