How do I filter rows based on a column value which is causing duplication of data display in UI?

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Sql query that I am running:

SQL

select distinct pack.package_code,pack.package_key, pack.package_desc, pack.package_status, pnr.pr_code, pnr.pnr_code, pnr.pnr_desc, pnr.pnr_family,pnr.pnr_status 

from 	T_MODEL_PACKAGE_PNR pack, T_PNR_DETAILS pnr

where pack.pr_code = pnr.pr_code 

and pack.model_year = pnr.model_year 
and pack.model_code = pnr.model_code 
and pack.model_version = pnr.model_version 
and pack.brand_code = pnr.brand_code 
and pack.model_year = '2010' 
and pack.model_code = '5K132K'
and pack.model_version = 4
and pack.brand_code = 'V'
and pack.package_code = 'PLA'
										
order by pack.package_code, pnr.pnr_code;

The Result I am getting from above the query:

--Modified results for proper display and understanding--

PACKAGE_CODE PACKAGE_KEY PACKAGE_STATUS	PR_CODE PNR_DESC
------------ ----------- -------------- ------- ---------
PLA	        GPLAPLA	 	       E	        8K3	MFLS8K3	Automatic headlight 
PLA	        GPLAPLA	 	       F	        8K3	MFLS8K3	Automatic headlight 
PLA	        GPLAPLA	 	       E	        6T1	MINL6T1	2 lights in front foot area
PLA	        GPLAPLA	 	       E	        4L6	MIRS4L6	Automatically dimming
PLA	        GPLAPLA	 	       E	        8N3	MSWS8N3	Rain sensor
-----------------------------------------------------------------------------------

Problem:

If you observe from the results, first two rows are almost identical but PACKAGE_STATUS is different.

My concern is, If a PR_CODE is having package_status both 'E' and 'F', I have to get only one row as this is resulting display duplicate rows in UI.

Kindly suggest me to change the query. Thanks.

Posted 4-Jun-15 5:07am

Bhanuchandar Challa

Updated 4-Jun-15 5:17am

v2

Add a Solution

2 solutions

Solution 1

Simply do not select PACKAGE_STATUS because "distinct" depends on what you are selecting
something like :

SQL

select distinct pack.package_code,pack.package_key, pack.package_desc, pnr.pr_code, pnr.pnr_code, pnr.pnr_desc, pnr.pnr_family
from 	T_MODEL_PACKAGE_PNR pack, T_PNR_DETAILS pnr
...
...

Posted 4-Jun-15 5:20am

Mohamed El khames Bakir

Comments

Bhanuchandar Challa 4-Jun-15 11:31am

But I need at least one status to be selected to display either E or F.

Mohamed El khames Bakir 4-Jun-15 11:50am

then try to do a group by instead of distinct and use min or max aggregate on PACKAGE_STATUS to choose either E or F

Bhanuchandar Challa 4-Jun-15 12:15pm

Can you please suggest the change, it would help as I am new to sql.

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)

Mohamed El khames Bakir · Accepted Answer · 2015-06-04T06:22:00

Here's the way with Group by:

SQL

select pack.package_code,pack.package_key, pack.package_desc, pnr.pr_code, pnr.pnr_code, pnr.pnr_desc, pnr.pnr_family,Min( pack.package_status)
 
from T_MODEL_PACKAGE_PNR pack, T_PNR_DETAILS pnr
 
where .....

Group by  pack.package_code,pack.package_key, pack.package_desc, pnr.pr_code, pnr.pnr_code, pnr.pnr_desc, pnr.pnr_family
										
order by ....