Slect Records with more than one value in another

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 2008 Forums

Transact-SQL (2008)

Slect Records with more than one value in another

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-01-21 : 10:25:45

m trying to select records with more than one value in another column but it seems not to work. the date column is varchar

this is my table
num date
010001 00000000
010001 00000000
010001 00000000
010005 00000000
010005 00000000
010005 20011001
010006 00000000
010006 00000000
010006 00000000
010007 00000000
010007 00000000
010007 00000000
010007 20011001

i want to select only

010005
010007

without using rank over function

select num, date FROM table1
group by num, date
having count(num) > 1

any help!!

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-01-21 : 10:31:04

SELECT distinct(num) FROM table1
WHERE num IN (SELECT num FROM table1 GROUP BY num HAVING COUNT(DISTINCT date)>1)

this works...thanks though

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-21 : 12:30:36

>> trying to select records [sic: rows are not records] with more than one value in another column but it seems not to work. <<

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

>> The date column is VARCHAR(n). <<

Hopefully, you fired the moron who did this. Now you need to go back and correct his error(s). Your posting has no key nor any way to ever have a key. That measn it is not a table, just loose garbage data.

SELECT something_nbr
FROM Garbage
GROUP BY something_nbr
HAVING COUNT(DISTINCT vague_date) > 1;

--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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources