[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Query to list all but MAX record in list

Posted on 2012-09-05
Medium Priority
550 Views
The following query lists the highest (MAX) MessageId by pkCellNo in my joined tables. What I now need to do is create a query that lists all EXCEPT the highest MessageId for every pkCellNo in my Joined tables.

Is there a way of creating a query to give that result?

SELECT tblAssets.pkCellNo, Max(Queue.MessageId) AS MaxOfMessageId
FROM Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo
WHERE Queue.FolderId Between 20 And 21 Or Queue.FolderId Is Null
GROUP BY tblAssets.pkCellNo
ORDER BY tblAssets.pkCellNo;
0
Question by:Rob4077
• 3
• 3

LVL 61

Expert Comment

ID: 38368004
Try this:

``````SELECT * FROM
Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo
LEFT JOIN
(
SELECT tblAssets.pkCellNo, Max(Queue.MessageId) AS MaxOfMessageId
FROM Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo
WHERE Queue.FolderId Between 20 And 21 Or Queue.FolderId Is Null
GROUP BY tblAssets.pkCellNo
) q
ON Queue.MessageId =  q.MaxOfMessageID
WHERE q.MaxOfMessageID IS NULL
ORDER BY tblAssets.pkCellNo
``````
0

LVL 61

Accepted Solution

mbizup earned 2000 total points
ID: 38368023
Or maybe this:

``````SELECT * FROM
Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo
WHERE Queue.FolderId Between 20 And 21 Or Queue.FolderId Is Null

LEFT JOIN
(
SELECT tblAssets.pkCellNo, Max(Queue.MessageId) AS MaxOfMessageId
FROM Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo
WHERE Queue.FolderId Between 20 And 21 Or Queue.FolderId Is Null
GROUP BY tblAssets.pkCellNo
) q
ON Queue.MessageId =  q.MaxOfMessageID
WHERE q.MaxOfMessageID IS NULL
ORDER BY tblAssets.pkCellNo
``````
0

Author Comment

ID: 38368389
Both are coming up with Syntax errors but it doesn't matter, I managed to figure out a different way of achieving the result I needed. Thanks for your help
0

Author Closing Comment

ID: 38368398
Though the solution came up with a systax error it would have led me to the solution had I not found an alternative. For that reason I am awarding full points. Thanks
0

LVL 61

Expert Comment

ID: 38368426
Rob,

Can you post the alternative approach you used for the benefit of others reading this thread?
0

Author Comment

ID: 38371077
The query I created (above) has the MaxOfMessageId fields only. So all I needed to do was link it to my other tables and look for records where the messageId was less than MaxOfMessageID. Seems simple when you think about it. Here's the actual, final. I then just developed a union with the other table. It also allowed me to add a field named Asset that has the asset description as its value for the first record in a CellNo group and blank for others, making my display on the form more friendly.

SELECT "" as Asset, Queue.*, tblAssets.*, tblMessageStatus.MessageStatus
FROM ((Queue RIGHT JOIN tblAssets ON Queue.CellNo = tblAssets.pkCellNo) LEFT JOIN qryLatestMessageByAsset ON tblAssets.pkCellNo = qryLatestMessageByAsset.pkCellNo) LEFT JOIN tblMessageStatus ON Queue.FolderId = tblMessageStatus.pkFolderid
WHERE (((Queue.MessageId)<[MaxOfMessageId]) AND ((Queue.FolderId) Between 20 And 21 Or (Queue.FolderId) Is Null) AND ((Queue.InAlert)=True))
ORDER BY Description, pkCellNo, MsgDate DESC;
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
###### Suggested Courses
Course of the Month18 days, 9 hours left to enroll