473,322 Members | 1,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

first()/order by query

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
7 10739
"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
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
"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
"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
"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
> ----------
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: neptune | last post by:
I must be missing something obvious. I have 3 fields and sample data. 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992 I...
1
by: Grim Reaper | last post by:
I asked this question, earlier, and received an answer that I am not sure about how to do. Basically, I am printing mailing labels with a "Sorting/Grouping" section that groups the label types...
8
by: Henrik Larsson | last post by:
Hi, I need help with selecting the following rows from a table looking like this: ID IP Query 1 -> 1 2.2.2.2 (ie first IP 1 1.1.1.1 <- Query 2 for each...
1
by: rloef | last post by:
I have a multi-million row table with three indexes in MySQL-5.0.15. These indexes have the following number of distinct values: date 415 block 100000 scan 45 If I'm doing a query...
5
by: comp.lang.php | last post by:
$orderBy = 's.app_date desc, s.last_name asc, s.first_name asc, s.mi asc'; if ($_REQUEST) { $ascArray = array('asc' => 'desc', 'desc' => 'asc'); // ARRAY OF ALL ORDERING POSSIBILITIES $junk =...
8
by: Roger | last post by:
I have a question regarding the behaviour of sql with OR and fetch first 1 rows only : I have a table with data : ACNO NAME TELNO CITY ZIP 1000 ...
7
by: Mary | last post by:
I have a student who has a hyphenated first name. If I concatenate the name like this: StudentName:( & ", " & ), it works as expected. If, however, I try to get the first name first by...
6
by: zack | last post by:
This query has been driving me crazy, and would be grateful for a nudge in the right direction. Still pretty much a beginner using Access 2003, I just cannot figure the solution to the following in...
3
by: Mesut | last post by:
I have written a form in with radio buttons the name is set to orderby and the value is set to KundeVorName and the next value is KundeNachName and it goes so on. I wanna modify my query according...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.