help date will not order by desc

Why is october at the bottom of the list?

sql code:
SELECT userInfo.*, marks.*, marks.datefield FROM userInfo INNER JOIN marks ON userInfo.userID = marks.userID ORDER BY marks.datefield desc;

this is what i get:
9/30/2003
9/23/2003
9/19/2003
9/1/2003
8/25/2003
8/21/2003
8/19/2003
8/19/2003
10/16/2003
10/1/2003

Why is october at the bottom of the list?hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps|||Originally posted by evanhattem
hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps
Am bit confused here I tried reproducing this problem but never got what is shown here. I tried the table with date field and also varchar field both ways the DESC worked.|||Originally posted by evanhattem
hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps

select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!|||Originally posted by wadecarlson
select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!

i just changed the data type in access to date/time and this seemed to fix the glitch.

thx all!!!!!|||basically what he is saying is to format the date differently.

instead of viewing 9/1/2003 change the format to 09/1/2003, with placing the 09 instead of 9 it will order properly.

just format the date column|||Originally posted by wadecarlson
select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!

Oracle knows only one format for the internal storing of a date. But we can show it differently. Internally it would be like mm/dd/yyyy, but we can show it like DD-Month-YY. To do so, we have to convert the date to a character string, because Oracle knows only one format of the date internally. TO_CHAR changes a date or a number value to a character, allowing us to display it in different format.

Good thing u have got it working.

Greetz.|||you still have a sorting problem, which you will discover in january

:cool:|||Originally posted by r937
you still have a sorting problem, which you will discover in january

:cool:

what do you mean? what will happen in jan?|||*********************************
*********************************
W00H00!!! my 1000th dbforums posting!
*********************************
*********************************

in january, if you are still using TO_CHAR to format and sort your dates, you will find that 01/01/2004 comes ahead of 12/31/2003 because you are sorting them as character strings

rudysql

HELP accessing SSAS cube from distance

