Viewing 15 posts - 14,836 through 14,850 (of 14,953 total)
Roy: Yeah, a case statement would work, but only with a small table where you have easily defined sets of values, and where the data will never change.
I'm kind of...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:49 am
Matt: You're right. Drop the C2 in the dense_rank function. Meant to have it in the query's Order By, but not in the dense_rank.
(And, actually,...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:47 am
alorenzini (1/17/2008)
Then again the value coming through will have different numbers eg. 123 needs to be 0000123 or 4567 needs to be 0004567.
That's what the "right" function is used for....
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:41 am
You'll need to use the WRITETEXT function to do that. It's a little complex, with pointers and such, but it can be done. Books Online gives the details....
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:35 am
select dense_rank() over (order by c1, c2) as [Nu.], c1, c2
from dbo.table
order by c1, c2
Edit: Only works in SQL 2005, not in 2000. For that, you need to insert...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:23 am
You'll have to define it as a string data type (char, varchar, nchar, nvarchar), then make it:
right('0000000000' + col1, 10)
That will make it so it's always 10 digits, with however...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:21 am
I've done various different types of IT work, and I have to say that database development and administration is the most satisfying for me.
I'd say go for it. If...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 11:18 am
Management Studio is what I use for typing all of my queries. Access is very awkward for that.
Some tricks that come in useful:
If you select part of the query...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 9:20 am
I think having "- Editor" is helpful. I assumed it was intentional and had a purpose (identifying who you are).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 17, 2008 at 9:07 am
Rajan (1/16/2008)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 2:28 pm
Jeff: Good point. I hadn't thought of that.
I just checked two execution plans, using both methods. They were identical, so no performance reason to use either. So...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 2:15 pm
A good place to start looking for Microsoft data on SQL Server is:
http://msdn2.microsoft.com/en-us/sqlserver/default.aspx
Lots of "how to" articles, blogs by MVPs, lots of other stuff.
Another good page to check out is...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 2:03 pm
My first suggestion is get very, very familiar with this site. It's a great place to learn all things SQL.
My second is get comfortable with Books Online, if you...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:53 pm
The only thing I can think of off the top of my head on this one is that the statistics on the tables are a mess. That might cause...
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:41 pm
What does, "... will not run ..." mean in this context? Do you get an error message?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 16, 2008 at 12:35 pm
Viewing 15 posts - 14,836 through 14,850 (of 14,953 total)