Click here to Skip to main content
15,868,340 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello Everybody !!

Does anybody knows if the sql command "SELECT DISTINCTROW" can be used in VB 2017 code ?.

I'm trying to develop a query in which I need to select single unique records from two tables and it just doesn't work. If I change it to "SELECT DISTINCT" it works fine, but that's not what I need. If I keep the "SELECT DISTINCTROW' statement, VS 2017 keeps telling me there's a syntax error.

The query originally is in Access (the environment, not the database) 2010 VBA code and it runs perfectly there, and now I need to export it to VB Net. It is a sub-query that passes information to another (second) query of a higher level.

I'd really appreciate any help or tip.

What I have tried:

Searching thru the Internet and trying to develop a SQL Stored Procedure at least for the first query, and then trying to select data from it with a parameter.

But the SQL manager doesn't accept the SELECT DISTINCTROW statemente; it says is an invalid column.
Posted
Updated 8-Mar-18 7:52am

1 solution

DISTINCTROW predicate is MS Access database engine specific. As MSDN documentation states, there's small difference between DISTINCT and DISTINCTROW:
  • DISTINCT - Omits records that contain duplicate data in the selected fields.
  • DISTINCTROW - Omits data based on entire duplicate records, not just duplicate fields. For example, you could create a query that joins the Customers and Orders tables on the CustomerID field. The Customers table contains no duplicate CustomerID fields, but the Orders table does because each customer can have many orders. The following SQL statement shows how you can use DISTINCTROW to produce a list of companies that have at least one order but without any details about those orders.

See: ALL, DISTINCT, DISTINCTROW, TOP Predicates (Microsoft Access SQL) [Access 2007 Developer Reference][^]

So, if you want to get data in the way you can fetch them with DISTINCTROW predicate, you have to create subquery. Depending on situation, you may need to use one of the following predicates/methods: TOP, ROW_NUMBER() or LEFT|RIGHT JOIN.

For further details, please see:
Visual Representation of SQL Joins[^]
Ranking Functions (Transact-SQL) | Microsoft Docs[^]
 
Share this answer
 
v2
Comments
Miguel Altamirano Morales 8-Mar-18 14:13pm    
Thanks a lot again, Maciej; I'll check what you suggest.

What time in in Poland right now ?
Maciej Los 8-Mar-18 14:18pm    
8:18 PM
;)
Miguel Altamirano Morales 8-Mar-18 14:19pm    
1:20 pm here;such a diference !!
Maciej Los 8-Mar-18 14:24pm    
7 hours only
:laugh:
Miguel Altamirano Morales 8-Mar-18 14:27pm    
Getting Ready for bed ?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900