Selecting first record from a sorted query

  • Thread starter Thread starter Monish
  • Start date Start date
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.
 
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];
 
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];
 
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...
 
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...
 
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)
 
Back
Top