distinct values of column

gudii9
gudii9 used Ask the Experts™
on
SELECT COUNT ( DISTINCT cust_code ) AS "Number of employees"
FROM orders;

checking above query at
https://www.w3resource.com/sql/aggregate-functions/count-with-distinct.php

how to display those unique values of  cust_code rather than just count
please advise
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Commented:
Getting rid of the COUNT() will return unique cust_code's

SELECT DISTINCT cust_code
FROM orders;

Author

Commented:
SELECT DISTINCT cust_code
FROM orders;
gave syntax error

SELECT DISTINCT (cust_code )
FROM orders;

above worked in microsoft sql
sql has to be same across all databases right why syntax differences?
Most Valuable Expert 2015

Commented:
Shouldn't make a difference, but maybe remove the optional semi-colon?  Though it worked fine for me with SQL Server 2008, with or without the semi-colon.

CREATE TABLE #Orders ( Cust_Code varchar(50))  
INSERT INTO #Orders VALUES ('abc'), ('efg')

SELECT DISTINCT cust_code
FROM #orders;

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015

Commented:
sql has to be same across all databases

In this case the same syntax should work in just about any database. However, that isn't always possible. Some things in SQL are vendor specific.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Something like this?
SELECT cust_code, COUNT(1) AS "Number of employees" 
FROM orders
GROUP BY cust_code;

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
SELECT DISTINCT cust_code
FROM orders;
gave syntax error

No, it wouldn't: _agx_ gave you the syntax exactly correctly.

There is something else causing the error -- maybe a statement before it or after it??

Author

Commented:
In this case the same syntax should work in just about any database. However, that isn't always possible. Some things in SQL are vendor specific.

is there link or resource where i can see which things are same which things are vendor specific across different databases?
Most Valuable Expert 2015

Commented:
Unfortunately there are too many versions and operations to compile a complete list of differences.  Like the link below says, all of the db's I've ever used implement basic ANSI commands like SELECT/UPDATE/INSERT/DELETE, but other features differ depending on the vendor. Even when it's possible, it doesn't always behoove you to use generic ANSI SQL.  Sometimes Vendor A or B's implementation of a particular feature provides better performance than the generic version.  

Here are a few links to give you a general idea of some of the differences.  The ANSI version's a bit outdated, but gives the general jist..
https://dba.stackexchange.com/questions/1014/how-different-are-the-top-db-vendors-sql-dml-dialects-from-each-other
https://stackoverflow.com/questions/1714461/ansi-sql-manual#1714789
Geert GOracle dba
Top Expert 2009

Commented:
Why does sql have to be the same for all db’s ?

There are inventions in companies, which are patented, and which will never be available in other db’s.
Take oracle’s locking for example

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial