Solved

Mysql query - PHP

Posted on 2013-10-30
5
406 Views
Last Modified: 2013-10-30
Here is my data:
---------------------------------------
2013-10-30 06:04:31      545      55      130.5      131.2      28.6      226      23.3      123.7      122.3      50739      1.3405      5      5      
2013-10-30 06:11:07      396      55      148.6      150.2      36.2      226      19.5      133.6      135.7      50740      1.9535      5      5      
2013-10-30 06:17:10      363      55      158.2      159.3      45.3      226      22.3      129.4      132.8      50740      2.065      5      5      
2013-10-30 06:18:06      56      55      159.7      161.9      46.8      226      20.3      141.7      142.7      50741      2.066      3      1      
2013-10-30 06:21:15      189      55      165.9      168.1      52.8      226      22.5      124.2      129.2      50741      2.071      3      1      
2013-10-30 07:34:25      57      55      191.1      192.4      109.3      227      20.3      152.2      156.1      50742      1.526      5      5      
2013-10-30 07:35:11      56      55      191.8      193.4      109.7      226      22.5      169.1      161.7      50743      1.553      5      5
--------------------------------------------------------------------

Say all my headers are h1, h2, h3, etc for above.

In my column ( h11 ) where i have duplicate numbers like '50741' twice, i only want to return the result once instead of twice.

Here is my query:
$query = "SELECT * FROM datalog WHERE DATE(hes_datetime) = '$hes_date' ORDER by hes_datetime ASC";

This query works but I dont want to count h11 twice if it is same number.

I have tried distinct but cannot seem to get it to work.
0
Comment
Question by:keith1001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39612708
>> In my column ( h11 ) where i have duplicate numbers like '50741' twice, i only want to return the result once instead of twice.
whcih one you want to return? first one or second one?

also what are the primary key columns of your table?
0
 

Author Comment

by:keith1001
ID: 39612732
I want the first one returned.

Sorry but i have id (h0) which is primary key .
0
 
LVL 9

Accepted Solution

by:
Derek Jensen earned 350 total points
ID: 39612745
Would a GROUP BY (h11) do the trick? That should return unique values for that column.
0
 
LVL 110

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
ID: 39612866
I think bigdogman may be right.  The query would look something like this:

$query = "SELECT * FROM datalog WHERE DATE(hes_datetime) = '$hes_date' GROUP BY h11 ORDER by hes_datetime";
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39612896
try this query
$query = "SELECT t1.* FROM datalog t1 INNER JOIN (SELECT h11,MAX(h1) AS max_h1 FROM datalog GROUP BY h11) t2 ON t1.h11 = t2.h11 AND t1.h1 = t2.max_h1 WHERE DATE(hes_datetime) = '$hes_date' ORDER by hes_datetime ASC";

Open in new window

I assume h1 is the date column in your table. Otherwise change the column name with date column name.
0

Featured Post

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question