How can I GROUP BY with this Query?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How can I GROUP BY with this Query??

Author

Topic

stevehatpa
Starting Member

21 Posts

Posted - 2007-11-19 : 11:44:01

The following query works perfectly, but I need to group the final results by P_ID, because we are getting duplicates for a few of them. Our programmer is out until Wednesday (or else I'd ask him) and I need to perform this query today. When I add GROUP BY P_ID at the end of the statement, I get "Ambiguous column name P_ID".

Is there any way to Group the final results by P_ID in the query below? I'm hoping it would be rather simple...I'm crossing my fingers.

-Steve
---------------------------------------------------------

DECLARE @YEAR_ID INT
SET @YEAR_ID = 2007

select t5.*, t6.formated_address, t6.city, t6.zip_postal from
(select t3.*, t4.free_line_1, t4.free_line_2, t4.free_line_3, t4.free_line_4, t4.free_line_5,
(SELECT LONG_DESC FROM VT_USR_SDIST vus WHERE vus.CODE=t3.DIST_SCHOOL AND LANGUAGE='ENG' AND YEAR_ID=@YEAR_ID AND SIMULATION=0) AS SCH_NAME,
(SELECT LONG_DESC FROM VT_USR_CITYDS vuc WHERE vuc.CODE=t3.DIST_CITY AND LANGUAGE='ENG' AND YEAR_ID=@YEAR_ID AND SIMULATION=0) AS MUNI_NAME
from
(select t1.*, t2.tax_map, t2.inactive_year from
(select p_id, year_id, (select top 1 dist_city from pc_area where pc_area.p_id=pc_legal_info.p_id and pc_area.year_id=@year_id order by area_seq asc) as DIST_CITY,
(select top 1 dist_school from pc_area where pc_area.p_id=pc_legal_info.p_id and pc_area.year_id=@year_id order by area_seq asc) as DIST_SCHOOL,
(select top 1 NA_ID from pc_owner where pc_owner.p_id=pc_legal_info.p_id and pc_owner.status='O' order by isnull(seq_priority, 9999)) as NA_ID
from pc_legal_info where class in ('R','RT','T','AT','AC','A') AND PROPERTY_TYPE IN('0','1') AND YEAR_ID=@YEAR_ID AND P_ID NOT IN(select p_id from tx_exemptions where stat_home in ('AB','F','H','HD','HP','PB','PC','PD','PF','PH') AND YEAR_ID=@YEAR_ID)) t1
left join
(select p_id, tax_map, inactive_year from pc_parcel) t2
on t1.p_id=t2.p_id) t3
left join
(select na_id, free_line_1, free_line_2, free_line_3, free_line_4, free_line_5, free_line_6 from na_names) t4
on t3.na_id=t4.na_id) t5
left join
(select p_id, formated_address, city, zip_postal, loc_seq from pc_address) t6
on t5.p_id=t6.P_id and t6.loc_seq=0
where t5.inactive_year > @year_id

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 12:17:06

What is just grouping byy that going to give you?

AND SELECT * is bad

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

stevehatpa
Starting Member

21 Posts

Posted - 2007-11-19 : 12:30:02

The P_ID is a unique identifier. The programmer has created a query that gives some duplicate results. For instance, there are 4 identical rows when P_ID equals 3. If I group the results by P_ID, then there will only be one row of P_ID equaling 3.

That's fine if we are using "SELECT *" for the time being.

Is there any way to Group the results by P_ID, or am I out of luck?

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:22:30

What scalar function do you want to apply to ALL of the other columns?

MIN? MAX? SUN? COUNT?

This is why SELECT * is bad, you're gonna to have to list them out anyway

Do you know what columns you only need?

Or do you only want rows that aren't dups?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

stevehatpa
Starting Member

21 Posts

Posted - 2007-11-19 : 13:47:09

Brett,

Essentially all I need are rows that are not duplicates.

The final result should have: p_id, year_id, dist_city, Dist_Sch, na_id, tax_map, inactive_year, free_line_1, free_line_2, free_line_3, free_line_4, free_line_5, sch_name, muni_name, formated_address, city, zip_postal.

Thanks.

-Steve

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 14:09:24

Steve -- you are not getting it. If the data is currently returned like this:

1,A,B,C
1,A,B,D
1,A,C,E
1,B,C,D

Which is all for ID #1, and you want only one row per ID #1 to be returned, what do you want? What you are asking for doesn't make logical sense; your requirements have not been fully defined.

see: http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

stevehatpa
Starting Member

21 Posts

Posted - 2007-11-19 : 14:41:38

Actually, I phrased it wrong and I think I get it.

Each P_ID is a person and their address. I'm trying to filter out duplicates. I have:

1,A,B,C
1,A,B,C
1,A,B,C
1,A,B,C
2,B,M,N
3,C,P,X

Since the four of those are the same, I want to have:

1,A,B,C
2,B,M,N
3,C,P,X

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-19 : 15:12:57

But what if you have

1,A,B,D
1,A,B,C
1,A,A,C
1,A,B,C
2,B,M,N
3,C,P,X

???
Are they duplicates or not?
How do you define a duplicate?
A. The same ID or B. ALL columns are the same?
If A. then group by ID and use aggregation on all other columns
If B. then group by all columns, not just ID

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-19 : 15:14:40

If there are duplicates like that in your SELECT, then you probably have missing joins,duplicate data, or other major issues in your SELECT statement and/or your database design ...

Anyway, to get just distinct rows, change the very first line from:


select t5.*, t6.formated_address, t6.city, t6.zip_postal from

to:


select distinct t5.*, t6.formated_address, t6.city, t6.zip_postal from

I doubt this is really what you want and that it will return good results, but hopefully it will work for you, it technically will give you the exact results that you specified that you want.

By the way -- that is some really ugly code written by your developers ! I hope they are junior-level or interns or something....

- Jeff
http://weblogs.sqlteam.com/JeffS

stevehatpa
Starting Member

21 Posts

Posted - 2007-11-19 : 16:03:19

Thanks! I'll try it.

Our guy had to make this on the fly for our clients. He usually does much better work.

-Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources