|
You could try using COALESCE (sp?) in your ORDER BY clause to replace the nulls with a far off date
ORDER BY COALESCE(MyDateColumn, '9999-12-31')
You might want to check what the maximum date permitted is in SQL Server as I don't know off the top of my head. It changes between the DATETIME and SMALLDATETIME types also.
|
|
|
|
|
Hi,
Thank you, thank you very much....that really works fine now. As you said the maximum value for datetimefield is December 31, 9999 and smalldatetime is June 6, 2079.
Best Regards
Sebastian
|
|
|
|
|
|
is this what you are looking for ....
select case when c1 = '31-dec-9999' then null else c1 end as c1 <br />
from (<br />
select top 1000 * <br />
from (<br />
select case when c1 is null then '31-dec-9999' else c1 end as c1 from a <br />
)a order by c1<br />
)a
i've checked this query with following sample table and data
CREATE TABLE a (c1 datetime)
insert into a values('01-jan-07')
insert into a values(null)
insert into a values('21-jan-07')
insert into a values('10-jan-07')
insert into a values('11-apr-07')
insert into a values(null)
insert into a values('10-apr-07')
insert into a values(null)
insert into a values('30-apr-07')
and output is
2007-01-01 00:00:00.000
2007-01-10 00:00:00.000
2007-01-21 00:00:00.000
2007-04-10 00:00:00.000
2007-04-11 00:00:00.000
2007-04-30 00:00:00.000
NULL
NULL
NULL
Regards
KP
|
|
|
|
|
Hi, thanks Kp, i have solved the issue.....
sebastian
|
|
|
|
|
you welcome.
colin's solution was simple
Regards
KP
|
|
|
|
|
Any help would be aoppreciated. I'm trying to pass the login username information into a sql table filter on column containing names. I have tried WHERE statement but can't find how pick-up user name.
Steve
|
|
|
|
|
sjp700 wrote: Any help would be aoppreciated. I'm trying to pass the login username information into a sql table filter on column containing names. I have tried WHERE statement but can't find how pick-up user name.
Some more information would be helpful. The SQL code you are currently using. The appropriate code in the application that calls the SQL also would be useful also.
|
|
|
|
|
Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition> applies
...any thoughts?
I'm looking for something in SQL; I can do it fine with ADO.NET but ADO.NET is too slow and need something faster.
|
|
|
|
|
logan1337 wrote: Hi all. I'm wondering if there is a means in SQL to delete a certain number of rows from a table, when ordered a certain way. In other words, I want to be able to say something like this:
delete the 10 oldest rows where <some condition=""> applies
How do you define which are the oldest rows? Once you have the answer to that question you can add an ORDER BY clause and then a TOP 10
|
|
|
|
|
That is easy, just order by a certain date column, in ascending.
I've done a bit of reasearching this TOP command, but from what I understand, you in fact cannot use ORDER BY with TOP when deleting. According to the documentation on MSDN, TOP simply selects n random rows at a time, and is really only intended for splitting up delete operations over several transactions.
Can anyone verify this? It would be nice to be able to delete n rows off the top of a sorted table.
Logan
|
|
|
|
|
You can do this:
DELETE<br />
FROM MyTable<br />
WHERE ID IN (SELECT TOP 10 ID FROM MyTable ORDER BY HistoricalDate)
If you have any other conditions with which to filter the data to be deleted then they should be put in the subquery.
|
|
|
|
|
Ah yes! Of course, thanks! I was so caught up on reading that it couldn't be used with deletes that I didn't realize you could delete using a select! 
|
|
|
|
|
Hello,
VS 2005
Using untyped dataset.
I have a xml file which I am using for local storage.
I have a dialog box that displays text boxes for uses to add another new incident.
I read in the xml file and add the following code to set the auto increment properties of the dataset table.
However, everytime the user wants to add a new incident the auto-increment properties will be set again and again. So the new row will not be incremented and will always have the same incidentID PK value.
Is there a way to only set these only once, so that when the add incident form loads it won't re-set the auto-increment properties. I was thinking of having some sort of global untyped dataset.
My code is below:
many thanks for any advice on this.
<br />
'Read the xsd file<br />
dsIncidents.ReadXmlSchema(incidentXSDpath)<br />
'Read in the xml fil<br />
dsIncidents.ReadXml(incidentXMLpath, XmlReadMode.DiffGram)<br />
<br />
Dim dt As DataTable = dsIncidents.Tables(0)<br />
'Set the auto increment properties<br />
dt.Columns("IncidentID").AutoIncrement = True<br />
dt.Columns("IncidentID").AutoIncrementSeed = 0<br />
dt.Columns("IncidentID").AutoIncrementStep = -1<br />
<br />
Dim dr As DataRow = dt.NewRow()<br />
dr("Company") = Me.cboCustomer.Text<br />
dr("Contact") = Me.cboContact.Text<br />
dr("PhoneNo") = Me.txtPhone.Text<br />
dt.Rows.Add(dr)<br />
<br />
'Write to the xml and schema files<br />
dsIncidents.WriteXmlSchema(incidentXSDpath)<br />
dsIncidents.WriteXml(incidentXMLpath, XmlWriteMode.DiffGram)<br />
|
|
|
|
|
i write PROCEDURE to update record in datadate this PROCEDURE have where statment ,,,
mow how can if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
now how can know if the PROCEDURE make update successfully to database or not ???
Palestine
|
|
|
|
|
i want a transact sql for select queries which returns
all rows that include something like here:
table1
code, name, address, ?? , ??
1 , john , toronto , ..
2 , mike , new york , ..
3 , alec , john city, ..
...
select * from table1 where includes 'john'
result is:
1 , john , toronto
3 , alec , john city
[note1: i do not know field names]
[note2: fields are in different data types]
[ like : int, numeric, float, char, text, image ....]
[note3: full text indexing is appropriate for string fields,not for all types]
|
|
|
|
|
amraouf wrote: [note1: i do not know field names]
Then you will need to find out.
amraouf wrote: [note2: fields are in different data types]
It is a bit difficult to do a string match on a non-string column.
amraouf wrote: [note3: full text indexing is appropriate for string fields,not for all types]
Then why not create a string column with all the searchable stuff in it (including string representations of non-string column) and index that. If you make the column name consistent across all tables then you don't need to know each individual column name, because all tables have it.
|
|
|
|
|
ooops!
its a bit difficult to change the structure of a database for such a query !
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database
How come?
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
amraouf wrote: its a bit difficult to change the structure of a database for such a query !
No it isn't. This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
If you have difficulty figuring out how to change the database structure from one thing to another then you might want to buy a book on refactoring databases. There is a very good one published by Addison Wesley.
|
|
|
|
|
Colin Angus Mackay wrote: This is a simple change.
1. Add the extra column on each table that requires it.
2. Add triggers to each table to update the new column each time there is an insert or update
Thank you, I was too lazy to go to that extent earlier 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thank you very much
Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
|
|
|
|
|
amraouf wrote: Of course I designed the database and I am the first one who can change or update it
but you know
in this phase of project i can not push my time on this matter
When I put a project together I figure out what are the bigger risks and do them up front so I've got as much time as possible to make changes if my ideas don't work out. It sounds to me that you need to be identifying those risks before it gets too late.
If the feature is not a "Must have" then it can be dropped. If the feature is required then the solution I gave is probably the quickest route to it being implemented.
|
|
|
|
|
select * from TableName where name + address like '%john%'
I Love SQL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
