By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,078 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,078 IT Pros & Developers. It's quick & easy.

first()/order by query

P: n/a
Hi,

Having a table with some duplicate ID's (different data tho), how can I
return the list but with only one record from each ID? Would this be using
the first() function and grouping?

Also, how would I choose which would be the first to be picked? If I did an
order by xx, would this alter the results, or is the first record picked
before the ordering is done?

Cheers,
Chris
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Not Me" <su**@sumwhere.fake> wrote in message
news:bp**********@ucsnew1.ncl.ac.uk...
Hi,

Having a table with some duplicate ID's (different data tho), how can I
return the list but with only one record from each ID? Would this be using the first() function and grouping?

Also, how would I choose which would be the first to be picked? If I did an order by xx, would this alter the results, or is the first record picked
before the ordering is done?


To partially answer my own question, It appears the ordering won't help (I
also had to do this in an intermediate query). If anyone can help please
do!

Cheers
Chris

Nov 12 '05 #2

P: n/a
If you take a look at the Totals row in QBE grid, you will see all sorts
of aggregate functions to return one value from a group: Firt, Last,
Max, Min, etc.

Pavel

Not Me wrote:

Hi,

Having a table with some duplicate ID's (different data tho), how can I
return the list but with only one record from each ID? Would this be using
the first() function and grouping?

Also, how would I choose which would be the first to be picked? If I did an
order by xx, would this alter the results, or is the first record picked
before the ordering is done?

Cheers,
Chris

Nov 12 '05 #3

P: n/a
"Pavel Romashkin" <pa*************@hotmail.com> wrote in message
news:3F***************@hotmail.com...
If you take a look at the Totals row in QBE grid, you will see all sorts
of aggregate functions to return one value from a group: Firt, Last,
Max, Min, etc.


Ta, I think I need to use the first() function but it isn't working as I
think it should.

The sorting query displays results like this (apologies for poor
formatting!)

ID Company StillWorksHere
111 X No
112 Y Yes
112 X No
112 Z No
113 Y Yes
113 Z No

So I thought if I grouped by ID and picked the first company I'd get the 2
that still work for that company, and the one that doesn't work for any
company. But I get results no different from if I use the original table
rather than the sorted one.

Cheers,
Chris
Nov 12 '05 #4

P: n/a
"Not Me" <su**@sumwhere.fake> wrote in message news:<bp**********@ucsnew1.ncl.ac.uk>...
Hi,

Having a table with some duplicate ID's (different data tho), how can I
return the list but with only one record from each ID? Would this be using
the first() function and grouping?

Also, how would I choose which would be the first to be picked? If I did an
order by xx, would this alter the results, or is the first record picked
before the ordering is done?

Cheers,
Chris


Hi Chris, you can use a query that displays only DISTINCT records.
Meaning, if you have a query that displays only ID, then using
DISTINCT will only show distinct records, excluding duplicate ID's.
If you have a query that displays ID and a date or some other field,
only distinct field combinations will appear...for example, if you
have once instance of ID=1 and Date =9/4/96, then the combination of
these fields would produce only one distinct record. If you have a
second instance of ID=1 with a different Date = 9/5/97, then two
records will be displayed for ID=1, one with the first date, and the
other with the second date, since the field combinations of ID and
Date are distinct for each record. It's kind of hard to explain in
text.

To make your query show only distinct records, open your query in
Design View; under Query Properties, set Unique Values = Yes.
Nov 12 '05 #5

P: n/a
"Bryan Christopher" <bh*****@medlink.com> wrote in message
news:d7**************************@posting.google.c om...
"Not Me" <su**@sumwhere.fake> wrote in message news:<bp**********@ucsnew1.ncl.ac.uk>...
<snip>
Also, how would I choose which would be the first to be picked? If I did an order by xx, would this alter the results, or is the first record picked
before the ordering is done?

Cheers,
Chris


Hi Chris, you can use a query that displays only DISTINCT records.
Meaning, if you have a query that displays only ID, then using
DISTINCT will only show distinct records, excluding duplicate ID's.

<snip> To make your query show only distinct records, open your query in
Design View; under Query Properties, set Unique Values = Yes.


Cheers for that,

What I'm having trouble with is the next part, I'll copy this table from
another post
----------
ID Company StillWorksHere
111 X No

112 Y Yes
112 X No
112 Z No

113 Y Yes
113 Z No
----------

The above is my table after ordering, you see if I pick each first instance
of ID I will get a preference of the company that person still works for,
but if they aren't working at the minute (e.g. ID 111), we still get their
record, and an example of a job they've done.

I thought the first function would work in this case, but I can't get it to
do what I want.

Cheers,
Chris
Nov 12 '05 #6

P: n/a
> ----------
ID Company StillWorksHere
111 X No

112 Y Yes
112 X No
112 Z No

113 Y Yes
113 Z No
----------


I'm with you part of the way with what you are trying to do. If I
understand this right, firstly you want to find all the people who are
still working somewhere:

SELECT * from YourTable where StillWorksHere = yes
which gives with your data
ID Company StillWorksHere
112 Y Yes
113 Y Yes

Then you want to pick up the people who worked at one company, but now
don't. And also don't work anywhere else:

select YT.ID,* from YourTable as YT where YT.ID not in (select yt.id
from YourTable as YT2 where YT.id=YT2.id and StillWorksHere =yes)
which gives with your data
ID Company StillWorksHere
111 X No.
You can union these queries if you like.

But this still leaves a hole in your results. Lets take a look at
someone who worked at company Z, moved to company Y and then retired.
So the table now looks like this:

ID Company StillWorksHere
111 X No
112 Y Yes
112 X No
112 Z No
113 Y Yes
113 Z No
114 Z No
114 Y No

if I run the unioned query, I get:

ID Company StillWorksHere
111 X No
112 Y Yes
113 Y Yes
114 Y No
114 Z No

See how 114 turns up twice? This is where I get stuck. How do you
intend to tell the difference between the last two lines of the table?
What is it logically that makes one of them right and the other
wrong? As far as I can tell from your post its the date/time of
leaving. You want to show the company that was last recently worked
at. If this is so, then you could attach a date/time field and
populate it with the now() of the time the person left. This might
make your problem easier.

I hope this has been some help to you, if not, please explain again
your criteria.

Cheers

Phil.
Nov 12 '05 #7

P: n/a
"Phil" <ph**@ideastakingshape.co.uk> wrote in message
news:5f**************************@posting.google.c om...
----------
ID Company StillWorksHere
111 X No

112 Y Yes
112 X No
112 Z No

113 Y Yes
113 Z No
----------


I'm with you part of the way with what you are trying to do. If I
understand this right, firstly you want to find all the people who are
still working somewhere:


Hi,

Thanks for going to all that trouble! However I managed to get it going
eventually using max and subselect (robbed it from an access website)

Chers,
Chris
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.