Select Unique Rows

Hello,

I have a table similar to this:

CustID Location OrderID
1 Vancouver 12
2 Vancouver 13
3 Boston 14
4 Boston 15
5 New York 16
6 New York 17

How can I use Access SQL query to generate

CustID Location OrderID
1 Vancouver 12
3 Boston 14
5 New York 16


Any help will be greatly appreciated.

Alan L.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Assuming that the OrderID field in a number and that you always want the
lowest number in it for each CustID:

SELECT tblAlan.CustID, tblAlan.Location, Min(tblAlan.OrderID) AS MinOfOrderID
FROM tblAlan
GROUP BY tblAlan.CustID, tblAlan.Location;
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"alee via AccessMonster.com" wrote:

Hello,

I have a table similar to this:

CustID Location OrderID
1 Vancouver 12
2 Vancouver 13
3 Boston 14
4 Boston 15
5 New York 16
6 New York 17

How can I use Access SQL query to generate

CustID Location OrderID
1 Vancouver 12
3 Boston 14
5 New York 16


Any help will be greatly appreciated.

Alan L.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Hello Jerry,

Thanks for your prompt reply.

However, I made a mistake when I posted the question. The third column is
not numeric based OrderID, and it could be any non-numeric field like
CountryName.
I can not use Min(tblAlan.CountryName).

If you have other suggestion, I will greatly appreciated. I have been trying
to use
Distinct or Group, but they don't seem to work.

Best regards,

Alan L.

Jerry Whittle wrote:
Assuming that the OrderID field in a number and that you always want the
lowest number in it for each CustID:

SELECT tblAlan.CustID, tblAlan.Location, Min(tblAlan.OrderID) AS MinOfOrderID
FROM tblAlan
GROUP BY tblAlan.CustID, tblAlan.Location;
Hello,

[quoted text clipped - 18 lines]

Alan L.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Can you describe in words what you want? Do you want the lowest CustID
for each location? In other words, how do you decide that you want
records with the custid 1, 3, and 5?


Distinct and group by do work, but you apparently want something that
they are not designed to return for you.

If you want the lowest custID for each location then that could look like:

SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)

If you want just a random record for each location:

SELECT First(CustID), Location, First(OrderID)
FROM SomeTable
GROUP BY Location



'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


alee via AccessMonster.com wrote:
Hello Jerry,

Thanks for your prompt reply.

However, I made a mistake when I posted the question. The third column is
not numeric based OrderID, and it could be any non-numeric field like
CountryName.
I can not use Min(tblAlan.CountryName).

If you have other suggestion, I will greatly appreciated. I have been trying
to use
Distinct or Group, but they don't seem to work.

Best regards,

Alan L.

Jerry Whittle wrote:
Assuming that the OrderID field in a number and that you always want the
lowest number in it for each CustID:

SELECT tblAlan.CustID, tblAlan.Location, Min(tblAlan.OrderID) AS MinOfOrderID
FROM tblAlan
GROUP BY tblAlan.CustID, tblAlan.Location;
Hello,

[quoted text clipped - 18 lines]
Alan L.

Hello John,

Your first solution works perfectly.

All I want is to get a column of distinct Locations to populate a drop down
box.

You are right, Distinct and Group both work properly when I use just
the preview feature within Visual Studio. However, when I connected
the query to an ASP.NET datasource, the built-in code populated all columns
which generated a "null or duplicate errors" for other data columns.

Thanks again for your wonderful help.

Best regards,

Alan L.

John Spencer wrote:
Can you describe in words what you want? Do you want the lowest CustID
for each location? In other words, how do you decide that you want
records with the custid 1, 3, and 5?

Distinct and group by do work, but you apparently want something that
they are not designed to return for you.

If you want the lowest custID for each location then that could look like:

SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)

If you want just a random record for each location:

SELECT First(CustID), Location, First(OrderID)
FROM SomeTable
GROUP BY Location

'================================================ ====
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================ ====

Hello Jerry,

[quoted text clipped - 23 lines]
[quoted text clipped - 18 lines]
Alan L.

--
Message posted via http://www.accessmonster.com

Hello John,

The query now returns a full table with distinct location. However, I don't
know
how to add "ORDER BY Location" to your solution.

I tried
a)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location
ORDER BY Locaiton);
b)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)
ORDER BY Locaiton;

I could not preview either a) or b).

Can you help?

Best regards,

Alan L.

John Spencer wrote:
Can you describe in words what you want? Do you want the lowest CustID
for each location? In other words, how do you decide that you want
records with the custid 1, 3, and 5?

Distinct and group by do work, but you apparently want something that
they are not designed to return for you.

If you want the lowest custID for each location then that could look like:

SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)

If you want just a random record for each location:

SELECT First(CustID), Location, First(OrderID)
FROM SomeTable
GROUP BY Location

'================================================ ====
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================ ====

Hello Jerry,

[quoted text clipped - 23 lines]
[quoted text clipped - 18 lines]
Alan L.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

What you posted has Locaiton as a field name instead of Location. So
you should be able to fix this by fixing the spelling of the field name.

If that is not the problem, then do you get any error messages?

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


alee via AccessMonster.com wrote:
Hello John,

The query now returns a full table with distinct location. However, I don't
know
how to add "ORDER BY Location" to your solution.

I tried
a)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location
ORDER BY Locaiton);
b)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)
ORDER BY Locaiton;

I could not preview either a) or b).

Can you help?

Best regards,

Alan L.

John Spencer wrote:
Can you describe in words what you want? Do you want the lowest CustID
for each location? In other words, how do you decide that you want
records with the custid 1, 3, and 5?

Distinct and group by do work, but you apparently want something that
they are not designed to return for you.

If you want the lowest custID for each location then that could look like:

SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)

If you want just a random record for each location:

SELECT First(CustID), Location, First(OrderID)
FROM SomeTable
GROUP BY Location

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===

Hello Jerry,

[quoted text clipped - 23 lines]
[quoted text clipped - 18 lines]
Alan L.

Hi John,

I tried the approach b) again, and it is working now.

Cheers,

Alan L.


alee wrote:
Hello John,

The query now returns a full table with distinct location. However, I don't
know
how to add "ORDER BY Location" to your solution.

I tried
a)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location
ORDER BY Locaiton);
b)
SELECT CustID, Location, OrderID
FROM SomeTable
WHERE CustID in
(SELECT Min(CustID)
FROM SomeTable
GROUP BY Location)
ORDER BY Locaiton;

I could not preview either a) or b).

Can you help?

Best regards,

Alan L.

Can you describe in words what you want? Do you want the lowest CustID
for each location? In other words, how do you decide that you want
[quoted text clipped - 30 lines]
[quoted text clipped - 18 lines]
Alan L.

--
Message posted via http://www.accessmonster.com