473,320 Members | 1,612 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,320 software developers and data experts.

Limiting results to 3 most recent ID's for a group by...

I have a query which someone wrote in Access and I have been charged with the task of making it work in SQL Server. The query runs fine, but the problem is that I need to get back only 3 records as opposed tot he 4 that are returned.
I am using a group by, and one of the rows i need suppressed is unique not sure how i can do this. here is the query...
SELECT
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation
, Max(tblBOARD_TEST_DETAILS.Test_Date) AS Test_Date
, tblBOARD_TEST_DETAILS.P_F_I AS P_F_I
, Max(tblBOARD_TEST_DETAILS.TestID) AS TestID
FROM
tblBOARD_TEST_DETAILS
GROUP BY
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation
, tblBOARD_TEST_DETAILS.P_F_I
HAVING
(((tblBOARD_TEST_DETAILS.Part_Number) Like ‘002-0402%’)
AND
((tblBOARD_TEST_DETAILS.Serial_Number)=’0504510044 21’))
ORDER BY
Max(tblBOARD_TEST_DETAILS.Test_Date) DESC;

it returns this:

002-0402-01,050451004421,HIPOT,2007-04-03 09:56:49.000,P,217907
002-0402-01,050451004421,RUN IN,2007-03-30 02:00:00.000,F,217473
002-0402-01,050451004421,RUN IN,2007-03-30 02:00:00.000,P,217318
002-0402-01,050451004421,SIP,2007-04-03 09:32:20.000,P,217877

The last field (TestID is unique, but from the two records that occured on 3-30 I need only the one who has the highest testID (217473) how can i eliminate the 217318 record from the resultset?

thank you!
Apr 6 '07 #1
1 1161
iburyak
1,017 Expert 512MB
1. I changed Having to Where condition which is more appropriate.

[PHP]SELECT
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation
, Max(tblBOARD_TEST_DETAILS.Test_Date) AS Test_Date
, tblBOARD_TEST_DETAILS.P_F_I AS P_F_I
, Max(tblBOARD_TEST_DETAILS.TestID) AS TestID
FROM
tblBOARD_TEST_DETAILS
WHERE
(((tblBOARD_TEST_DETAILS.Part_Number) Like ‘002-0402%’)
AND
((tblBOARD_TEST_DETAILS.Serial_Number)=’0504510044 21’))

GROUP BY
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation
, tblBOARD_TEST_DETAILS.P_F_I
ORDER BY
Max(tblBOARD_TEST_DETAILS.Test_Date) DESC;[/PHP]


2. The problem is in this column:

tblBOARD_TEST_DETAILS.P_F_I

See your data it has F in one row and P in another.
When you do a group by it considers which one to take and takes both.

002-0402-01,050451004421,RUN IN,2007-03-30 02:00:00.000,F,217473
002-0402-01,050451004421,RUN IN,2007-03-30 02:00:00.000,P,217318


You have to decide which one you want both F and P records or you want
one record and this column can be excluded or if there is no preferences you should do max on this column too and you will always get P in this case.

[PHP]
, MAX(tblBOARD_TEST_DETAILS.P_F_I) AS P_F_I[/PHP]

Final query should look like this:

[PHP]SELECT
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation
, Max(tblBOARD_TEST_DETAILS.Test_Date) AS Test_Date
, Max(tblBOARD_TEST_DETAILS.P_F_I) AS P_F_I
, Max(tblBOARD_TEST_DETAILS.TestID) AS TestID
FROM
tblBOARD_TEST_DETAILS
WHERE
(((tblBOARD_TEST_DETAILS.Part_Number) Like ‘002-0402%’)
AND
((tblBOARD_TEST_DETAILS.Serial_Number)=’0504510044 21’))

GROUP BY
tblBOARD_TEST_DETAILS.Part_Number
, tblBOARD_TEST_DETAILS.Serial_Number
, tblBOARD_TEST_DETAILS.Operation

ORDER BY
Max(tblBOARD_TEST_DETAILS.Test_Date) DESC;[/PHP]
Hope it helps.
Apr 6 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jay Moore | last post by:
Greetings, all! I have a project for work, and I'm not sure how to efficiently do what I need to do. I'm hoping someone out there can help. Project is this: I'm creating a web-based...
6
by: Kingdom | last post by:
I'm using this script to dynamicaly populate 2 dropdowns and dispaly the results. Choose a component type from the first drop down, lets say 'car' and the second box will list all the car...
4
by: s99999999s2003 | last post by:
hi the database "execute" function returns a list of logical results. Each logical result is a list of row tuples, as explained in the documents. everytime i use it to execute various...
3
by: Shaiguy | last post by:
Does anyone have a clue on how to limit a table (using a Query) to the most recent 2 records by date ? Example: I have a table containing ProjectAreaID (primaryKey) ProjectID Date
3
by: William Wisnieski | last post by:
Hello Everyone, I'm helping out a non-profit school with their database. They would like to know the last gift made by each donor, the donor name, and the gift amount. I built a query based...
8
by: Jason H | last post by:
Hi, I am sure I am just overlooking the obvious, but I am having a little trouble with this one... I am setting up an inventory database that tracks company tools and their location. This...
1
by: striker77 | last post by:
i get a list of results based on an id and based on a status value that can be 'active', 'inprocess' or 'cancel' - it is possible that the same user has both 'active' and 'inprocess' states...
1
by: digidave | last post by:
I am keenly aware that my coding skills are extremely noob but please indulge me a second.. Take a look at these queries.. $sql = "SELECT DISTINCT year FROM _current_floats_config WHERE active =...
2
hodgeman
by: hodgeman | last post by:
My second thread on thescripts, so hoping to get the same feedback and help as last time... I've developed an online invoicing and payment system for all my accounts for my web design company....
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...
0
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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.