January 9, 2010 at 8:08 am
--SELECT DISTINCT TOP 1000 Email
SELECT COUNT(DISTINCT(Email))
FROM(SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, '"project" and "manager" and "php"'))
AND (currentdateout BETWEEN '2000-01-01' AND '2010-01-06'))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
I tried SELECT TOP 10 COUNT(DISTINCT(Email)) but the count is wrong.
I also tried SET ROWCOUNT, but the count is also wrong.
I got it working on the DISTINCT statement, but can't get the correct solution using Count.
Thanks
January 9, 2010 at 1:01 pm
How about some sample data, Ifila? You've visited here 399 times, you must know the drill by now 😛
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 9, 2010 at 1:30 pm
I think 'the drill' is BS! By the time i go thru all the things that email asks for, i can get the response elsewhere, for a LOT less $$$
If people dont want to help, then they can move along!
:rolleyes:
January 9, 2010 at 2:09 pm
ifila (1/9/2010)
I think 'the drill' is BS! By the time i go thru all the things that email asks for, i can get the response elsewhere, for a LOT less $$$If people dont want to help, then they can move along!
:rolleyes:
Do you want true, valid, worthwhile help or would you prefer people just take pot shots in the dark?
We do not have the advantage of having your data, tables, etc available to help you. We ask for these things so that we CAN help you. If you want the help, you really need to help us to do that.
Considering we are all volunteers giving of our own, very valuable, free taime trying to help others, if you aren't willing to do the same, then I say go elsewhere. Don't be surprised, however, if you get the same responses there. Again, we ask questions to get a better understanding of what you are doing and what you are TRYING to do.
I suggest that you take the 15 minutes or so it would take to read the first article I reference below in my signature block regarding "Asking for assistance." If you follow those instructions, plus provide the expected results, you will find more people willing to help you, plus you'd get the benefit of TESTED code in return.
January 9, 2010 at 3:15 pm
Definition of a Forum "a public meeting or assembly for open discussion"
Based on the strict rules of this site, it does not qualify as a Forum!
....and people wonder why the tech jobs are going to India and China!
Great technical skills, none of the arrogance, and $10.00 per hour!
January 9, 2010 at 3:47 pm
ifila (1/9/2010)
Definition of a Forum "a public meeting or assembly for open discussion"Based on the strict rules of this site, it does not qualify as a Forum!
....and people wonder why the tech jobs are going to India and China!
Great technical skills, none of the arrogance, and $10.00 per hour!
I would question that based on some of the questions and where they come from on this site.
And it isn't arrogance when we ask questions about your problem. It is a desire to understand the problem and provide the best help possible while also trying to help others understand things better as well. There are quite a few extremely knowledgeable individuals on this site that are more than willing to share their knowledge with others. There needs to be a desire from those who ask for help to actually learn something rather than just expect us to provide answers to their work/school/interview questions and/or problems.
Since becoming active on SSC I have learned a lot that I may never have learned had I not become involved in this forum. It is, in fact, more than a forum; SSC is actually a community of professionals that seek to increase the knowledge and skills of others as a benefit to the community. You can choose to be a part of it, or not. It is really up to you.
Edit: Open discussion -- That means TWO WAYS. If you are asked questions, you really need to provide feedback. If not, it isn't a discussion, is it?
January 9, 2010 at 5:50 pm
ifila (1/9/2010)
--SELECT DISTINCT TOP 1000 EmailSELECT COUNT(DISTINCT(Email))
FROM(SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, '"project" and "manager" and "php"'))
AND (currentdateout BETWEEN '2000-01-01' AND '2010-01-06'))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
I tried SELECT TOP 10 COUNT(DISTINCT(Email)) but the count is wrong.
I also tried SET ROWCOUNT, but the count is also wrong.
I got it working on the DISTINCT statement, but can't get the correct solution using Count.
Thanks
Let us take a look at your original post, shall we?
Looking at it, I am not really sure what you are attempting to accomplish. You state that you can't get the correct solution using COUNT, but you can while using DISTINCT. Okay, what is the correct solution? What is the source of the data that the query is running against?
To truly help you, we need the structure of the table(s) as CREATE TABLE statements. Sample data for the table(s) as a series of INSERT INTO statements for the table(s). The expected results based on the sample data. Given this, we then have something to work with to help you solve your problem. Is it really too much to ask this of you or anyone else seeking assistance?
Do you feel that we are being arrogant to ask the individual asking for help to do this? I have actually seen people on SSC that after going through the process of providing this information have actually solved their own problems. When they haven't, they have been able to provide better answers to questions posed by people trying to help them. The final benefit is the best, the person asking for help usually gets more than one solution to their problem, PLUS tested code.
January 9, 2010 at 9:52 pm
Lynn Pettis (1/9/2010)
ifila (1/9/2010)
--SELECT DISTINCT TOP 1000 EmailSELECT COUNT(DISTINCT(Email))
FROM(SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, '"project" and "manager" and "php"'))
AND (currentdateout BETWEEN '2000-01-01' AND '2010-01-06'))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
I tried SELECT TOP 10 COUNT(DISTINCT(Email)) but the count is wrong.
I also tried SET ROWCOUNT, but the count is also wrong.
I got it working on the DISTINCT statement, but can't get the correct solution using Count.
Thanks
Let us take a look at your original post, shall we?
Looking at it, I am not really sure what you are attempting to accomplish. You state that you can't get the correct solution using COUNT, but you can while using DISTINCT. Okay, what is the correct solution? What is the source of the data that the query is running against?
To truly help you, we need the structure of the table(s) as CREATE TABLE statements. Sample data for the table(s) as a series of INSERT INTO statements for the table(s). The expected results based on the sample data. Given this, we then have something to work with to help you solve your problem. Is it really too much to ask this of you or anyone else seeking assistance?
Do you feel that we are being arrogant to ask the individual asking for help to do this? I have actually seen people on SSC that after going through the process of providing this information have actually solved their own problems. When they haven't, they have been able to provide better answers to questions posed by people trying to help them. The final benefit is the best, the person asking for help usually gets more than one solution to their problem, PLUS tested code.
Ditto - have seen the same sort of results and the OP is much happier in the end.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 9, 2010 at 11:54 pm
Add to this, if the OP is able to solve their own problem and still takes the time to post the information, they may be shown other ways to solve their problem. In fact, we usually encourage them to post their solution. Two reasons, gives others a chance to see how the problem was solved. Second, if you are open to constructive critism, the opportunity to find other possible solutions that may be better (or worse, but still different and worth seeing).
January 10, 2010 at 3:11 am
ifila (1/9/2010)
I think 'the drill' is BS! By the time i go thru all the things that email asks for, i can get the response elsewhere, for a LOT less $$$If people dont want to help, then they can move along!
:rolleyes:
I know its all BS, and time wasting. Where do these so called 'experts' get off !
I recently went to buy a simple car of all things. I went into the showroom and said "I want a car!".
God , you would not believe the stuff that came out of this guys mouth. "What was my budget?", "What luggage capacity would i require? ", "What MPG would i be expecting ?" MPG !!!! WTF does that even mean ? I told him that he was wasting my time, and that if he could answer my original question correctly he should just of ignored me.
Anyway , long story short, i phoned an offshore car expert , who recommend a Trabant. So i hopped on the first available plane to the old east germany , and bought the first one i saw. Shipping fees we quite high to bring it home , but still , job done ive got my car.
Now my problem is, my pet tiger who accompanies me every where cant fit on the back seat, what should i do ?
January 10, 2010 at 8:58 am
Lynn Pettis (1/9/2010)
ifila (1/9/2010)
--SELECT DISTINCT TOP 1000 EmailSELECT COUNT(DISTINCT(Email))
FROM(SELECT Email1,Email2,Email3, Email4,Email5,Email6,Email7,Email8,Email9,Email10 FROM outputresume3
WHERE ((contains (originalresume, '"project" and "manager" and "php"'))
AND (currentdateout BETWEEN '2000-01-01' AND '2010-01-06'))
)p
UNPIVOT (Email FOR Emails IN (Email1,Email2,Email3,Email4,Email5,Email6,Email7,Email8,Email9,Email10)) as unpvt
WHERE LEN(Email) > 0
I tried SELECT TOP 10 COUNT(DISTINCT(Email)) but the count is wrong.
I also tried SET ROWCOUNT, but the count is also wrong.
I got it working on the DISTINCT statement, but can't get the correct solution using Count.
Thanks
Let us take a look at your original post, shall we?
Looking at it, I am not really sure what you are attempting to accomplish. You state that you can't get the correct solution using COUNT, but you can while using DISTINCT. Okay, what is the correct solution? What is the source of the data that the query is running against?
To truly help you, we need the structure of the table(s) as CREATE TABLE statements. Sample data for the table(s) as a series of INSERT INTO statements for the table(s). The expected results based on the sample data. Given this, we then have something to work with to help you solve your problem. Is it really too much to ask this of you or anyone else seeking assistance?
Do you feel that we are being arrogant to ask the individual asking for help to do this? I have actually seen people on SSC that after going through the process of providing this information have actually solved their own problems. When they haven't, they have been able to provide better answers to questions posed by people trying to help them. The final benefit is the best, the person asking for help usually gets more than one solution to their problem, PLUS tested code.
Lynn,
This is the ONLY forum i have used in 10+ years, that virtually makes it a requirement to provide tons of upfront 'stuff' just to get a simple answer.
What is wrong with someone eyeballing the code and either commenting that they have done something similar and explain what they did, or they dont know, so they move along. Its not like we are curing cancer here, and going into unchartered territory!
BTW all i am trying to do is limit the number of records i am counting....pretty simple really. I just dont know the correct syntax.
January 10, 2010 at 9:32 am
ifila (1/10/2010)
BTW all i am trying to do is limit the number of records i am counting....pretty simple really. I just dont know the correct syntax.
It probably is, but personally I don't fully understand, from reading your posts, exactly what it is that you want. I've very likely done something similar many times but, if I were to tell you how I solved my problems, I may be giving you an answer to a question that you haven't asked. All that does is waste your time, waste my time and make people angry.
You said you tried several things and the count was wrong. What would a 'correct' count be?
Without understanding your exact requirements (which, as I said, I don't), any query I give you has got a good chance of also producing a wrong count.
Please remember that while the problem may appear simple to you, we're not there and we don't know the problem in the detail that you do. Hence the request for some data so that we can see exactly what the correct count that you want is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2010 at 9:51 am
SELECT COUNT(DISTINCT(variablename))
Let's assume i have 1000 variable names, with duplicates, which is why i need DISTINCT.
I only want to display the TOP 50.
SELECT TOP 50 COUNT(DISTINCT(variablename)) does not work.
SET ROWCOUNT 50 does not work.
What would be the correct syntax of the above statement?
January 10, 2010 at 10:18 am
But a SELECT Count will only return 1 row if there's no grouping, so why the top?
Do you want to count the distinct values in the top 50, or are you grouping by something, what the counts of the distinct values of everything and return only the top 50 grouped values?
Either way, I suspect you'll need another level of subquery.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2010 at 11:16 am
ifila (1/10/2010)
Lynn,This is the ONLY forum i have used in 10+ years, that virtually makes it a requirement to provide tons of upfront 'stuff' just to get a simple answer.
What is wrong with someone eyeballing the code and either commenting that they have done something similar and explain what they did, or they dont know, so they move along. Its not like we are curing cancer here, and going into unchartered territory!
BTW all i am trying to do is limit the number of records i am counting....pretty simple really. I just dont know the correct syntax.
I have seen the same request for information on other forums. Also, I curious why you think asking the for table defs, sample data, expected results is "tons of upfront stuff". You have all of this in front of you, what are you giving us? Just your code. We aren't mind readers, you have to give us enough information to understand your problem so that we can answer your question. Based just on your code, I have NO idea what you are are trying to do, nor where to even start to understand what you are trying to do.
As Gail said, to you the question may be simple, but to us where we do we start? How do we even setup a similar test environment to work with? How do we know what we develop is even correct without know what the results should be? This is why we ask for the information we do, it helps us HELP YOU.
Viewing 15 posts - 1 through 15 (of 103 total)
You must be logged in to reply to this topic. Login to reply