Link to home
Start Free TrialLog in
Avatar of gudii9
gudii9Flag for United States of America

asked on

distinct values of column

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
ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gudii9

ASKER

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

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.
Something like this?
SELECT cust_code, COUNT(1) AS "Number of employees" 
FROM orders
GROUP BY cust_code;

Open in new window

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??
Avatar of gudii9

ASKER

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