This SQL works in Oracle, but not in SQL Server. How do I structure it in SQL
Server? It determines the number of unique rows (distinct) based on multiple columns.
select count(*) FROM (select distinct hdr_invoice_number, trn_sequence, lip_charge_transaction from STG1_INVOICE_FACT)
Requires Free Membership to View
The syntax you have supplied does work with SQL Server when you alias the sub select. For example execute the following T-SQL:
create table #demo1 ( col1 int, col2 int, col3 int ) insert #demo1 values(1,2,3) insert #demo1 values(1,2,3) insert #demo1 values(1,2,4) insert #demo1 values(1,2,4) insert #demo1 values(1,2,4) insert #demo1 values(5,6,7) insert #demo1 values(6,7,8) select count(*) from( select distinct col1, col2, col3 from #demo1) table1This produces a COUNT(*) of 4 which is the number of distinct rows when selecting unqiness based on the three columns. Similarly this code:
select count(*) from( select distinct col1, col2 from #demo1) table1produces a count(*) of 3 as uniqueness is being selected on only two columns.
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in October 2003
There are Comments. Add yours.