Equivalent to DISTINCTROW

What is the equivalent to DISTINCTROW keyword of Microsoft Access? DISTINCT is not an exact replacement.
braviAsked:
Who is Participating?
 
mayhewConnect With a Mentor Commented:
There is not an equivalent keyword.

But the workaround is to include the field names in your select distinct statement that you want uniqueness to be defined on.

For example, let's say you want to see a list of all of your customers with at least one order even if they have the same name.

In Access you would use:

SELECT DISTINCTROW Customers.CustomerName
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CustomerName


This query will bring you back the name of every customer (in customers table) with at least one order (in orders table) even if two customers have the same name.

For example, if you have customer Bob with a customerid of 1 and customer Bob with a customerid of 5, they'll both show up in a distinctrow where only one Bob would show up in a distinct.


In SQL Server, you could do:

SELECT DISTINCT Customers.CustomerName, Customers.CustomerID
FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY CompanyName


This will give you the same rows as the distinctrow query would.  The problem is that you now have to deal with the customer id being returned in your query.

But if you're working in VB or something like that, it shouldn't be a problem to repress a column (like customer id) that you don't want to show.

Let me know if that helps.
0
 
simonsabinCommented:
There is only DISTINCT, can you give me an example where they are not equal. I think it is only down to how records are retrieved.
0
 
braviAuthor Commented:
That definetly helps. I will give you the points. Thanks
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
mayhewCommented:
I'm glad that worked.  :)
0
 
simonsabinCommented:
Have been thinking on this,
an equivalent is to use the IN and a subquery i.e

SELECT DISTINCTROW Customers.CustomerName
FROM Customers
WHERE Customers.CustomerID IN (SELECT DISTINCT Orders.CustomerID
FROM Orders)

0
 
mayhewCommented:
I don't think that will do it.

I'm assuming you meant distinct instead of distinctrow in the main select.

If you have CustomerName = Bob, CustomerID = 1 and CustomerName = Bob, CustomerID = 5 you're still only going to get one Bob returned.
0
 
simonsabinCommented:
Sorry remove the distinctrow completely, i think.

The essence is you don't want 4 rows if you have 4 orders, you only want one.
0
All Courses

From novice to tech pro — start learning today.