April 6, 2009 at 1:09 pm
maybe my typo messed it up.
The line
GROUP BY Col_1, Col_2, Col_3
should be
GROUP BY Col_1, Col_2, Col_5
5 instead of 3
If it is not this then I think I am not getting what are you trying to produce. You may have to provide some sample data and then explain what result this data should produce.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 6, 2009 at 1:10 pm
Jack Corbett (4/6/2009)
Based on this data:
ID Col1 Col2 Col3 Col4 Col5
----------- -------- ---- ----------------------- ---- -----------------------
1 Example X 2009-04-07 14:39:29.900 Z 2009-04-09 14:39:29.900
2 Example X 2009-04-08 14:39:29.900 Z 2009-04-10 14:39:29.900
3 Example Y 2009-04-11 14:39:29.900 A 2009-04-12 14:39:29.900
What do you want to returned?
Just the third row only. I need to get only rows with the most recent datetype values in Col_5 for each grouping of "Example X". However, in the table you';ve provided , Col_1 and Co_2 is rendered as
Example X
Example X
Example X
There is no "Y" in second field in the third row.
I can't get a sub-select query to just return the 1 row (the third row) in your Table). Do I need to append a unique ID to the table to write this ??
Once again, thanks for your help, and I'll review your previous observations.
-uman
April 6, 2009 at 1:28 pm
JacekO (4/6/2009)
maybe my typo messed it up.The line
GROUP BY Col_1, Col_2, Col_3
should be
GROUP BY Col_1, Col_2, Col_5
5 instead of 3
If it is not this then I think I am not getting what are you trying to produce. You may have to provide some sample data and then explain what result this data should produce.
Okay, now this reduced it down to 2 rows
X, Y, 2, 4, Z
X, Y, 5, 6, A
How to get it down to just
X, Y, 5, 6, A
Here is an example Sample Data I'm trying to get the results:
X, Y, 1, 3, A
X, Y, 2, 4, A
X, Y, 5, 6, Z
C, D, 2, 4, Z
C, D, 1, 3, A
C, D, 5, 6, A
E, F, 3, 5, Z
So, from the above, I'm would like to have the query produce the following:
X, Y, 5, 6, Z
C, D, 5, 6, A
E, F, 3, 5, Z
The columns with numbers represent datetype values. I need to get the 1 row belonging to max column from 4th datetype column for each grouping of the 1st and 2nd column.
I'm leaving out the query specification on the MAX(Column 3). There's another way I'm going to write this using that.
I appreciate all of your efforts. I've learned a lot from this web forum. Thanks
April 6, 2009 at 1:33 pm
Drop the last grouping
Change from
GROUP BY Col_1, Col_2, Col_5
to this
GROUP BY Col_1, Col_2
Does it work now?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 6, 2009 at 1:33 pm
Please don't qualify the results in the data I presented, just answer what your requirements require you to return based on the data provided. It seems you keep changing what you need.
Do you want the most recent (based on the date col5) row grouped on Col1, Col2, and Col4? In the 3 rows of data I provided that would be:
ID Col1 Col2 Col3 Col4 Col5
------------ -------- ---- ----------------------- ---- -----------------------
2 Example X 2009-04-08 14:39:29.900 Z 2009-04-10 14:39:29.900
3 Example Y 2009-04-11 14:39:29.900 A 2009-04-12 14:39:29.900
If this is right then this code should work:
DECLARE @test-2 TABLE
(
ID int identity
primary key,
Col1 varchar(100),
Col2 CHAR(1),
Col3 datetime,
Col4 CHAR(1),
Col5 datetime
) ;
insert into
(
Col1,
Col2,
Col3,
Col4,
Col5
)
select 'Example', 'X', '2009-04-07 14:39:29.900', 'Z', '2009-04-09 14:39:29.900' union all
select 'Example', 'X', '2009-04-08 14:39:29.900' , 'Z', '2009-04-10 14:39:29.900' union all
select 'Example', 'Y', '2009-04-11 14:39:29.900' , 'A', '2009-04-12 14:39:29.900' ;
;WITH cteData AS
(
SELECT
col1,
col2,
col4,
MAX(col5) AS col5
FROM
GROUP BY
col1,
col2,
col4
)
SELECT
T.Col1,
T.Col2,
T.Col3,
T.Col4,
T.Col5
FROM
@test-2 AS T JOIN
cteData AS D ON
T.Col1 = D.Col1 AND
T.Col2 = D.Col2 AND
T.Col4 = D.Col4 AND
T.Col5 = D.Col5
WHERE
T.Col1 = 'Example'
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 6, 2009 at 1:55 pm
Jack Corbett (4/6/2009)
Please don't qualify the results in the data I presented, just answer what your requirements require you to return based on the data provided. It seems you keep changing what you need.
Jack, I apologize for any confusion. An example of the source table is:
X, Y, 1, 3, A
X, Y, 2, 4, A
X, Y, 5, 6, Z
C, D, 2, 4, Z
C, D, 1, 3, A
C, D, 5, 6, A
E, F, 3, 5, Z
(Numbers merely represent the datetype columns)
So, from the above, I'm would like to have the query produce the following:
X, Y, 5, 6, Z
C, D, 5, 6, A
E, F, 3, 5, Z
Each row contains the max of datetype column 5, grouped on first two columns. I appreciate the assistance, and will run the code with this data in this post. Thanks for your patience.
-uman
April 6, 2009 at 2:22 pm
I reckon this is what you are looking for
DROP TABLE #Temp
CREATE TABLE #Temp (col1 CHAR(1), col2 CHAR(1), col3 CHAR(1), col4 CHAR(1), col5 CHAR(1))
INSERT INTO #Temp (col1, col2, col3, col4, col5)
SELECT 'X', 'Y', '1', '3', 'A' UNION ALL
SELECT 'X', 'Y', '2', '4', 'A' UNION ALL
SELECT 'X', 'Y', '5', '6', 'Z' UNION ALL
SELECT 'C', 'D', '2', '4', 'Z' UNION ALL
SELECT 'C', 'D', '1', '3', 'A' UNION ALL
SELECT 'C', 'D', '5', '6', 'A' UNION ALL
SELECT 'E', 'F', '3', '5', 'Z'
SELECT t.*
FROM #Temp t
INNER JOIN (SELECT col1, col2, MAX(col3) AS MAXcol3, MAX(col4) AS MAXcol4
FROM #Temp
GROUP BY col1, col2) d
ON d.col1 = t.col1 AND d.col2 = t.col2 AND d.MAXcol3 = t.col3 AND d.MAXcol4 = t.col4
col1 col2 col3 col4 col5
---- ---- ---- ---- ----
C D 5 6 A
E F 3 5 Z
X Y 5 6 Z
but I have to agree with the other posters on this thread, it's not easy to tell!!
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2009 at 2:28 pm
Chris Morris (4/6/2009)
I reckon this is what you are looking for
DROP TABLE #TempCREATE TABLE #Temp (col1 CHAR(1), col2 CHAR(1), col3 CHAR(1), col4 CHAR(1), col5 CHAR(1))
INSERT INTO #Temp (col1, col2, col3, col4, col5)
SELECT 'X', 'Y', '1', '3', 'A' UNION ALL
SELECT 'X', 'Y', '2', '4', 'A' UNION ALL
SELECT 'X', 'Y', '5', '6', 'Z' UNION ALL
SELECT 'C', 'D', '2', '4', 'Z' UNION ALL
SELECT 'C', 'D', '1', '3', 'A' UNION ALL
SELECT 'C', 'D', '5', '6', 'A' UNION ALL
SELECT 'E', 'F', '3', '5', 'Z'
SELECT t.*
FROM #Temp t
INNER JOIN (SELECT col1, col2, MAX(col3) AS MAXcol3, MAX(col4) AS MAXcol4
FROM #Temp
GROUP BY col1, col2) d
ON d.col1 = t.col1 AND d.col2 = t.col2 AND d.MAXcol3 = t.col3 AND d.MAXcol4 = t.col4
col1 col2 col3 col4 col5---- ---- ---- ---- ----
C D 5 6 A
E F 3 5 Z
X Y 5 6 Z
but I have to agree with the other posters on this thread, it's not easy to tell!!
No, based on this:
Each row contains the max of datetype column 5, grouped on first two columns. I appreciate the assistance, and will run the code with this data in this post. Thanks for your patience.
You need to group on Col1 and Col2 and get MAX(Col5) then return the row(s) that match that. So using your final query would be:
SELECT t.*
FROM #Temp t
INNER JOIN (SELECT col1, col2, MAX(col5) AS MAXcol5
FROM #Temp
GROUP BY col1, col2) d
ON d.col1 = t.col1 AND d.col2 = t.col2 AND d.MAXcol5 = t.col5
I think?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 6, 2009 at 2:29 pm
Chris,
I thought you were heading to a pub...:-P
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 6, 2009 at 2:35 pm
JacekO (4/6/2009)
Chris,I thought you were heading to a pub...:-P
Nah Jacek, the kitchen! Leffe brune, just the one...gonna stop now, keep bumping into Jack. Good luck, Jack 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2009 at 2:38 pm
Jack Corbett (4/6/2009)
I think?
It's one of those, Jack, but the requirements are so ambiguous it's kinda hard to tell 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 6, 2009 at 2:45 pm
Chris Morris (4/6/2009)
It's one of those, Jack, but the requirements are so ambiguous it's kinda hard to tell 🙂
Exactly! I don't drink but posts like these could drive me to it 😀
Yours actually looks like it returns the results Uman wants.
No offense intended Uman, I understand that sometimes it can be hard to explain what you need. That's why we ask for example data with desired results. That can remove any ambiguity.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2009 at 7:14 am
Jack Corbett (4/6/2009)
Chris Morris (4/6/2009)
It's one of those, Jack, but the requirements are so ambiguous it's kinda hard to tell 🙂Exactly! I don't drink but posts like these could drive me to it 😀
Yours actually looks like it returns the results Uman wants.
No offense intended Uman, I understand that sometimes it can be hard to explain what you need. That's why we ask for example data with desired results. That can remove any ambiguity.
No offense taken. Your assistance, along with Chris Morris, has proved substantial, and I sincerely appreciate your feedback. I've learned a great deal from this forum. I always work through the problems before posting, but I see I was unclear in my initial presentation of the requirements. I will heed your advice in the future.
It ran Chris Morris code, and it took successfully. What is that called when you join a table unto itself? I had that idea, too, but couldn't nail it. I'm reviewing the output (The source table, obviously, is much larger than the example I provided).
-uman
April 8, 2009 at 10:36 am
Chris Morris, Jack, et. all.
Much appreciative of the code you provided. Works to a Tee. I'm much more in debt to the thought process of the query you wrote; i couldn't nail that one. In the future, I will make sure to pay more attention to the results. Thanks again.
Any time your in Boston, I'll buy you a round of brewskis for you.
-uman
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply