Click here to Skip to main content
14,736,767 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.
Updated 8-Mar-18 8: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[^]
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
Miguel Altamirano Morales 8-Mar-18 14:27pm
Getting Ready for bed ?
Maciej Los 8-Mar-18 14:32pm
Are you joking? No, i'm not. Mainly i'm going to sleep between 11 and 12 PM and i'm waking up at 5:30 AM.
Miguel Altamirano Morales 8-Mar-18 14:40pm
Such a healthy man. I always go to bed about 9 and 10 pm. (in between the week); at weekend I do it later.

I get up at 6:30 am.
Maciej Los 8-Mar-18 14:50pm
Man is a creature of habit ;)
Miguel Altamirano Morales 9-Mar-18 12:16pm
Hello Maciej; good afternoon (for you). I already developed a new query with VB code without the 'DISTINCTROW' predicate and it worked just fine; I'm not sure if this is only with this particular case, but I´m going to leave it like this for further stronger tests made by the user; I just can´t see the point in using this predicate in the Access App (VBA), and it seems not to work with VB Net; but if necessary, (if something fails) I intend to use it grouping the data with the 'GROUP BY' statement. In fact, I think I'm going to leave it that way right now.
Such an interest articles about Visual SQL joints representations you sent me; thank you very mucho for your help again and a big hug for that great nation of yours Polska.
Maciej Los 9-Mar-18 15:04pm
Thank you, Miguel. You're very kind person.
In case, you will need help in a future, please don't hesitate to call ;)

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