-
Jun 12th, 2017, 04:36 PM
#1
Thread Starter
Fanatic Member
[RESOLVED] SQL Query SELECT data with multiple fields and only get the maximum for one value.
Hello:
I am writing a query to get specific info from a table.
Code:
SELECT VariableID, RevisionNo, ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue]
WHERE VariableID = 56 OR VariableID=63 OR VariableID=68
The code above produces the following table.
Everything is going as expected. However, I would like to not have #1 show up, since I am only interest in the latest RevisionNo for the VariableID. The system does not like to do MAX unless only one field is selected, so I need another way to return this.
Note, there is only one table, so I cannot use JOIN logic.
Thank you in advance for any help you can provide.
-
Jun 12th, 2017, 09:01 PM
#2
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
First, you select the max RevisionNo for each VariableID:
Code:
Select VariableID, MAX(RevisionNo) As MaxRev
from VariableValue
Group by VariableID
Now you use that as part of an inner select and join back to the table to get what you want:
Code:
Select V.VariableID, V.RevisionNo, V.[Data()]
from VariableValue V
inner join (Select VariableID, MAX(RevisionNo) As MaxRev
from VariableValue
Group by VariableID)RV
on V.VariableID = RV.VariableID and V.RevisionNo = RV.MaxRev
-tg
-
Jun 14th, 2017, 09:34 AM
#3
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello:
First off, thank you for your replies to this post.
It is my understanding that I can add INNER JOINS to INNER JOINS. I am struggling a little with this because I am unsure where the parenthesis go. Some examples show them right before FROM, with multiple left parenthesis to cover each INNER JOIN. The problem is, I do not seem to be able to get this to work.
Below is a query I have written, just to see what it does. The second to last right parenthesis is erroring out. Can you tell me what I am missing?
Code:
SELECT DISTINCT F.[Filename]
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID
FROM MultipleApproval.dbo.VariableValue) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT V2.ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue] V2
INNER JOIN (SELECT VariableID, MAX(RevisionNo) As MaxRev
FROM [MultipleApproval].[dbo].[VariableValue]
GROUP BY VariableID) RV
ON V2.VariableID = RV.VariableID AND V2.RevisionNo = RV.MaxRev
WHERE V2.VariableID=56 OR V2.VariableID=63 OR V2.VariableID=68
)
)
)
--FOR XML PATH('')
Thanks again, for any syntactical things I might come to understand from this.
-
Jun 14th, 2017, 09:59 AM
#4
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
You're missing two things... 1) an alias for the sub query and what to join on... it would help if you re-adjusted your indents so that things that go together are indented similar...
But you're missing the ON clause between one of the joins... it's hard to tell because the indents don't fit my COD tendencies... (ODC, but in alpha-order as it should be).
Give me a few and I'll see if I can straighten it out.
-tg
-
Jun 14th, 2017, 10:03 AM
#5
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
OK... as far as I can tell you had TWO extra parens on the end... no alias for the one sub query and no join condition for it either...
here it is re-formatted based on how you wrote it (it may still not be right)... and I've marked the missing alias and join condition.
Code:
SELECT DISTINCT F.[Filename]
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID
FROM MultipleApproval.dbo.VariableValue) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT V2.ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue] V2
INNER JOIN (SELECT VariableID, MAX(RevisionNo) As MaxRev
FROM [MultipleApproval].[dbo].[VariableValue]
GROUP BY VariableID) RV
ON V2.VariableID = RV.VariableID AND V2.RevisionNo = RV.MaxRev
WHERE V2.VariableID=56 OR V2.VariableID=63 OR V2.VariableID=68
) [ADD ALIAS] on [ADD YOUR JOIN CONDITION HERE]
-tg
-
Jun 14th, 2017, 10:05 AM
#6
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
I removed all three parenthesis, as they are redundant. There is still a syntax error after the 68 on line 12.
Code:
SELECT DISTINCT F.[Filename]
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID
FROM MultipleApproval.dbo.VariableValue) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT V2.ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue] V2
INNER JOIN (SELECT VariableID, MAX(RevisionNo) As MaxRev
FROM [MultipleApproval].[dbo].[VariableValue]
GROUP BY VariableID) RV
ON V2.VariableID = RV.VariableID AND V2.RevisionNo = RV.MaxRev
WHERE V2.VariableID=56 OR V2.VariableID=63 OR V2.VariableID=68 (errors here now)
--FOR XML PATH('')
-
Jun 14th, 2017, 10:07 AM
#7
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Just curious, what other join conditions do I need?
-
Jun 14th, 2017, 10:29 AM
#8
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
OI... I said you had TWO extra parens, so you deleted three? I showed what you needed... you need to make the final select query a sub query by encasing it in parens ... that means one ( at the front (you have that) and onr ) at the end (which you had, but deleted) ... and then you need to alias it... give it a name ... and then you need to give it the condition on which to join to the other tables...
You did it successfully with the join between F and V, so I'm not sure what the problem here is...
-tg
-
Jun 14th, 2017, 10:32 AM
#9
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
I think I got it, so thanks for now!! I guess I need to add something I don't really need.
Code:
SELECT DISTINCT V.DocumentID, F.[Filename], V.VariableID
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID
FROM MultipleApproval.dbo.VariableValue
) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT V2.ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue] V2
INNER JOIN (SELECT VariableID, MAX(RevisionNo) As MaxRev
FROM [MultipleApproval].[dbo].[VariableValue]
GROUP BY VariableID
) RV
ON V2.VariableID = RV.VariableID AND V2.RevisionNo = RV.MaxRev
WHERE V2.VariableID=56 OR V2.VariableID=63 OR V2.VariableID=68
) X ON F.DocumentID=V.DocumentID
--FOR XML PATH('')
-
Jun 14th, 2017, 10:44 AM
#10
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
What do you mean? What did you need to add?
Wait... you have the X sub-query in there, but you never use it in the join condition?!!!??? You just rejoin F to V, which is already done from the first inner join.
Nor do you use it anywhere in the result. It's going to give you a cartesian result...
So why do you even need that second join if you're not joining that data to anything or using its results in any fashion?
You could probably cut it out and still get the same results.
-tg
-
Jun 14th, 2017, 02:08 PM
#11
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
The second join is on the same table. The goal in an attempt to only show the latest revision. I am still playing with it, but I have lost it since making the query more complete.
Current results.
Original Query.
Code:
SELECT V.ValueText + ', ' AS 'data()'
FROM VariableValue V
INNER JOIN (SELECT VariableID, MAX(RevisionNo) As MaxRev
FROM VariableValue
GROUP BY VariableID)RV
ON V.VariableID = RV.VariableID AND V.RevisionNo = RV.MaxRev
WHERE V.VariableID=56 OR V.VariableID=63 OR V.VariableID=68
FOR XML PATH('')
Let me know if you have any thoughts? I'm sure most people know more about this than I.
-
Jun 14th, 2017, 04:14 PM
#12
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Ok... so there's more to it than originally posted... I'm intrigued enough hang in there and help you through this
It's the end of hte day and I've got a 60+ minute commute ahead of me (yuck).
If you can, post the tables involved VariableValue and Documents, and any others that might be needed. DO NOT POST screenshots of the data or table defs... type them out...
If possible, something simple like this:
Code:
VariableValue
-----------
ID - int - Id of the field - autonumber
VariableID - int - Identifies the variable value
RevisionNo - int - revision number of the variable
and so on... note which columns relate to fields in other columns... Once I know the table format you're dealing with, I think I'll be able to help better.
-tg
-
Jun 14th, 2017, 05:19 PM
#13
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
TG:
Hope your commute is ok. I have an hour as well.
Below are the two full tables. We don't care about too ma many of the fields.
The data in ValueText is what we are after. For values 64-68, we want to pull Values 52-56 respectively if the value is no, and the initial corresponding value is not NULL. There is one result for each document, and we want the latest rev only with regard to this document.
No rush. I'm evolving through this myself, one step at a time. Sorry for not pulling the full scope in.
I've also been reading books on Safari as I go though this. If you know of a really good one to reference, let me know.
Thanks,
Steve Anderson
Table 1: VariableValue
PK VariableID int
PK DocumentID int
PK ProjectID int
PK RevisionNo int
PK ConfigurationID int
ValueText nvarchar(MAX)
ValueInt int
ValueFloat float
ValueDate datetime
ValueCache nvarchar(64)
IsLongText bit
Table 2: Documents
PK DocumentID int
Fielname nvarchar(255)
LockProject int
UserID int
LockDomain nvarchar(255)
LockPath nvarchar(255)
Busy bit
Flushed int
DevValStored int
RevGenCounter int
LatestRevisionNo int
CurrentStatusID int
WorkingVersionModified bit
ExtensionID int
LockDate datetime
UserDocRefsModified bit
Deleted bit
Shared int
LockViewID uniqueidentifier
Link bit
DocTypeID int
ObjectTypeID int
Flags int
-
Jun 15th, 2017, 01:48 PM
#14
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello:
Just out of curiousity, ValueText is not null! This returns nothing...
Code:
SELECT ValueText + ', ' AS 'data()'
FROM [MultipleApproval].[dbo].[VariableValue] S1
WHERE ValueText IS NOT NULL
-
Jun 15th, 2017, 02:45 PM
#15
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello again:
I am writing to report that this is causing insanity. Doing the same thing over and over and expecting different results. I think I'm getting close, and then I am back to ground zero.
Please let me know if you have any thoughts.... I'm going to read some more books.
Code:
SELECT F.DocumentID, F.[Filename], V.VariableID, V.ValueText, V.RevisionNo
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT MAX(RevisionNo) As MaxRev
FROM MultipleApproval.dbo.VariableValue
) V2
ON V.RevisionNo = V2.MaxRev
WHERE 1=1
Code:
SELECT F.DocumentID, F.[Filename]
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo, MAX(RevisionNo) as MaxRev
FROM MultipleApproval.dbo.VariableValue
GROUP BY DocumentID, VariableID
) V
ON V.DocumentID = F.DocumentID AND V.RevisionNo = V.MaxRev
WHERE 1=1
-
Jun 15th, 2017, 04:28 PM
#16
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello again.
So I think I've finally made some progress. I need to add a column to the mix for the user.
ValueText 64 corresponds to ValueText 52
ValueText 65 corresponds to ValueText 53
ValueText 66 corresponds to ValueText 54
ValueText 67 corresponds to ValueText 55
ValueText 68 corresponds to ValueText 56
You can see in the query below, that these values are hardcoded...
Code:
SELECT DISTINCT F.[Filename], F.LatestRevisionNo, 'Not Approved' AS Approved
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V2
ON V.RevisionNo = F.LatestRevisionNo
WHERE (V.VariableID=52 AND V.ValueText <> 'Yes') OR V.VariableID <> 52
OR (V.VariableID=53 AND V.ValueText <> 'Yes') OR V.VariableID <> 53
OR (V.VariableID=54 AND V.ValueText <> 'Yes') OR V.VariableID <> 54
OR (V.VariableID=55 AND V.ValueText <> 'Yes') OR V.VariableID <> 55
OR (V.VariableID=56 AND V.ValueText <> 'Yes') OR V.VariableID <> 56
And produce this information.
I need to add the fourth column to show the ValueText values for 64 through 68. I will thus need to have multiple columns show up for each filename. Right now, using SELECT DISTINCT reduces the number of rows from 297 to 2. I do not want to show 297 rows. However, I will need to show in this case, two rows for one of two parts based on values existing for 67 and 68 on DocumentID 3.
A pic of the data table is below.
Last edited by ssabc; Jun 16th, 2017 at 08:31 AM.
-
Jun 19th, 2017, 09:56 AM
#17
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Please let me know if I've provided enough information on this. Any help at all is greatly appreciated!
-
Jun 19th, 2017, 09:59 AM
#18
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
I thought I had it... then you added more info... then I lost it.... O.o
It looks like for each VariableID/DocumentID/ProjectID, you need the Max Revision ID, use that to get the Value text, then possibly pivot it to get it all into a row, rather than cols...
-tg
-
Jun 19th, 2017, 10:01 AM
#19
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
It also sounds like you have some cases where some variables might be dependant on the values of others.
-tg
-
Jun 20th, 2017, 01:40 PM
#20
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
TJ:
My apology if it appears this thing keeps changing. The problem is, I only asked specific information in the beginning, thinking that if I understood that, I would understand the rest. Also, there has been a little evolution involved as well.
I am looking to output the following:
Document.Filename, Document.LatestRevisionNo, VariableValue.ValueText (64 thru 68) for Approver and Variable.Value (52 thru 56) for Approved. I only want to see the data when VariableValue (64 thru 68) IN NOT NULL or blank (length=0), and Variable.Value (52 thru 56) = No.
64 has a value and 52 = No
65 has a value and 53 = No
66 has a value and 54 = No
67 has a value and 55 = No
68 has a value and 56 = No
The other trouble is that this data which lives in rows, needs to be outputted in Columns. Some people have suggested creating a temporary table to read from, but it is my understanding that this is not a good practice, and can't SQL queries accomplish this?
Thank you for all your help, and let me know what you think.
-
Jun 21st, 2017, 07:11 AM
#21
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello,
I hope tg. doesn't mind me logging in to this problem.
I do this in quit a few situations I like to call then 'DummyTables'....
Some people have suggested creating a temporary table to read from
as you can use Dispose of the Table when you dont need it an more.
If the Sql-Query in Post#2 is correct then place this in a new Table and work from there
in the new 'DummyTable' if you need Columns for further Information then add them so
that you could place values with a 'insert into'
I know this approach may for some people sound stupid but
to execute multiple Sql-Statements to get the answer you want isn't new
well that is my advice
regards
Chris
-
Jun 27th, 2017, 10:40 AM
#22
Thread Starter
Fanatic Member
Re: SQL Query SELECT data with multiple fields and only get the maximum for one value
Hello:
I think the solution is this simple!
Code:
SELECT DISTINCT F.DocumentID, F.[Filename], F.LatestRevisionNo, V.ValueText, 'Not Approved' AS Approved
FROM MultipleApproval.dbo.Documents F
INNER JOIN (SELECT DocumentID, VariableID, ValueText, RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V
ON V.DocumentID = F.DocumentID
INNER JOIN (SELECT RevisionNo
FROM MultipleApproval.dbo.VariableValue
) V2
ON V.RevisionNo = F.LatestRevisionNo
WHERE (V.VariableID = 52 AND V.ValueText <> 'Yes') OR (V.VariableID = 64)
OR (V.VariableID = 53 AND V.ValueText <> 'Yes') OR (V.VariableID = 65)
OR (V.VariableID = 54 AND V.ValueText <> 'Yes') OR (V.VariableID = 66)
OR (V.VariableID = 55 AND V.ValueText <> 'Yes') OR (V.VariableID = 67)
OR (V.VariableID = 56 AND V.ValueText <> 'Yes') OR (V.VariableID = 68)
Thanks for all your help, everyone!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|