Query index

[Hokis]

Hi, i have this table that has a field that has 3 different names in it Like AAA-BBB & CCC but i have 1000 records they all are repeated. What i need to do is in the query i need to select AAA but only 3 records (random) of it, i don't want 500 AAAs, i just need 3 records.
I know how to put in the criteria "AAA" but it's bringing me the whole AAAs, so any suggestions???
Thanks
Hokis

 

[Remou]

Do you have an id/key field? If so, this may suit:

[tt]SELECT TOP 3 Rnd([ID]) AS SortFld, T.AAAField, T.ID
FROM T
WHERE T.AAAField="AAA"
ORDER BY Rnd([ID])[/tt]

 

[lewatkin]

Use SELECT DISTINCT in your query. Using the QDE, you can right mouse click in the upper portion (where the tables are - but not on a table), then select properties, then set Unique Values=Yes, or go to the SQL View and add DISTINCT after SELECT as shown above.

 

[Hokis]

well i did what lewatkin said but i don't see anything that's done.
all i need is to index on a field & get 3 of each (index)
Like if i have AAA, BBB & CCC in the field, then i need 3 of AAA, & 3 of BBB & 3 of BBBs, how can i do that.
Thaks again
Alice

 

[jedraw]

Did you try Remou's approach?

 

[Hokis]

I didn't understand Remou's approach SORRY.
I did do select top 3 but it is selecting only top 3 records & it's not indexing on AAA & BBB that i want.
SO???

 

[lespaul]

please don't double post....thread701-1444449



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com