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!
1 1161
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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 =...
|
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....
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |