0

I'm working on outputting values from data base in the table. My table has 5 columns: Date, FirstName, LastName, City, State. Here is example of my data base table: 

DateMeeting  FirstName LastName   City     State
2015-12-11    Mike     Johns     Dallas    TX
2015-12-11    John     Cook      Dallas    TX
2015-12-11    Nick     Roberts   Dallas    TX
2015-12-11    Oliver   Ryan      New York  NY
2015-12-11    Michael  Best      New York  NY
2015-12-11    David    Holmes    New York  NY

So I want to have output table that will display just one date for multiple records. I tried to use DISTINCT on the date and that works fine but if I include my WHERE clause for City and State my query breaks. Also I tried to use GROUP BY but same problem, I can get Date values only once as long as I do not include other columns. In this case I need all columns but my Date value only once. Here is my query that I use:

Select Distinct(DateMeeting),FirstName, LastName, City, State
From Customers
Where City = 'Dallas'
and State = 'TX'

This query does not work with all columns that I have in my select, only if I run DISTINCT(DateMeeting). I would like to output my values in the table to look like this: 

Date       First Name Last Name   City     State
              Mike     Johns     Dallas    TX
              John     Cook      Dallas    TX
              Nick     Roberts   Dallas    TX
 2015-12-11   Oliver   Ryan      New York  NY
              Michael  Best      New York  NY
              David    Holmes    New York  NY

If anyone knows how this can be done please let me know. Thank you.

Lynn Crumbling
  • 12,985
  • 8
  • 57
  • 95
espresso_coffee
  • 5,980
  • 11
  • 83
  • 193
  • `DISTINCT` does not work that way, it applies to the entire row. It is not a "function". Also, it would be helpful if you edit the question to show an example of your desired output. – Uueerdo Dec 11 '15 at 20:09
  • Please post the desired output. – PM 77-1 Dec 11 '15 at 20:12
  • 2
    *DISTINCT does not work that way* True. Though based on the comment *...display just one date for multiple records*, sounds like what you want is a [grouped cfoutput](http://stackoverflow.com/a/24147305/104223). See also http://stackoverflow.com/a/21483596/104223 – Leigh Dec 11 '15 at 20:13
  • you want just one row to have the date output, is that it? And this will also have the potential of multiple dates (as in, we answer this, then you want a month's worth to come back, for instance) ? Note, there is no date in the where clause, but I bet there are more dates in the db – Drew Dec 11 '15 at 20:24
  • The question is unclear. I suggest you use coldfusion as your report engine, as opposed to have mysql perform this for you. – Drew Dec 11 '15 at 20:24
  • I gave example of just one table row, of course I can have multiple records that should be displayed on the screen same as this one above. – espresso_coffee Dec 11 '15 at 20:28
  • that is my point. So how is row 7 (the next date under David Holmes) going to be de-marked as belonging to the next chunk of a *next date*? – Drew Dec 11 '15 at 20:32
  • That is what a report engine does well, and mysql is not one. Were you expecting to expand upon this notion, and have mysql dump out a bunch of hyphen rows for you ? – Drew Dec 11 '15 at 20:33
  • @Drew - Agreed, this should be done at the application layer, not the database. In CF, you use the "group" feature of [``](http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7ff6.html). See link in previous comments. – Leigh Dec 11 '15 at 20:40
  • I think that will help. Thank for the hint. – espresso_coffee Dec 11 '15 at 20:43

1 Answers1

3

Each layer in the technology stack has its strengths and weaknesses.

As for mysql, do not turn it into a report engine as described with blank date columns except for one per date somewhere in the middle of a date chunk as shown. Subsequent dates as ordered will get muddled and confused.

True, one could use slightly interesting mysql variables and dump it just on the first row of a chunk. But for what.

Play to mysql's strengths, return all the data. And have the front-end (coldfusion or whatever), deal with the reporting features you desire for the output.

Drew
  • 24,851
  • 10
  • 43
  • 78
  • 2
    Agree with @Drew - MySQL is brilliant at managing data and allowing many people to read/add/edit (with the help of Apache or another web service). It's not an end-all, be-all formatter. That's where PHP (or PERL in the traditional LAMP stack) - or in your case Cold Fusion - comes in. Two loops: one to get all the distinct dates, then the inner loop to capture the data for each. – user3741598 Dec 11 '15 at 21:15