Rookie Question

Mar 21, 2006

Hi,

I'm trying to read the most recent entries in a table of data.

Its fairly straightforward because there is a timestamp saved when I write into the table.

For a table with the following columns

Username, description, location, timestamp, result1, result2

I can select the max timestamp for a username and it will return the most recent date/time that an entry was written to the table for that particular user.  No problems with that.

However, what I really want is to select the most recent timestamp for each location for each username; I also need the result1 and result2 fields returned.

Thus for the following table:

user   description   location   timestamp   result1   result2

Jo      Red               Hall        01/01/06      Yes      Yes

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

 

I would like a query to return:

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

The problem is if I do

Select  max(timestamp) as Tmstp, description, user,   location,   result1,  result2 from tblSomeTble

I will get

Jo      Red               Hall        01/01/06      Yes      Yes

Jo      Blue               Hall        01/02/06      Yes      No

Jo      Blue               Deck      01/04/06      No      No

because some of the results are different and thus the SQL will return the most recent entry for each unique combination of  user,   location,   result1 and result2.

I've solved the problem by doing two separate database reads; the first does a simple

Select  max(timestampdescription) as Tmstp, user, location

then I use VB.net to fill a dataset and read these values from the dataset and plug them into a second SQL for a second database read like:

Select  max(timestamp) as Tmstp, description, user,   location,   result1,  result2 from tblSomeTble where timestamp = [timestamp from first sqlread] and user = [user] and location = [location from first sqlread]

it all works fine, but I'm aware its messy and probably not very scaleable to have multiple database reads that are unnecessary.  It must be a commonly encountered problem though?  How do other people handle this? is there a common solution people use?

thanks

View 3 Replies


ADVERTISEMENT

PLEASE Help A Rookie

Jan 26, 1999

Hello,
I would like to create a SQL Server DB on my win95 machine...I have VB Studio 97 Enterprise Edition loaded on my machine.

When I go to Interdev and try to create a Project (via New DB wizard) the first question is "What Server do you want to create your DB on?"

I haven't found ANY documentation that details how to set up the Client and the Server on the SAME machine.

1) Can you set up SQL Server in a way that the same machine is the Client and the Server?
2) Do I need anymore software than VB Studio 97 Enterprise Edition? (To setup a SQL Server DB on my machine)

I basically need guidance on how to create a DB on my local machine, so I can reference it in my ASP application.

I appreciate your help, RB

View 1 Replies View Related

Rookie Question

Jun 13, 2006

    I have a database on my development machine and its working fine. And I want to duplicate the database schema on the production server. What is the easiest way to do this?James

View 1 Replies View Related

Rookie Needs Help With What To Do After Installation.

Sep 9, 2006

Any guidance to get me off the ground would really help

View 7 Replies View Related

Rookie Sql Question

Oct 10, 2006

SQL, It's up and running, 2000.

Looking in enterprise manager at the server, Green right handed arrow(running) with a red squiggly line?



What is that? (mean)?



Thanks

View 3 Replies View Related

Backuping Up Database --- ROOKIE ---HELP !!

Nov 9, 2000

I am getting an error backing up databases on a remote SQL Server. I have tried using two options:

1. All tasks, Backup Database... (Enterprise Manager)
2. Database Maintenance Plan Wizard

In both cases, I get the following error:

2000-11-09 18:21:34.93 kernel BackupDiskFile::CreateMedia:
Backup device 'FMSSQLBACKUPwOLTP_db_200011091821.BAK' failed to create.
Operating system error = 5(Access is denied.).

What are the exact NT and SQL Server permissions needed to perform backups.
Also, I am able to write manually to the backup directory not through the SQL Server application.

NOTE: ALTHOUGH I WAS ABLE TO REGISTER THE REMOTE SERVER USING USER sa,
I CANNOT STOP OR START ANY OF THE SERVICES (SQL SERVER AGENT...)
OR CONFIGURE THEIR STARTUP ACCOUNTS (GREYED OUT)!!

PLEASE HELP
THANKS
SHELLY

View 2 Replies View Related

SSIS Rookie Question

Apr 10, 2008

Hi everyone,

I'm new to SSIS. I need to Extract, transform and load data from one SQL table to another. I want to do something like "select count(a) from table 1 where ...."

I have define the following Data flow items on the package designer, I believe that OLE DB source & OLE DB Destination, are correctly configured, however i don't see how can i transform data on the "OLE DB COMMAND" item....should i use "data conversion" instead ?






ps: is there any site where i can find examples of data transformations ?

View 4 Replies View Related

Rookie Question About Query/View

Jul 23, 2005

I have much MSACCESS expeience and am new to SQL so here goes...I am trying to create calculated fields in a view, but can't seem tomore complex calculations to work.I can create a new field which does simple arithmetic on two otherfields and rename it so it becomes part of the new view to be queriedby other views, but when I use more complicated constructs I can'tseem to get them to work.For starters:How do you create a new field that has a number based on an IFconstruct based on another field.Example:field1 has state abbrevs and field 2 should have a "1" if field1="NY".In access I do field2:IIF(field1="NY",1,0),How do I accomplish this in MS SQL.Can it be done in the view?Thanks,Brad

View 7 Replies View Related

Incrementing Number In A Column -- Rookie Sql User

Jul 23, 2005

Sorry to bother you guys with what I though would be an easy task. Ihave a table in my database were I would like one of the rows toincrement a number for each row. I want the first row to start at 1000and keep on incrementing by 1 'till the end of the rows (about 2.7million rows). I though this would be a piece of cake, but I just can'tseem to find anything like it on the internet...weird.Anyways, I'm just a rookie in sql, any help would be appreciatedThanksJMT

View 6 Replies View Related

Rookie Question? Peer-to-Peer Replication Not Detecting Conflict

Mar 8, 2007

Hi

Sorry for asking what may be a rookie question... We have configured transactional peer-to-peer replication in a testing environment with two servers, each publishing and subscribing to each other. If we write a piece of code that updates the same row on both servers with different values, we are not seeing data conflict issues. The data updated on server 1 propagates to server 2, but at the same time data updated on server 2 is updated on server 1. This leaves the records on each server out of data.

I expected my test case to produce errors, but instead I got inconsistent data. How do you turn on data consistency checking?

Thanks in advance

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved