[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
6
Medium Priority
?
550 Views
Last Modified: 2012-09-05
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
Comment
Question by:Rob4077
  • 3
  • 3
6 Comments
 
LVL 61

Expert Comment

by:mbizup
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

Open in new window

0
 
LVL 61

Accepted Solution

by:
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 

Open in new window

0
 

Author Comment

by:Rob4077
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Closing Comment

by:Rob4077
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

by:mbizup
ID: 38368426
Rob,

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

Author Comment

by:Rob4077
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question