September 17, 2013 at 2:04 pm
Hi Everyone
I have the following query which I wish to turn into a view. The query uses 3 tables to compare old and new values.
SELECT Table1.QM, Table1.GD, Table1.Geometry, Table2.QM AS QM_OLD,
Table2.GD AS GD_OLD, Table2.Geometry AS GEOMETRY_OLD
FROM Table3 INNER JOIN
Table2 ON Table3.GD_OLD = Table2.GD INNER JOIN
Table1 ON Table3.GD = Table1.GD
here is some sample output
QM GD Geometry QM_OLDGD_OLD GEOMETRY_OLD
024068-002 24068002Value 037731-00337731003Value2
055257-004 55257004Value 006061-0016061001Value2
055257-005 55257005Value 006061-0016061001Value2
055257-006 55257006Value 006061-0016061001Value2
055203-004 55203004Value 034536-00334536003Value2
055257-003 55257003Value 053564-00553564005Value2
what I have been trying to do with out luck is to include only the rows which there is only one value for GD_OLD. As one can see from the above sample output the 2-4 lines all have the same GD_OLD, so I do not to include them in the output. I want the output to look like the following, only including records with unique GD_OLD.
QM GD Geometry QM_OLDGD_OLD GEOMETRY_OLD
024068-002 24068002Value 037731-00337731003Value2
055203-004 55203004Value 034536-00334536003Value2
055257-003 55257003Value 053564-00553564005Value2
Any help with this would be valued. Thanks.
September 17, 2013 at 2:08 pm
Use a GROUP BY and HAVING.
Since there is nothing to work this is only psuedocode.
GROUP BY QM, GD, Geometry, QM_OLD, GEOMETRY_OLD
HAVING count(Distinct GD_OLD) = 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 2:23 pm
Did not work. still showing all 6 rows.
September 17, 2013 at 2:26 pm
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data You already showed us this part.
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 2:27 pm
just adding the 5 columns to the order by returns an error
Column 'GD_OLD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
After adding the GD_OLD to the group by and adding the
having count(Distinct GD_OLD) =1
it returns that same rows.
September 17, 2013 at 2:34 pm
Are those geometry columns in your query? If so you will not be able to include them in group by clause.
As Sean said some DDL and sample data will help you get a solution.
September 17, 2013 at 2:35 pm
You have to remember that we can't see what you see. We need something to work with. I provided this as an example.
create table #Something
(
QM varchar(25),
GD bigint,
Geometry char(5),
QM_OLD varchar(25),
GD_OLD bigint,
GEOMETRY_OLD char(6)
)
insert #Something
select '024068-002', 24068002, 'Value', '037731-003', 37731003 ,'Value2' union all
select '055257-004', 55257004, 'Value', '006061-001', 6061001 ,'Value2' union all
select '055257-005', 55257005, 'Value', '006061-001', 6061001 ,'Value2' union all
select '055257-006', 55257006, 'Value', '006061-001', 6061001 ,'Value2' union all
select '055203-004', 55203004, 'Value', '034536-003', 34536003 ,'Value2' union all
select '055257-003', 55257003, 'Value', '053564-005', 53564005 ,'Value2'
There are probably some other ways to do this but I spent enough time on this already just creating the details so I can work on the problem.
select *
from #Something
where GD_OLD in
(
select GD_OLD
from #Something
group by GD_OLD
having COUNT(*) = 1
)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2013 at 2:43 pm
Thank you 'SSChampion', that worked.
I found where I was making my mistake. I was including the count(*) in my select statement as well as my having statment which was causing other issues.
Edit for spelling
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply