MS Access: Subselect, order by and such.
Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=13640
Printed Date: 30 March 2026 at 2:33am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: MS Access: Subselect, order by and such.
Posted By: Jyde
Subject: MS Access: Subselect, order by and such.
Date Posted: 03 February 2005 at 7:47pm
|
Hi all, I have this small problem that is bugging my to quitsville... I have two tables (simplyfied here): Company: ID Description CompanyName: CompanyID (foreign to ID above, many-to-one) Name FromYear The Company table is self-explanatory. The CompanyName table holds the different names a particular company have had since it started out. In order to generate a list of company names for a given year, I do something like this (again simplified here): SELECT co.ID, co.Description, (SELECT top 1 cn.Name from CompanyName cn where cn.CompanyID=co.ID) as Name FROM Company co WHERE cn.FromYear >= [YEAR] ORDER BY co.ID [YEAR] is a parameter I supply. My problem is that I would ideally like to have this list sorted by the company names, but alas one cannot sort by a subquery. I have tried with inner and outer joins, group by, but I keep hitting a wall. Please bear in mind that this is the simplified version, and this ties in with other tables, so re-structuring the tables is not an option. If anyone has any ideas how to get around this, I would be sleeping a lot better! I am fairly routined at SQL and I cannot believe I cannot solve this minor thing. I guess it is just one of those things...  All the best... |
Replies:
Posted By: michael
Date Posted: 03 February 2005 at 9:05pm
|
Why do you use a subquery? just use a join like
Select co.ID, co.Description, cn.CompanyName from Company co Join CompanyName cn on co.id=cn.CompanyID order by cn.Companyname ------------- http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker |
Posted By: Jyde
Date Posted: 04 February 2005 at 6:47am
|
Michael, thanks for your reply, but you missed a point: The CompanyName table is many-to-one and and I only want one (thus the top 1) name from that, ie. the name that is relevant to the year (FromYear). A simple join would give me loads of dublicate company IDs and all the names a company has had over time. Jyde |
Posted By: WebWiz-Bruce
Date Posted: 04 February 2005 at 7:14am
|
Can you not use:- SELECT DISTINCT So you don't get redundencies? ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: Jyde
Date Posted: 04 February 2005 at 7:43am
|
Problem with DISTINCT is that it works on all the fields in the select
statement, thus if I select only DISTINCT ID, it would work, though if
I did SELECT DISTINCT ID, Name, it would give me distinct combinations,
ie. all of them. But you did give me a good idea though. I think I have tried, but will have a look. If anyone has any other ideas, please keep them coming. Thanks. |