Yerka: SamSagaZ: You need explicit data that allows you to represent the concept of “newest” or “age”.
Baetz: SamSagaZ: Without that, you can’t have logic involving age or newest.
Pindell: Trying to fill the sqlfille
Crew: Http://sqlfiddle.com/#!9/173e6/3
Sleger: Btw, very nice site sqlfiddle.com!
Trepagnier: Reading roupwise max now
Garhart: I have an issue with my SQL.
Feinen: And I’m not really sure how to explain it but I’ll do my best.
Paxton: I have pages on my CMS which I’ve migrated to a different spot using the same CMS. However now my file’s fid don’t matchup with the nodeid :/
Latronica: Well – does your CMS has a migration plan or do***entation how to migrate properly?
Tamimi: Fid starts at 1 on the new imported site
Mihalko: It does heavily rely on how the CMS works and how it treats sites and pages
Mandonado: Fid starts at 303 on the old site
Vukcevic: This has nothing to do with the database management system, this is purely an CMS issue
Boardman: Rince, the do***entation is there scattered around with no real explanation but I think it could be solved with a simple MySQL comment.
Scibilia: How can I reset my “fid” column to start at 303 and auto increment up to the last entry?
Hassinger: But with that you would destroy every dependency the CMS has created. Links to other sites wouldn’t work any more. Are you sure you want to do this? really sure?
Brelje: Rince, I thank you for your concern. The other site is a mirror image, however the images had to be seperatly imported so the fid fields don’t match the “AI”
Wohlert: MrJW: If you are expecting auto_increment values to be sequential, that’s a mistake.
Liebl: How can I convert a datetime to timestamp?
Liebl: Or alternatively, how to do comparison between ‘2015-08-02T21:00:00.659Z’ and datetime?
Liebl: Select dt from foo where dt $1
Liebl: Incorrect datetime value: ‘2015-09-03T20:59:59.659Z’ for column dt at row 1
Tio: Liebl, you can use STR_TO_DATE
Dellis: Btw using GROUP BY t1.productId looks good ok too
Tio: SamSagaZ, depends on what you need – select distinct might be more “readable” but can be slower than group by
Liebl: Tio: I don’t want dates, I want datetimes
Fiechter: Liebl: See http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date
Tio: Liebl, bad naming only, output is datetime
Tio: SamSagaZ, do you just want rows from t1 where there exists “some” row in t2 ?
Liebl: No luck because of the milliseconds and Z in the end
Asselta: Because in t2 i just track the stock, and will be updated once/day
Mierzejewski: SamSagaZ: You said you want the latest item in each group.
Ulland: SamSagaZ: Using GROUP BY alone is not correct.
Tio: SamSagaZ, then us inner join, no left that would return t1 rows without a match too and the group by – probably the simplest
Tio: Liebl, yes, you have to put in proper format string
Owston: SamSagaZ: See groupwise max. Ask questions about that if you don’t understand something.
Liebl: Tio: how do I format it for milliseconds? Only microseconds listed in the docs
Liebl: Tried ‘%Y-%m-%dT%h:%i:%s.%fZ’
Tio: Liebl, SELECT STR_TO_DATE ‘2015-08-02T21:00:00.659Z’, ‘%Y-%m-%dT%H:%i:%s.%fZ’ ;
Tio: Liebl, 12/24h that would be a nasty bug if your “example” time were 13h
Tio: SamSagaZ, you seemed satisfied with the group by and did select only from t1 in the fiddle, that would work, but if in real case you need select from t2 too then group by is not suited for that – you have to know, we can only guess from what you “give” us :
Forster: Later will need info from t2 too