Destinct Question

[SteveV]

I need to return a query that returns a distinct value in only one field but I need to display two other fields: Here is my data:

Run BNumber SNumber
84 KB743 17
95 KK546 13
95 HG435 55
56 AW546 33

I only want unique run numbers, so the record for 95 should only show the first. If I use the DESTINCT key work in the SELECT statement it still outputs multipule run numbers because the BNumber and SNumber are different. Any ideas?

 

[pcs]

if i understand the question...think you could split your query into 2 queries, the 1st with unique values for Run, then join the second query to it to get BNum and Snum.

hth,
al


[This message has been edited by pcs (edited 11-08-2001).]

 

[shacket]

What about using FIRST?

SELECT First(tblName.Run) As FirstOfRun, BNumber, SNumber FROM...

You could use LAST as well.

 

[SteveV]

PCS,
I am a little confused about what you mean to "split the query" do you mean use a sub query. Can you post an example?

Shacket, your fix would probably work but the syntax is incorrect and all Access gives me is a SQL error. Can you give me the correct syntax?

 

[pcs]

stevev,
sorry, was somehow thinking you had 2 tables...shacket's method is correct...

try this:

SELECT tblYourTable.Run, First(tblYourTable.BNumber) AS FirstOfBNumber, First(tblYourTable.SNumber) AS FirstOfSNumber
FROM tblYourTable
GROUP BY tblYourTable.Run;

hth,
al