Databases :: SQL - Create A Variable That Holds The Value For Each Row Of The Timestamp?
Feb 8, 2010
I'm making a simple forum using MySQL and I use a repeater to display a list of all threads in it. I have two tables for posts, one for threads and one for replies, and I want to sort the threads by either when they were posted or when the last reply was posted in it. My SQL query is really long and hard to read so here's some psuedo code similar to it (easier to understand these column names, etc):
SELECT
threads.id,
threads.title,
threads.timestamp,
users.username,
(SELECT COUNT(*) FROM replies WHERE thread_id = threads.id) AS number_of_replies,
(SELECT COUNT(*) FROM views WHERE thread_id = threads.id) AS number_of_views,
(SELECT timestamp FROM replies WHERE thread_id = threads.id ORDER BY timestamp DESC LIMIT 1) AS last_reply_timestamp
FROM threads
LEFT JOIN users
ON threads.user_id = users.id
ORDER BY ************** DESC
What I do here is I fetch the values id, title, timestamp, the author's username, the number of replies and views for a specific thread, and I also get the timestamp for the last reply to this thread.
Then, what I want to do is sort the whole list so that the threads are displayed in an order matching when either it was posted or the last reply was posted in it (you know, like a normal forum) but I'm not sure how to accomplish this.
I tried this:
ORDER BY CASE WHEN last_reply_timestamp > threads.timestamp THEN last_reply_timestamp ELSE threads.timestamp END DESC
but that, I guess, maybe only looks at the first row and determines which one should be used so the order is not correct.
I'm not sure if it's possible to create a sort of variable that holds the value for each row of the timestamp that applies, if that's possible you could sort by that. For example:
(CASE WHEN last_reply_timestamp > threads.timestamp THEN last_reply_timestamp ELSE threads.timestamp END) AS last_activity_timestamp
...
ORDER BY last_activity_timestamp DESC
So each row has a field "last_activity_timestamp" containing either timestamp, whichever is the later than the other.
Can this be done somehow? How does everyone else solve this? It's so hard to search google because I just get lots of forums but no code.
Here is the situation:I have a Datagrid which is going to display a number of rows. I want to click a button on the datagrid and have the following occur
open a row underneath the row clicked, without hiding or replacing that row Open this row so that it spans all of the columns as one continuous row where i can put form fields and/or just data formatted as I would like and not just in columns.Be able to close that new row. While I do most of my work in VB.NET, don't think that is a requirement for answering this.I have managed to currently take the first column (with the "Edit" button) and when clicked to Hide the rest of the cells in that row Expand the button column to run all the way across the table Unfortunately I loose the clicked row's data, and I've only started getting this to work so I haven't tried too much, or getting data to bind to fields "on the fly"
From my experience in silverlight, their comboBoxItem can holds object data, so I can do like ComboBox.selectedItem and cast to the data object type and get the databinding object out. But in Asp.Net, the ComboBoxItem seems like only have Text and Value property for databinding. I wonder if there is a way that I can get the databinding object out? Not with searching the datasource and find the object by Text and Value...
I've got a form with a button on it. You have to be logged in to see this form. I use this code to check if the user is logged in and act appropriatley.
Dim isLoggedIn As Boolean = CType(Session("LoggedIn"), Boolean) If isLoggedIn = False Then Response.Redirect("Login.aspx") End If
The button runs a report that takes anywhere from 0.5 to 20 seconds. For testing it just sleeps for 10 seconds.
Threading.Thread.Sleep(10000)
The problem is this. Say User1 comes to the form, logs in and runs the report. If User2 comes to the form, the page will not load until User1's report is done.
Now, the bonus is if I comment out the response.redirect line, everything works fine. But if that line is in there I will get this problem.
I have 3 drop boxes: 1.) Choose Operation 2.) Choose Site 3.) Choose Area. All three of them are linked to an SQL server database which has 3 tables following the same heiarchy of order. I.e. Areas belong to a Site and Sites belong to an operation. And there can only be one operation per site, or one site per area.
First you select an operation, then the page auto postbacks to filter the sites. Then you pick a site and again it autopostbacks to filter the areas. The issue I am having is that when an associated operation has no sites underneight it, and therefore no areas under that, the previous values that were in the boxes are displayed and the user can't click on the boxes anymore. I think this is kind of cheap looking and would like a way to make it so the boxes are clear (i.e. nothin in them). Is there anyway to check if they are going to freeze like that and assign them blank values?
How do we code common functions for SELECT, INSERT, DELETE & UPDATE operations in Data Access Layer(Using ADO.NET ), If we have many tables in our database?
which is better hard coded DAL or using in-build database handling techniques?
I am trying to pass a textbox integer to a sql statement in my code behind using the "where" statement to filter what I get from the mysql database but for some reason, it diplays nothing. If I change the variable in the "where" statement to an actual digit between 1 and 12, it works.
I have a web page that has a textbox for users to enter a datestring to check against a database. The database that I am running my query against has datetime values set at minutes since 12/31/1899 and that returns a timestamp. What I'd like to do in my code is to convert my textbox entry into minutes since 12/31/1899. Does anyone have an easy way to do that?
I would like to know what does a field timestamp in MSSQL do?I have read a book "ASP_NET_3_5_Social_Networking" and almost all the tabels have a columnt with type "timestamp" in their example app.What is the benefit of timestamp , and why should i use it?
I have a column in my table that is named 'startDateTime' and 'endDateTime'. I need to select all the rows where current time stamp is in between the startdatetime and enddatetime. Note that the datatype is smalldatetime. How to do this?
I have created a dynamic data-driven report delivery by email in Reporting Services 2005. The report will be attached as PDF format. I can add the execution time stamp (@ExecutionTime) to the subject and comment lines. However, I cannot find a way to append the timestamp to the report name.
Dim testdate As String = "29/10/2010" testdate = Convert.ToDateTime(testdate.ToString) Response.Write(testdate) expecting "29/10/2010 00:00:00" what I get is "29/10/2010"
How to register a Java Script varibale in Server Side (Code behind ) and access at Client side (Javascript file ) , without a hiddenfield , Literal etc
I have several Include files in asp classic that I would like to convert to the class in C#, I have bunch of variable, Constraint, functions, DB connection. Is class the best way to convert them?
I'm building an application using MVC3 RC and I'm trying to add a session variable as soon as anyone logs on. The variable is an int which is retrieved from a database using User.Identity.Name to retrieve the relevant database record.
I'm using the MS AccountController and tried adding the session variable immediately after the line:
how can i create a backup table for my MySql base table.my base table name is FAB when ever user store the data into FAB table same records should be stored into back-up table automatically.how can i acheive this.i am really thank full to you if any one provide me solution.