hello to everybody. well i just put the ssas database to a distant server. well what can i do now in order to be able to access the cube from distance? what component do i need to install to the remote pc and is there any way to access the management studio of the distant server from a remote pc(that is to say by clicking to a link can i have access to the server's managment studio?)?

I recommend using Terminal Server. It is a part of Windows 2003 server and I think that up to two users are allowed to connect through the Windows XP remote PC. With more users you will have to pay a license. But check the license requirements.

Regards

Thomas Ivarsson

|||well i want to give the possibility for more than 5 personns to access the cube. can i do that by installing sslserver express edition?do i have to install managment studio to all posts? thank u for your answer|||

Install Management Studio on the remote server and run Terminal Server from your own workstation. With this setup you will only need a remote desktop connection on your workstation. Windows XP have this as default.

I am talking about Terminal Server licenses. I think you have to pay for more than two simultaneous users.

Regards

Thomas Ivarsson

HELP accessing SSAS cube from distance

hello to everybody. well i just put the ssas database to a distant server. well what can i do now in order to be able to access the cube from distance? what component do i need to install to the remote pc and is there any way to access the management studio of the distant server from a remote pc(that is to say by clicking to a link can i have access to the server's managment studio?)?

I recommend using Terminal Server. It is a part of Windows 2003 server and I think that up to two users are allowed to connect through the Windows XP remote PC. With more users you will have to pay a license. But check the license requirements.

Regards

Thomas Ivarsson

|||well i want to give the possibility for more than 5 personns to access the cube. can i do that by installing sslserver express edition?do i have to install managment studio to all posts? thank u for your answer|||

Install Management Studio on the remote server and run Terminal Server from your own workstation. With this setup you will only need a remote desktop connection on your workstation. Windows XP have this as default.

I am talking about Terminal Server licenses. I think you have to pay for more than two simultaneous users.

Regards

Thomas Ivarsson

help : Update statement

Here's the statement I need some help with:

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job'
WHERE Username = 'username'
;

what' i'm trying to do is insert the job description to the most recent entry (which is why i sorted) but it's adding the job description everywhere it sees username (which is more than once) so i need it to update the latest line

something like:

UID | Username | Job description
1 username test_job
2 username test_job1
3 username test_job2

when it updates it should add to UID 3 since it's the most recent. if anyone knows how to write the update statement please let me know. THANK YOU!

What determines if a row is 'most recent'?

Is it the larger UID?

What are the other columns in the tables?

|||I'm not sure how to make it so it selects the most recent. I wanted to sort it and select the first one (which would be most recent) but I'm not sure how to select the first one.

SELECT UID FROM sandbox.jobs WHERE username = 'username' order by UID desc LIMIT 1
FROM sandbox.timespent;
UPDATE sandbox.timespent
SET UID = UID, Username = 'username', Job_Description = 'test_job100'
WHERE UID = UID
;

that's what i wanted it to do, but instead of
WHERE UID = UID i want it to be something like WHERE UID = "most recent UID" some how

|||

So how do you determine which is the 'most recent UID'?

Is is the largest UID value? (It's still not clear.)

|||alright i'll try to explain myself better... I'm writing a program in vb.net

the table looks something like this:

UID | Username | Job | PLC_option | PLC_software
0 user1 9 option1 software1

that's what I want the data to look like. When the program runs, it adds a new UID and the users username to the table... another form opens and the user selects a job, i want the job to be added to that same column (where '9' is) but what's happening is the username dissapears. Another form will come up and the user selects the PLC option

what i'm trying to accomplish is the ID and username gets inserted and then the job and plc options get updated into that same row...

hope that's a little more clear.. thank you
|||

Kenny,

There are at least a couple of ways to go on this. First, let me ask a couple more questions.

What is the code that inserts the new UID and UserName into the table?

How is that code executed?

|||that's where i'm not sure... right now i have this code...

this is in my main form where the UID and username gets sent to the table...

strsuccess = " INSERT INTO sanbox.timespent "
strsuccess &= " ( `username ` ) VALUES ( "
strsuccess &= " '" & GlobalV.strUsername & "');"
x.dbCommand = strsuccess
x.ExecuteNonQuery()

strTime = "Update(sandbox.timespent) "
strTime &= " SET UID = UID , Username = '" & GlobalV.strUsername & "';"
strTime &= " WHERE(uid = uid) "
x.dbCommand = strTime
x.ExecuteNonQuery()

not sure if i did that part right but what I want to do is add a new UID to the form and add the username right next to that UID as a new session kind of deal... once the user proceeds to the next form this code is executed:

strjob = "Update(sandbox.timespent) "
strjob &= " SET job_description = '" & lstJobList.SelectedItem & "'"
strjob &= " WHERE username = '" & GlobalV.strUsername & "';"
x.dbCommand = strjob
x.ExecuteNonQuery()
x = Nothing

i'm pretty sure i'm not doing that part right, i want the job item to be added right next to the username... but what's happening is, the job item is filling in everywhere it finds the username to be the variable value...

i'd like to have it added to the newest UID, which would only add once... that's why i mentioned possibly making it so " WHERE UID = " some kind of uid variable "

it's a little hard to explain but if you can figure out how to add the job item once to the newest column i'd really appreciate it. thanks!

|||

OK, I see a couple of problems.

A couple more questions.

Where is the UID created?

Is it a IDENTITY field value from the table?

And What version/Edition of SQL Server? ( 2000 / 2005 ) ( Express / Standard / Enterprise )

|||the UID is unique and is incrimented by 1, it's the primary key if that helps.|||

Kenny,

You didn't respond about the version/edition of SQL Server, so I will assume that you are using SQL 2005.

This will not execute in your application, but it should point you in the direction that you need to go to make it work as you desire.

First, you need to capture the UID into a newly created application variable (scope it appropriately).

To do so, change the query type to ExecuteQuery so you can get a resultset.

Change the query to your version of this:

INSERT INTO SandBox.TimeSpent ( UserName )
OUTPUT inserted.UID
VALUES ( GlobalV.strUserName )

The application will do something like:

GlobalV.UID = x.ExecuteQuery

At the execution of the insert query, you will have the UID for the newly inserted row -which contains the UserName.

Then your UPDATE statement should be somewhat like this:

UPDATE SandBox.Timespent
SET Job_Description = {Your listbox.SelectedItem for JobDescription}
WHERE UID = GlobalV.UID

By using the UID, you will UPDATE ONLY the row you desire. AS you noticed, when you UPDATE by UserName, you are updating all rows with that UserName.

HELP - SQL Server 2000 Install Files needed urgently

Hi!

I have a large project that is due to complete this week. In order to
complete it I need SQL Server 2000 installed on a remote server. My
disk is corrupt and to order another media disk would damage my
deadline. I have the licence and serial key, but now need good install
files. I am even ready to buy another retail box, if I can find a
supplier that would give me a download site for the media, while I wait
for the shipment!

Please PLEASE help!

Regards,

BarryHi,

Sorry if i'm stating the obvious - but it wasn't commented on in your
post.. If you have the licence and serial key shouldn't you be
contacting Microsoft?

Greg|||Hi Greg. They will send me another media disk, but that doesnt help me
getting it installed for tomorrow.

Thanks for your reply.

Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.

Unfortuantely I doubt most anyone here would be comfortable putting binaries
out there to be copied. Even if you're totally legit, MS would have a field
day if they found out.

Sorry.

> Thanks for your reply.
> Barry|||"TheFoot" <workshop@.carib-sys.com> wrote in message
news:1112918510.565481.327210@.g14g2000cwa.googlegr oups.com...
> Hi Greg. They will send me another media disk, but that doesnt help me
> getting it installed for tomorrow.
> Thanks for your reply.

Oh, one other thought.. may be too late by an hour or two, but a couple of
places like PC Connection on the East coast used to have a "order by
midnight and get it the next day" policy. MIGHT have luck with them.

> Barry|||I agree with Greg, nobody's going to "lend" you the SQL Server binaries
based on the premise that you're entitled to them - too much personal
risk.

I see two options:

1. Delay the project and wait for the shipment
2. Incur the extra cost for the "retail box" and hopefully charge it
back to the business|||Download the eval and then upgrade later.

http://www.microsoft.com/sql/evalua...ial/default.asp

--
David Portas
SQL Server MVP
--

Help

Can anyone help me,
I am writting a script where i get to find out all the
store procedure, triggers,... that has the command "..from
table_name order by 2,1.." for example...
Here is what i got as a base idea... hope you understand
what i am trying to get at:
declare @.i int
declare @.tbnames varchar (150)
set @.i = 1
declare test cursor for
select [name] from sysobjects where xtype = 'U'
open test
fetch next from test into @.tbnames
if @.@.fetch_status = 1
Begin
if @.i < 9
begin
select o.name from syscomments c join sysobjects o
on o.id = c.id
where o.type IN ('TR','TF','P','X','V','FN')
and
c.text like '%from ' + @.tbnames + 'order by' + @.i
+ '%'
set @.i = @.i + 1
end
fetch next from test into @.tbnames
End
close test
deallocate test
Can anyone give me a hand with this pleaseI don't think you need to join from sysobjects just try
select object_name(id),* from syscomments where ctext like '%order by
[0-9]%'
since the comments can be split across multiple rows, IF a row happens to be
split in the middle of the order by OR the sp, etc is encryped your will NOT
get a hit...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:106ea01c41581$4e921ee0$a001280a@.phx
.gbl...
> Can anyone help me,
> I am writting a script where i get to find out all the
> store procedure, triggers,... that has the command "..from
> table_name order by 2,1.." for example...
> Here is what i got as a base idea... hope you understand
> what i am trying to get at:
> declare @.i int
> declare @.tbnames varchar (150)
> set @.i = 1
> declare test cursor for
> select [name] from sysobjects where xtype = 'U'
> open test
> fetch next from test into @.tbnames
> if @.@.fetch_status = 1
> Begin
> if @.i < 9
> begin
> select o.name from syscomments c join sysobjects o
> on o.id = c.id
> where o.type IN ('TR','TF','P','X','V','FN')
> and
> c.text like '%from ' + @.tbnames + 'order by' + @.i
> + '%'
> set @.i = @.i + 1
> end
>
> fetch next from test into @.tbnames
> End
> close test
> deallocate test
> Can anyone give me a hand with this please