First Records or Certain Data

[gregorg]

Hi, I have a table of the following.
---------------------------------

field 1--- field 2
-----------------
A-----------1
A-----------2
A-----------3
B-----------2
B-----------3
C-----------3
C-----------4
C-----------5
------------------

I want to right a quiery to pull out the 1st, 4th, and sixth record.

In other words to pull out the first occurance if A, and the first of B and the first of C.

Can anyone give me any help please ??

Gregor.
-----------

 

[boblarson]

How are you ensuring that you know which order is correct. Access does not store data in any particular order in the tables, so selecting these would be dependent upon a date/time stamp or some other means.

 

[georgedwilkinson]

Try this:

select field1, min(field2)
from Gregordstableofthefollowing
group by field1;

 

[boblarson]

Is the first occurance always going to be the min value though? That would be good to know. If it is then shouldn't be a problem getting the min of each item. But, if it isn't then that presents the same difficulty in knowing what is the first occurance, because what is displayed now isn't necessarily going to be the same order displayed each time (without explicit ordering) as the data in Access tables are not stored in any particular order.

 

[gregorg]

Hi i got it wrong the data is more like

field 1--- field 2
-----------------
A-----------qwewq
A-----------ewt
A-----------rrwehgr
B-----------regtre
B-----------rgsdfg
C-----------dfgdfsg
C-----------dgsdfg
C-----------dfgsdfgfsd
------------------

as in field two is non sequential

damn

 

[gregorg]

but there IS a sequental ID field. 1,2,3,4,5,6,7,8,9

 

[gregorg]

so the data is more like

ID---field 1------- field 2
-----------------------
1---red car-----------qa
2---red car-----------ws
3---red car-----------ed
4---yellow car--------rf
5---yellow car--------tg
6---pink car----------yh
7---pink car----------uj
8---pink car----------ik
------------------------

so i need to pull out 1,4,6

 

[georgedwilkinson]

OK, then how about this:

select field1, field2 from
Gregordstableofthefollowing
where ID in
(select min(ID)
from Gregordstableofthefollowing
group by field1);

This is air code...not tested or guaranteed to work.

Whatever you do, you need to define how you will distinguish what is the criteria that makes your select happen.

 

[gregorg]

GREAT it works !,can i ask you to explain how ?

as in

ID in
(select min(ID)
from Gregordstableofthefollowing
group by field1);

i tried to use the First() command ????

 

[georgedwilkinson]

I've never used the First command (I'm old-school). Theoretically, First() and Min() should do the same thing in this case (I guess...see my first sentence).

The subquery just filters the eventual result set down to those records that are unique on the "field1" column and applies the smallest value of the ID to those...it then returns that ID to the outer query for processing the request.

 

[gregorg]

Cool
........................

 

[boblarson]

Just to be aware that min will not necessarily pull what you think it is going to. It will as long as your ID is sequential and numeric. But, should you ever decide to use replication for your data, you can throw it out the window because you will then end up with ID's of -2399203992, etc. which will throw your assumptions off.

That is just something to be aware of.