|
Introduce a new table called PriorityOrder with something like:
Priority PriorityOrder
-------------------------
0 5
1 1
2 2
3 3
4 4
And then join your tables with this table on the Priority column and then Order by the PriorityOrder column.
This design gives you the flexibility to change the ordering any time by changing the contents of this table instead of changing code.
|
|
|
|
|
Wow what a nasty solution, he simply wants to move the 0 value records to the end of the sort and you want to create and support another table. So every time a new record is created he potentially needs to update the priority table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Priorities are usually limited and may be used in more than one table. This is a generic solution which works best if Priority is stored in its own reference table (a.k.a. Master table), in which case the PriorityOrder column can appear in the same table.
|
|
|
|
|
Hi Shameel,
Thanks for your reply to my problem.
Like Mycroft Holmes said, I just want a simply to move the 0 value records to the end of the sort.
Regards.
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Hi,
Mycroft's solution is elegant and I like the way he presents the solution.
However, there are a few more ways which will accomplish the task, though again I like Mycroft's solution
declare @t table(docvalue varchar(50),priority int)
insert into @t select 'aaa',0 union all select 'xxx', 1 union all select 'bbb', 3 union all
select 'ccc',0 union all select 'aaa',2
Query1:
select docvalue,priority from @t where priority <> 0 group by priority,docvalue
union all
select * from @t where priority = 0
Query 2:
select distinct * from @t where priority <> 0 group by priority,docvalue
union
select * from @t where priority = 0
Output:
docvalue priority
xxx 1
aaa 2
bbb 3
aaa 0
ccc 0
Niladri Biswas
|
|
|
|
|
Hi guys.. Actually i have an web application running on vs2003 it fetches the records from a list of xml file now i want to import all my data that is present in the xml file to sql db(sybase).. can any one guide me as to how can i import the data using c# coding... i googled alot dint get the apt one..I tried using Sqlbulkcopy but that doesnt work with vs2003... and also there are duplicate rows in my xml files...
Thanks in advance...
|
|
|
|
|
Read in your xml file
convert it into a datatable
clean out the duplicates
save each record to the database via a database connection and a sqlcommand
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanx for ur response... since im a fresher do u hav any sample codes or can u mention any tutorials that wil provide me a helping hand... anyways thanks for ur reply...
regards,
Thashif
|
|
|
|
|
Sorry, that's maybe 4 hours work and if I did that for you then how do you expect to become anything but a "fresher"
As a general comment, learn to use the keyboard, typos I can live with, but that crap you used is just horrible.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I neva asked u 2 work or complete the task for me.. i only asked u 2 mention any tutorials or sample codes if u know.. wel learn 2 use keyboard ???? what does that mean??? again, crap??? i dint get u....
|
|
|
|
|
<<thashif>> wrote: I neva asked u 2 work or complete the task for me
You are right so take the ideas I gave you one line at a time and feed them into Google, look at the results and you will find lots of articles and tutorial on each of the subjects. Then put them together in a solution.
As for your keyboard use, are you serious, there are 101 keys on most keyboards and you want to use your like a bloody phone. Wake up to yourself and communicate like a professional or at least competent person.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have uninstalled sql server express 2005 and installed sql server express 2008 but now I found that almost all )reporting services) windows services are still exists and that's why the reporting services installation for 2008 was failed because it was using the same instance name.
what can I do?
|
|
|
|
|
I am using SQL server 2008 express on WIndows server 2008 and everything is fine so far but today is the first time for me to use the reprting services..
when I go to the reporting services web page I just get a blank white page with the following text:
server/ReportServer_SSGSQL - /
Microsoft SQL Server Reporting Services Version 10.0.1600.22
what can I do?
Jassim Rahma
|
|
|
|
|
Hi All ,
If have table contains of seven columns or more, and i need to return the douplictate records across only three column.how i can writ this using SQL query ?
for example i have this table :
Clmn1 | Clmn2 | Clmn3 | Clmn4 .......|Clmn7
AJ 35 25 0 19
BQ 10 12 1 8
KS 35 25 3 19
i need to return all records that have duplicat value in this three columns only "Clmn2" , "Clmn3" and "Colmn7" . like this:
Clmn1 | Clmn2 | colmn3 | clmn4 .......|colmn7
AJ 35 25 0 19
Thanks,
KS 35 25 3 19
|
|
|
|
|
Hint: Use GROUP BY clause with HAVING COUNT(*) > 0
|
|
|
|
|
you know , Your Are Great !!
Many thanks,
could you please allow me to ask you a question :
i wrote this:
<code>SELECT</code> MyTable.Column1 , S.Column2,S.Column3, S.Column4
<code>FROM</code> MyTable , (
<code>Select</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>From</code> MyTable
<code>Group By</code> MyTable.T2 , MyTable.T3, MyTable.T4
<code>Having</code> ( Count(Sheet1.T2)>1 and Count(Sheet1.T3)>1 and Count(Sheet1.T4)>1 )
) as S
<code>Where</code> MyTable.T2=S.T2 and MyTable.T3=S.T3 and MyTable.T4=S.T4 and it is work , but are there better than this way ? becuse when i put Column1 column in the nested select clause with out to be in group by cluase , i got error that show " the coulmn1 is not in the agregation function " so why this is happen? . instade of that i use join to get Column1 , was what i did right ?
|
|
|
|
|
Hi,I hope this will help you
SELECT
(SELECT top 1 m2.Clmn1 FROM mytable m2 WHERE m2.Clmn2= m.Clmn2) Clmn1 ,--by this subquery you get first value of clmn1 column
clmn2,clmn3,clmn7
FROM mytable m
GROUP BY m.clmn2,m.clmn3,m.clm7
HAVING
COUNT (col2) > 0 AND
COUNT (col3) > 0 AND
COUNT (col7) > 0
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
yeeees !! Blue_Boy , realy realy i appreciate your reply. you are great and helpfull.
yor answer is very celever .
Many Thanks
|
|
|
|
|
You are always welcome bro
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Hello,
Following the faq, I am attempting to query the a DB to obtain the list of table names with the following query:
SELECT * FROM sqlite_master WHERE type='table' ORDER BY name
I am using an ODBC driver and have written code in c#:
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("SELECT * FROM sqlite_master WHERE type='table' ORDER BY name");
DbReader = DbCommand.ExecuteReader();
List<string> TableNames = new List<string>();
for (int i = 0; i < DbReader.FieldCount; i++)
{
TableNames.Add(DbReader.GetName(i));
Trace.WriteLine(DbReader.GetName(i));
}
DbReader.Close();
DbConnection.Close();
I have a feeling that dbreader.getname(i) is returning the wrong thing, versus the query being incorrect.
Can anyone help me figure out a way to identify the names of tables in an SQLite3 DB?
Thanks,
Matt
|
|
|
|
|
The DbReader you obtained can be used to retrieve the table names from the "name" field:
DbReader["name"]
So your program should be modified to be like this:
DbConnection.Open();
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("SELECT * FROM sqlite_master WHERE type='table' ORDER BY name");
DbReader = DbCommand.ExecuteReader();
List TableNames = new List();
while(DbReader.Read()){
TableNames.Add(DbReader["name"]);
Trace.WriteLine(DbReader["name"]);
}
DbReader.Close();
DbConnection.Close();
Your original code was trying to retrieve all the fields in one record.
Good luck!
|
|
|
|
|
Thanks for the modified code!
Can't make a list like that for some reason. Maybe I'm missing an import; I kept the casting.
Because of this:
OdbcCommand DbCommand = DbConnection.CreateCommand();
DbCommand.CommandText = ("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
DbReader = DbCommand.ExecuteReader();
List<string> TableNames = new List<string>();
while (DbReader.Read())
{
TableNames.Add(DbReader["name"].ToString());
Trace.WriteLine(DbReader["name"]);
}
DbReader.Close();
if (TableNames.Contains("bandwidth_records"))
{
DbConnection.Close();
}
else
{
)
Thanks!
|
|
|
|
|
Hello,
I have an issue with addresses that I need to parse into streetNum, Direction (if it is there), sreetName.
The data can look as such:
1234 McKean Street -- Needs to look like StreetNum: 1234, Direction: , StreetName: McKean
1234 Mc Kean Street -- Needs to look like StreetNum: 1234, Direction: , StreetName: Mc Kean
1234 E McKean Street -- Needs to look like StreetNum: 1234, Direction: E, StreetName: McKean
1234 E Mc Kean Street -- Needs to look like StreetNum: 1234, Direction: E, StreetName: Mc Kean
(edit I went ahead and made the 526 addresses that include 1/2 to be have an underscore so since there is no space anymore it is easy to query)
As far as I can tell those are all the options. There are approximately 600,000 records so it really has to be automated. There can be A LOT of changes in this data as the city may update a large portion of it at any time so I need to be able to do this on a monthly basis.
One thing to note I am trying to do this so I can compare this data to another table of 70,000 records to automate a search that I would normally do using a "like" query in Access but I streamlined that data as it was only 70k records with a turnover of about 50 a month so that is easy enough and now I need to streamline this data so I can compare the two tables.
That data is in this format:
streetNumber 1324, 1324-28, 1324-1/2
direction N,S,E,W
streetName McKean (I went through by hand and took all the spaces out)
I post this information just in case it is easier to do this another way even though it would be really nice to have that data parsed but because of it being different and the only delimiter being a space makes it difficult to parse.
Any help would be much appreciated.
Thank you,
George
modified on Friday, December 4, 2009 10:27 AM
|
|
|
|
|
Let me know if it helps
Sample data
declare @t table(completeaddress varchar(100))
insert into @t
select '1234 McKean Street' union all select '1234 McKean Street' union all
select '1234 E McKean Street' union all select '1234 E McKean Street'
Query
select
StreetNum = 'StreetNum : ' + SUBSTRING(completeaddress,1,4) +','
,Direction = 'Direction : ' +
case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W')
then SUBSTRING(completeaddress,5,2) else '' end + ','
,StreetName = 'StreetName : ' +
case when ltrim(rtrim(SUBSTRING(completeaddress,5,2))) In('N','S','E','W')
then SUBSTRING(completeaddress,7,LEN(completeaddress))
else SUBSTRING(completeaddress,6,LEN(completeaddress)) end
from @t
Output:
StreetNum Direction StreetName
StreetNum : 1234, Direction : , StreetName : McKean Street
StreetNum : 1234, Direction : , StreetName : McKean Street
StreetNum : 1234, Direction : E, StreetName : McKean Street
StreetNum : 1234, Direction : E, StreetName : McKean Street
Niladri Biswas
|
|
|
|
|
SET NOCOUNT ON
DECLARE @ID uniqueidentifier,
@vFirstName nvarchar(255),
@vLastName nvarchar (10)
DECLARE customer CURSOR FOR
SELECT @ID,
FirstName,
LastName
FROM user
OPEN Customer
FETCH Customer INTO @ID,
@vFirstName,
@vLastName
WHILE @@Fetch_Status = 0
BEGIN
FETCH Customer INTO @ID,
@vFirstName,
@vLastName
Insert into test values (@ID,@vFirstName, @vLastName)
END
CLOSE Customer
DEALLOCATE Customer
RETURN
The above cursor works good, but what i want to change above is - Instead of taking ID from user table , i want to
SET ID = newid() but on adding this after DECLARE customer am getting error.
How can i get around this problem ?
Thank You
Andyyy
|
|
|
|
|