select columns if happened on same date


I am trying to craft a query that will select multiple columns (uniqueID, IPaddress, date, country) but I would like the query only to select those records where the same IPaddress and UniqueID showed up on a given day. I have been trying ...

SELECT uniqueID AS uniqueID, IPAddress AS IPAddr, Date AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE (uniqueID= 20677) GROUP BY Date ORDER BY DDate DESC, IPAddr DESC

But it does eliminate records where only one Ip/UniqueID showed up on one day. I really only want to find records where the <strong>uniqueID showed up on the same day with different IPaddresses</strong>.


Not sure if I follow exactly, but sounds like you want to join a subquery:

SELECT a.* FROM History a JOIN ( SELECT uniqueID, Date, COUNT(DISTINCT IPAddress) FROM History GROUP BY uniqueID,Date HAVING COUNT(DISTINCT IPAddress) > 1 )b ON a.uniqueID = b.uniqueID AND a.date = b.date

EDIT: misread your with different IP addresses part, updated.

The subquery will show you all the records that have multiple ip addresses for the same uniqueID/date, then joining that to your history table shows you all the fields for the records you're after.


You need to group by three fields, if you are looking for duplicates amongst them:

SELECT uniqueID, IPAddress, "Date" AS DDate, Location AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;

You dn't really need the uniqueid in the group by clause because you are selecting only one. However, I think it makes the intention of the query clearer.

As written, this will either generate an error (because location is not in the aggregation clause) or return a random value for location, if you are using MySQL). If the latter, try:

SELECT uniqueID, IPAddress, "Date" AS DDate, group_concat(Location) AS Countries, COUNT(IPAddress) AS IPCount FROM History WHERE uniqueID = 20677 GROUP BY uniqueId, IPAddress, "Date" having IPCount > 1;

Also, naming columns after data types and functions is not good idea. In many databases, date is both a data type and a function. Some other name, like DateAdded avoids this conflict.


  • What are the reordering columns on laravel-backpack?
  • Android Permission Denial: broadcasting Intent
  • Functional Programming in Swit to distribute array elements to correct “buckets”
  • SQL SELECT ORDER BY multiple columns depending on value of other column
  • How to resolve designated initialization error for UITableViewController?
  • Python list of String to SQL IN parameter
  • format JSON value with a php function and print result inside a div#id
  • Get last call details from Call Log
  • MySQL select genres issue (php)
  • Should I create an object or work with an array?
  • NullPointerException for lwuit.html.CSSEngine.applyStyleToUIElement(Unknown Source)
  • In explicit LINQ-to-SQL (C#) does order matter?
  • Using a join with three tables when a field might be null
  • Bokeh custom ToolTips {safe} tag displays nothing
  • How to translate SQL queries to cypher in the optimal way?
  • Sequelize mssql: order by primary key and limit
  • Sorting table with just jquery, no other plugins
  • Google OAuth2 for an web application hosted behind NAT (intranet server without public IP)
  • inserting items in list in mongodb document
  • How to resolve docker host names (/etc/hosts) in containers
  • Should I be afraid to use UDP to make a client/server broadcast talk?
  • Oracle - Second level subquery cannot see field from main query
  • Best practice to eliminate magic numbers within a member function
  • How to skip require in ruby?
  • C++ Single function pointer for all template instances
  • Most efficient way to move table rows from one table to another
  • Can I have a variable number of URI parameters or key-value pairs in Laravel 4?
  • Add dynamic data to line chart from mysql database with highcharts
  • Using Sax parsing to edit and write XML in VB6
  • Test if a set exists before trying to drop it
  • Cannot resolve symbol 'MyApi'
  • Django: Count of Group Elements
  • Using $this when not in object context
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • How can I remove ASP.NET Designer.cs files?
  • python draw pie shapes with colour filled
  • Is there any way to bind data to data.frame by some index?
  • How can i traverse a binary tree from right to left in java?