0

I have a query i thought was working until i added more values into the equation This has resulted in the rows in the results not matching the actual data.

Here is the Data set i am querying:

enter image description here

My Query:

SELECT c.Company_Name, c.Branch_Name, Till_No, Database_Build_Date, Database_Updated_Date, Touchkeys_Build_Date, Touchkeys_Updated_Date, MAX(DateTime)
FROM `TillDatabase` 
INNER JOIN Customers c USING(ClientID, Branch)
WHERE `ClientID` =  1 AND `Branch` = 1
Group by Till_No
Order by Till_No Asc;

My Results: enter image description here

With the group by and using Max date, i would of expected only Till 1, 2 and 3 To show using the Latest Date

What have i failed to acknowledge or am i going about the query the wrong way?

Thanks

(Sorry I tried to format for table as it shows but i have no idea how to do it as it came out so badly formatted)

Dave Hamilton
  • 675
  • 1
  • 12
  • 23
  • Columns not listed in the `GROUP BY` have no defined value - MySQL is free to pick _any_ of the matching rows in each group. Other databases, or MySQL in strict mode, would simply reject the query as invalid. See [this excellent illustration](https://stackoverflow.com/a/38551525/157957). – IMSoP Oct 21 '21 at 16:02
  • Would the group by not attempt to group the dates by a distinct value? As all the dates should be different. – Dave Hamilton Oct 21 '21 at 17:02
  • It will only group by things you've told it to group by, which in this case is `Till_No` only. I'm not actually clear what results you're _expecting_ to see, could you [edit] the question to add them? You could also put the table and current result as text using [table syntax](https://stackoverflow.com/editing-help#tables) so that they're easier to read, and easier to copy into answers. – IMSoP Oct 21 '21 at 17:20
  • I'll try, but every time I tried it wasn't accepting the format. The results I was after was till_no being unique and showing the results of the latest datetime so in this case I would only see Till 1, 2 and 3 showing the results with the most recent datetime stamp (or ID) as technically speaking the latest entry would be based on the highest ID for the Till_No – Dave Hamilton Oct 21 '21 at 18:03
  • In that case, you'll need something more complicated than a Group By I'm afraid. Have a look at this: [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) and browse the questions tagged [tag:greatest-n-per-group] – IMSoP Oct 21 '21 at 19:08

0 Answers0