Subquery with Distinct - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Subquery with Distinct

Author

Topic

LauraFields
Starting Member

4 Posts

Posted - 2011-06-13 : 11:09:25

I'm really struggling with an issue I am having. I want to get a distinct on COL2, but I'm not sure how with the subquery? Instead i get duplicates in COL2 which don't want,

SELECT COL1, COL2
FROM
(SELECT table1.COL1, table2.COL2
FROM
TAB1 as table1, TAB2 as table2
WHERE
table1.ID = table2.ID
ORDER BY COL1)
GROUP BY COL1

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 11:19:23

subquery doesn't do anything

SELECT table1.COL1, COL2 = max(table2.COL2)
FROM
TAB1 as table1
join TAB2 as table2
on table1.ID = table2.ID
group by table1.COL1

or maybe

SELECT table2.COL2, COL1 = max(table1.COL1)
FROM
TAB1 as table1
join TAB2 as table2
on table1.ID = table2.ID
group by table2.COL2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LauraFields
Starting Member

4 Posts

Posted - 2011-06-13 : 12:05:39

Thanks, you are right. I took out the subquery and still get the same result set. What about pivoting the data? I'd like the data to be horizitonal, particularty COL2. I tried dense ranking on my original query but was unsuccessful.

quote:
Originally posted by nigelrivett

subquery doesn't do anything

SELECT table1.COL1, COL2 = max(table2.COL2)
FROM
TAB1 as table1
join TAB2 as table2
on table1.ID = table2.ID
group by table1.COL1

or maybe

SELECT table2.COL2, COL1 = max(table1.COL1)
FROM
TAB1 as table1
join TAB2 as table2
on table1.ID = table2.ID
group by table2.COL2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 12:41:11

Depends on what you want to get.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-13 : 16:46:07

Derived tables are tables and tables have no ordering; that ORDER BY is silly. Give the derived tablea name so you can reference it.

SELECT X.col1, MAX(X.col2) -- or whatever aggregate you need.
FROM (SELECT Tab1.col1, Tab2.col2
FROM Tab1, Tab2
WHERE Tab1.generic_silly_id = Tab2.generic_silly_id)
AS X(col1, col2)
GROUP BY X.col1;

If you had posted DDL, we could do better.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

LauraFields
Starting Member

4 Posts

Posted - 2011-06-14 : 10:21:42

Thanks for the input. I did not actually write the query, I have been trying to figure it out the best I can while being fairly new to sql. Thanks for the help.

quote:
Originally posted by jcelko

Derived tables are tables and tables have no ordering; that ORDER BY is silly. Give the derived tablea name so you can reference it.

SELECT X.col1, MAX(X.col2) -- or whatever aggregate you need.
FROM (SELECT Tab1.col1, Tab2.col2
FROM Tab1, Tab2
WHERE Tab1.generic_silly_id = Tab2.generic_silly_id)
AS X(col1, col2)
GROUP BY X.col1;

If you had posted DDL, we could do better.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-14 : 17:48:29

Everyone looks at me funny when I tell them that one of the best books for an intro SQL and RDBMS is THE MANGA GUIDE TO DATABASES. It is a quick, funny read and might be in the kid's section of a public library.

Of course, Everyone looks at me funny no matter what I say. I think it is the serial killer thing.. :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 17:53:37

quote:
Of course, Everyone looks at me funny no matter what I say. I think it is the serial killer thing

You do rock the Mephistophelian look.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-15 : 12:58:33

Panel once told me I am called "The Sequel Satan" in India that they can get my books :)

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

seomert
Starting Member

3 Posts

Posted - 2011-06-15 : 13:00:45

thanks! Great solutions

erenköy bosch servisi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources