View Single Post
  #1 (permalink)  
Old March 31st, 2005, 12:13 PM
EndEffect EndEffect is offline
Registered User
 
Join Date: Mar 2005
Location: Trevose, PA, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Distinct SELECT DISTINCT question...

Okay... Here is my conundrum. I have a table of products. This table is a list of products with associated components listed under one specific system number:

Sysno | Item Number | Description | Rate
----------------------------------------
  1 | C123C | Laptop | 250
  1 | A987A | Lap. Memory |
  1 | A876A | Lap. Case |
  2 | C234C | Projector | 300
  2 | A765A | Proj. Case |
  2 | A654A | Proj. Bulb |
  3 | C345C | Technician | 50
  3 | A543A | Travel |

I realize that this is NOT an ideal product list, but I cannot modify it as it is the company's master database for a completely different complex piece of software.

I want to select all the data from each row (like SELECT *) BUT I only want the first of the distinct Sysno fields so my recordset would look like this:

Sysno | Item Number | Description | Rate
----------------------------------------
  1 | C123C | Laptop | 250
  2 | C234C | Projector | 300
  3 | C345C | Technician | 50

How can I accomplish this through a heavily modified Select statement?

Thank you all SO much in advance!