Selecting first record from a sorted query

M

Monish

Hi

I hope someone can help me with this - I have a feeling it is fairly simple...

I have a query which has been sorted (by 2 date fields) and grouped (by ID).
I need now to select only the first record from each group. Dataset looks
like below:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
A8B ABC Co 2 496 17.34% 12/5/08 10/15/09
A8B ABC Co 2 496 17.34% 12/5/08 2/15/10
A8B ABC Co 2 496 17.34% 9/19/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0RV XYZ Co. 1 82 30.49% 2/6/09 9/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 12/15/09
A5G LMNO Co 2 254 25.20% 10/24/08 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 11/1/09

What I need returned from this is:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09

Essentially, the first record for each ID is what I need...

I appreciate any help...thanks for reading.
 
K

KARL DEWEY

Try this (UNTESTED) --
SELECT Q.ID, Q.[Acct Name], Q.Seg, Q.Plan, Q.[% To Plan], Q.[Last Date],
Q.[Next Date], (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1)<2))
ORDER BY Q.ID, Q.[Last Date], Q.[Next Date];
 
K

KARL DEWEY

Try this (UNTESTED) --
SELECT Q.ID, Q.[Acct Name], Q.Seg, Q.Plan, Q.[% To Plan], Q.[Last Date],
Q.[Next Date], (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1)<2))
ORDER BY Q.ID, Q.[Last Date], Q.[Next Date];
 
M

Monish

Karl

For whatever reason, this does not work - in fact it simply freezes in the
process...

I am not sure if this was what you were trying to do (I wasn't able to
follow your code unfortunately) but if there was a way I could assign a rank
to each record (without messing with the sorted order) and have that rank
reset when a new ID is encountered, then I could select all the Rank = 1
records and would get what I need...right?

If so, is there an wasy way to assign a rank that resets with a change in ID?

Thanks!
Monish

KARL DEWEY said:
Try this (UNTESTED) --
SELECT Q.ID, Q.[Acct Name], Q.Seg, Q.Plan, Q.[% To Plan], Q.[Last Date],
Q.[Next Date], (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1)<2))
ORDER BY Q.ID, Q.[Last Date], Q.[Next Date];


Monish said:
KARL - I'm sorry but I couldn't read your post...

Monish
Click to expand...
Click to expand...
 
K

KARL DEWEY

That is what I posted. Here is a simple Ranking by Group query --
SELECT Q.[Group], Q.[Points], (SELECT COUNT(*) FROM [Product] Q1
WHERE Q1.[Group] = Q.[Group]
AND Q1.[Points] < Q.[Points])+1 AS Rank
FROM Product AS Q
ORDER BY Q.[Group], Q.[Points];

Instead of your 'Rank = 1' I used 'Rank < 2'.

Here it is without criteria --
SELECT Q.ID, Q.[Acct Name], Q.Seg, Q.Plan, Q.[% To Plan], Q.[Last Date],
Q.[Next Date], (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1 AS Rank
FROM YourTable AS Q
ORDER BY Q.ID, Q.[Last Date], Q.[Next Date];


Monish said:
Karl

For whatever reason, this does not work - in fact it simply freezes in the
process...

I am not sure if this was what you were trying to do (I wasn't able to
follow your code unfortunately) but if there was a way I could assign a rank
to each record (without messing with the sorted order) and have that rank
reset when a new ID is encountered, then I could select all the Rank = 1
records and would get what I need...right?

If so, is there an wasy way to assign a rank that resets with a change in ID?

Thanks!
Monish

KARL DEWEY said:
Try this (UNTESTED) --
SELECT Q.ID, Q.[Acct Name], Q.Seg, Q.Plan, Q.[% To Plan], Q.[Last Date],
Q.[Next Date], (SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[ID] = Q.[ID]
AND Q1.[Last Date] & Q1.[Next Date] < Q.[Last Date] & Q.[Next
Date])+1)<2))
ORDER BY Q.ID, Q.[Last Date], Q.[Next Date];


Monish said:
KARL - I'm sorry but I couldn't read your post...

Monish

:

Hi

I hope someone can help me with this - I have a feeling it is fairly simple...

I have a query which has been sorted (by 2 date fields) and grouped (by ID).
I need now to select only the first record from each group. Dataset looks
like below:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
A8B ABC Co 2 496 17.34% 12/5/08 10/15/09
A8B ABC Co 2 496 17.34% 12/5/08 2/15/10
A8B ABC Co 2 496 17.34% 9/19/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0RV XYZ Co. 1 82 30.49% 2/6/09 9/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 12/15/09
A5G LMNO Co 2 254 25.20% 10/24/08 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 11/1/09

What I need returned from this is:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09

Essentially, the first record for each ID is what I need...

I appreciate any help...thanks for reading.
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Monish said:
I have a query which has been sorted (by 2 date fields) and grouped (by ID).
I need now to select only the first record from each group. Dataset looks
like below:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
A8B ABC Co 2 496 17.34% 12/5/08 10/15/09
A8B ABC Co 2 496 17.34% 12/5/08 2/15/10
A8B ABC Co 2 496 17.34% 9/19/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0RV XYZ Co. 1 82 30.49% 2/6/09 9/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 12/15/09
A5G LMNO Co 2 254 25.20% 10/24/08 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 11/1/09

What I need returned from this is:

ID Acct Name Seg Plan % To Plan Last Date Next Date
A8B ABC Co 2 496 17.34% 12/5/08 4/24/09
0RV XYZ Co. 1 82 30.49% 2/6/09 5/15/09
0JM AAAA Inc. 1 15 6.67% 1/28/09 6/15/09
A5G LMNO Co 2 254 25.20% 3/20/09 8/15/09
AWP QRS Inc 2 170 19.41% 3/6/09 7/15/09

Essentially, the first record for each ID is what I need...
Click to expand...


I think you want each group's record with the earliest date.
That may sound like the same thing, but it may help think
about the problem you are trying to solve. Try something
like:

SELECT thetable.*
FROM thetable
WHERE thetable.LastDate =
(SELECT Min(X.LastDate)
FROM thetable As X
WHERE X.ID = thetable.ID)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using results from a Sub Query in a Crosstab Query 4
Selecting a specific file from many 4
Selecting value from second list when date is within date range on first list 5
Data from two sheets make up a list in a third sheet (real time) 1
from excel to access - a specific question 2
Need help tidying up a report 2
colin mcrae rally 4 (from a brazilian) 1
Outlook connector crashes outlook 0

Top