cant pull up distinct records

Question

0.00/5 (No votes)

See more:

this is my stored procedure, its doing what its supposed to do except its "distinct" function, right now its pulling up more than 1 result for one city.

I am very new in sql so forgive me if this question sounds weird for youq

SQL

  SELECT distinct city.name, jp.id
, ROW_NUMBER() OVER (order by city.name) [sr_no]
, COUNT(jp.id) OVER (partition by name) as no_of_posts
, COUNT(od.id) OVER (partition by name) as no_of_employers
, CONVERT(varchar(12), jp.posting_date, 103) [date_created]
   FROM rs_job_posting jp

INNER JOIN rs_job_posting_location jpl on jp.id = jpl.id
INNER JOIN rs_cor_city city on jpl.city_fk = city.id
INNER JOIN rs_organization_detail od on od.id = jp.id
order by city.name ASC
where cast(city.name as int) <= 1

Posted 5-Dec-13 22:44pm

shoaibsheikh

Updated 5-Dec-13 23:11pm

Jörgen Andersson

v2

Add a Solution

Comments

Member 10434230 6-Dec-13 4:56am

What are you trying to achieve?
From query I can interpret that you have cities and job openings for those cities. From those jobs you are trying to get openings grouped by cities. Is this true?
Are you trying to get jobs city wise?

shoaibsheikh 6-Dec-13 5:27am

thats right, i have this screen where i need to pull up posts by city, everything is working fine except that if i have more than 1 record for 1 city it will show it twice, thats what i dont want, i want it to show that only once but not able to do so far

Member 10434230 6-Dec-13 6:03am

I got it, your query is returning DISTINCT rows...

Member 10434230 6-Dec-13 6:13am

Can you please clarify your requirement, the SQL query you have posted is doing it's job.
We are missing with our requirement.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2013-12-05T23:09:00

Solution 1

As we're lacking information here we can only guess what you're trying to achieve.
But as the DISTINCT clause is performed BEFORE the analytic functions the DISTINCT is actually made on city.name, jp.id AND od.id, which might not be your intention.

<edit>As you have od.id = jp.id in your join, how do you expect no_of_posts and no_of_employers to be different from each other?</edit>

Posted 5-Dec-13 23:09pm

Jörgen Andersson

Updated 5-Dec-13 23:23pm

v3

Comments

shoaibsheikh 6-Dec-13 5:28am

@jorgen, forgive me if i didnt provide all necessary info. please let me know what else do i need to provide here

Jörgen Andersson 6-Dec-13 7:24am

Well, you can start with telling us what you try to achieve.
Give us some simplified test data, and what result you would expect.

I don't understand how the different IDs can be connected to each other.
jpl.city_fk = city.id makes sense, but how rs_organization_detail.id can be the same entity as rs_job_posting.id is beyond my understanding.

Jörgen Andersson 6-Dec-13 7:25am

But most important, I believe you're mixing up the functionality of aggregation with analytic functions. Google it.