• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Display Data Based on Date

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have 2 columns in a table in mysql which looks sometime like below,



The result I am looking for is something like,



Can we iterate resultsets object to get the data displayed in this format.How is this normally done, is this done at DB Level or needs to formatted via application code.
 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can someone reply to this.
 
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i would use the order by clause in the sql statement, and then perform processing on date changed.
 
Sheriff
Posts: 22783
131
Eclipse IDE Spring VI Editor Chrome Java Windows
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And you should know that PatienceIsAVirtue.
 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes I have order by clause Wendy.Please do look at what I am requiring.Order by does give me what I want,but it do not completely do what I am looking for.I need for each day, display all the records for that day.Move to next date, display all records for that date and continue in descending order.
I have 2 columns in my table, title and timestamp in mysql,I am converting timestamp to date and then for each date displaying all the titles in desc order.Currentdate being on the top.

For resolving this after trying various solutions and failing miserably, I feel now this cannot be done in the single query,

So I thought let me do it with 2 queries and feed the result of first query to second one and then do it.



Of course still I am working on this.But would be happy if someone has better way to do it.All I am looking for is some logic which will scale.Requirements looks simple,but for some reason I am just not able to figure this out.

Yes Rob, I understand one needs to have patience and I do have it.
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Kiran, please click on and read the PatienceIsAVirtue link carefully. It will help you understand why Rob has put it up; I think it was perfectly appropriate.

Wendy's reply is perhaps a little bit terse, but it does contain everything you need to process your original requirement. And yes, it is doable with one query, with a little help on the processing side. You'll go like this:

1) Declare a variable to hold last processed date. Initialize it to some value which is guaranteed not to occur in the processed rows.

2) Select all the record you want to obtain and order them by date first, then by any other criteria you want to apply inside that date.

3) Loop through the records:
  3a) Whenever the current record's date differs from the last processed date, print out the date header and store the new date into the last processed date variable.
  3b) Print out the other information from the record.

That's about all. However, you code sample contains a LIMIT clause on the dates, which was missing from your original problem description. It cannot be easily incorporated into the SQL query in the processing I've described. If that one is necessary, I'd suggest to open the query for all records and stop processing once 100 different days are processed.

This will probably scale better than your current approach, unless the LIMIT clause causes only a small fraction of the rows in the table to be read.
 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Martin, I did try the approach you suggested,

But please do look at the timestamp field, its always going to be unique.So its necessary for me to convert timestamp to Date.

>>1) Declare a variable to hold last processed date. Initialize it to some value which is guaranteed not to occur in the processed rows.

Just for clarification, its insert time along with date.Its mysql timestamp field. Lets say I initialized it to current date thats is 2012-07-23.

>>3a) Whenever the current record's date differs from the last processed date, print out the date header and store the new date into the last processed date variable.

again lets say that there is some inserts from 23th july morning, so its alway going to find some values.So lets change to change value to null, since its nonnull field, I am sure we will not come across this value. Then I run the query

Select Date(ctimestamp),title from table A Limit 100;
I loop through this resultset and compare Date with null value, so for each comparision its not going to match,so I have timestamp/title getting added to list for each loop.In this list I will have many duplicates records coming up something

Date1, title1,Date1,title2,Date1,title3..

This is not what I require.I need for each Date1, It should have records title1,title2,title 3 etc.

If my understanding is incorrect, can you give me some tips as how what values should I use to initialise date variables.

I think getting less data from DB helps in first place then doing processing on the code and limit processing records to 100.Thats the reason I use Limit.








 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


This is what I got it finally with JSTL though it seems to meet my requirement,I am for some reason unable to do this via java class.Queries do return the data in the format I want now.But for some reason I am unable to push it to view in the manner I need it.Tried experimenting with lists/maps/arrrays ,but still not luck.Can someone have a look and give me some shout on this please.

Here is my all experiments done so far get this implemented in some DAO Class.I dont want this feature to implemented with JSTL.
 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can some one reply to this please.
 
Marshal
Posts: 28193
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

kiran badi wrote:Can some one reply to this please.



I don't see a question. You claim you have it working in JSTL and you display some Java code which looks sort of equivalent to me. Although I haven't spent a lot of time looking at it, because you haven't said what your problem with it is.
 
kiran badi
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Paul for replying.Yes JSTL code does meet my requirement,but I am somewhat not comfortable in implementing this with JSTL.I was looking to implement this
as part of model class.My problem is that I am not able to build the proper list datewise for some reason.Every time I add resultset to list or map, I lose the relationship
between date and its corresponding titles.
This is one of my core requirements for my site,and its very huge site which I am working on.so I expect this part of code to be repeated across 100's my products.
I want this to be implemented on the lines of valuelist pattern.
reply
    Bookmark Topic Watch Topic
  • New Topic