If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Select Unique Rows
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 |
#3
|
|||
|
|||
Select Unique Rows
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 |
#4
|
|||
|
|||
Select Unique Rows
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. |
#5
|
|||
|
|||
Select Unique Rows
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 |
#6
|
|||
|
|||
Select Unique Rows
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 |
#7
|
|||
|
|||
Select Unique Rows
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. |
#8
|
|||
|
|||
Select Unique Rows
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 |
Thread Tools | |
Display Modes | |
|
|