StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POdate Subquery PHP and Mysql
primarykey
Id
19865067
data
AcceptedAnswerId
19865213
AnswerCount
2
ClosedDate
CommentCount
4
CommunityOwnedDate
CreationDate
2013-11-08T17:28:44.087
FavoriteCount
0
LastActivityDate
2013-11-08T18:44:08.697
LastEditDate
2013-11-08T18:44:08.697
LastEditorUserId
1458648
OwnerUserId
1796640
ParentId
0
PostTypeId
1
Score
0
ViewCount
98
LastEditorDisplayName
text
Body
Following is my sql query with included subquery. Essentially I'm looking to obtain the most recent meter readings for our equipment for a given month and year. I know it's in my subquery where the issue is coming up but I don't know how to properly fix it. Following is result if I delete the subquery and just select equipment with meter readings for that month. <pre><code>eid eqid name pid hours date 70 C1 KOMATSU WA250 3YD BUCKET 27 1176 2013-10-07 70 C1 KOMATSU WA250 3YD BUCKET 27 1195 2013-10-28 70 C1 KOMATSU WA250 3YD BUCKET 27 1178 2013-10-14 73 C11 KOMATSU PC200 EXCAVATOR 27 1080 2013-10-14 73 C11 KOMATSU PC200 EXCAVATOR 27 1099 2013-10-28 73 C11 KOMATSU PC200 EXCAVATOR 27 1078 2013-10-07 92 C4 CATERPILLAR 304D MINI EX 27 646 2013-10-14 92 C4 CATERPILLAR 304D MINI EX 27 645 2013-10-07 92 C4 CATERPILLAR 304D MINI EX 27 649 2013-10-28 58 E14 BOBCAT-ATV 2300 Utility 8 522 2013-10-31 61 E17 SKYTRAK FORKLIFT 40 943 2013-10-10 62 E18 SKYTRAK FORKLIFT 5 1790 2013-10-30 62 E18 SKYTRAK FORKLIFT 5 1789 2013-10-29 62 E18 SKYTRAK FORKLIFT 5 1777 2013-10-13 62 E18 SKYTRAK FORKLIFT 5 1772 2013-10-07 62 E18 SKYTRAK FORKLIFT 5 1777 2013-10-13 62 E18 SKYTRAK FORKLIFT 5 1772 2013-10-04 62 E18 SKYTRAK FORKLIFT 5 1772 2013-10-07 62 E18 SKYTRAK FORKLIFT 5 1772 2013-10-04 67 E23 BOBCAT SKID STEER 27 1178 2013-10-28 </code></pre> Following is result of full query including subquery. <pre><code>92 C4 CATERPILLAR 304D MINI EX 27 649 2013-10-28 61 E17 SKYTRAK FORKLIFT 40 943 2013-10-10 62 E18 SKYTRAK FORKLIFT 5 1790 2013-10-30 </code></pre> Following is query I am using. <pre><code>SELECT e.eid, e.eqid, e.name, m.pid, m.hours, m.date FROM meter m JOIN equipment e ON m.eid = e.eid WHERE MONTH(date) = $month AND YEAR(date) = $year AND m.date = (SELECT MAX(m2.date) FROM meter m2 WHERE m2.eid = m.eid) ORDER BY e.eqid ASC </code></pre> Any help is greatly appreciated. EDIT*** I would have never gotten there Sebas. I had to change one thing and it worked perfectly. <pre><code>SELECT DISTINCT e.eid, e.eqid, e.name, m.pid, m.hours, m.date FROM equipment e JOIN ( SELECT eid, MAX(date) as date FROM meter WHERE MONTH(date) = $month AND YEAR(date) = $year GROUP BY eid ) maxdate ON maxdate.eid = e.eid JOIN meter m ON m.eid = e.eid AND m.date = maxdate.date ORDER BY e.eqid ASC </code></pre>
Tags
<mysql><sql><subquery>
Title
date Subquery PHP and Mysql
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. US웃웃웃웃웃
UserOwnerUserId
1. UShatchet145
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

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.