Databases
StackOverflow2013
Postsdbo
POSelect distinct ID's
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect distinct ID's
    primarykey
    data
    text
    <p>We would like an SQL statement that lists the number of times a unique IP/uniqueID pair has visited on any unique date ordered by the maximum numbers of times that the UniqueID/IP pair has visited.</p> <p>Here is the table structure:</p> <pre><code>Column Type ------------------------------ Date Timestamp NumberofUsers smallint ipaddress varchar(16) location varchar(2) Count bigint(20) </code></pre> <p>Here is the sql we have been trying:</p> <pre><code>SELECT LicenseID, MAX(Date) AS LatestAccess, COUNT(DISTINCT Location) AS DifferentCountries, COUNT(DISTINCT IPAddress) AS DistinctIPCount, COUNT(DISTINCT Date,IPAddress) AS DistinctDate FROM LicenseHistory WHERE (LicenseID&lt;&gt;30002) GROUP BY LicenseID ORDER BY DistinctDate DESC </code></pre> <p>Here is some sample date from the table in CSV format:</p> <pre><code>2009-10-08 10:37,30002,8,24.108.64.80,CA,2399 2009-05-27 16:57,24508,50,24.108.64.80,CA,645 2008-11-06 12:04,30,100,24.108.64.80,CA,282 2008-02-04 10:51,24508,30,24.69.19.207,CA,62 2009-10-08 14:52,13136,5,24.108.64.80,CA,285 2013-05-13 13:10,718,10,66.251.68.106,US,23860 2008-02-12 11:10,30002,8,24.69.19.207,CA,36 2008-04-09 17:49,18504,10,70.90.32.57,US,121 2007-07-26 13:38,30002,8,76.226.201.191,US,2 2009-12-03 22:35,30002,8,196.25.255.214,ZA,14 2013-05-13 6:49,20341,4,66.232.201.125,US,2676 2007-07-28 23:57,30002,8,75.81.107.238,US,1 2007-07-29 10:39,30002,8,70.63.54.162,US,1 2007-07-30 3:53,30002,8,121.210.199.31,AU,4 2007-07-30 5:11,30002,8,41.207.67.10,KE,2 </code></pre> <p>Here is some sample results (not correct yet, last column should not match second to last):</p> <pre><code>uniqueID LatestAccess DifferentCountries DistinctIPCount DistinctDate -------------------------------------------------------------------------------- 20677 2013-05-13 18:20:15 4 162 162 27749 2013-05-14 05:30:59 7 155 155 459 2013-05-13 11:12:47 2 143 143 24965 2013-05-14 13:44:56 6 123 123 25226 2013-05-06 16:11:56 3 104 104 20370 2013-05-14 05:54:04 4 100 100 </code></pre> <p>The problem I think is in the <code>"COUNT(DISTINCT Date,IPAddress) AS DistinctDate"</code> piece.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfthiella
    1. USuser2386810
    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. This table or related slice is empty.
    1. CO"Here is the table structure" ?
      singulars
      1. POSelect distinct ID's
      1. USDenys Séguret
 

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