Solved

Mysql query - PHP

Posted on 2013-10-30
5
393 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
5 Comments
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
>> 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
Comment Utility
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
Comment Utility
Would a GROUP BY (h11) do the trick? That should return unique values for that column.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 total points
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
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 count occurrences of each item in an array.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now