April 6, 2009 at 8:10 am
Hi,
I'm trying to write query which returns the row with the maximum value of Column 1 and maximum value of Column 2. I don't know if you can do this with a MAX function.
When I write the following query:
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_3, Col_4, Col_5
I get the following data of 3 rows:
X, Y, 1, Z, 3
X, Y, 2, Z, 4
X, Y, 5, A, 6
I just need to produce the 3rd row, which have the maximum values I need to show. So I write the above query in a noncorrelated sub-select:
1 SELECT Col_1, Col_2, Col_3, Col_4, Col_5
2 FROM Table
3 WHERE ((Col_3 IS NOT NULL) AND (Col_4 IS NOT NULL)) IN
4 (SELECT MAX(Col_3), MAX(Col_4)
5 FROM Table
6 WHERE Col_One = "Example")
And it bomb ons Line 3 with:
An expression of non-boolean type specified in a context where a condition is expected, near 'AND'.
Col_3 and Col_4 are both datetime datatypes. But I thought the expression would capture if they are true or false. And I can't seem to produce the subquery when I write it with one column either. (with just the Select as Col_3 or just Col_4)
Anyone have any suggestions are on how to write the 3rd row? I appreciate any help. I'll keep working on this and checking back.
-uman (frustrated)
April 6, 2009 at 8:46 am
One problem with your first query is that it includes the columns you want the MAX for in the GROUP BY so you get all the rows. I think you want to change this query:
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_3, Col_4, Col_5
to this
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_5
I am really not sure what you want to do, so if you read the first link in my signature and post using those suggestions you will likely get more accurate responses.
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 8:55 am
If I understand what you need, here's an example that seems to work:
-- Set up test data
create table #T (
ID int identity primary key,
Col1 varchar(100),
Col2 int,
Col3 datetime,
Col4 datetime,
Col5 int);
insert into #T (Col1, Col2, Col3, Col4, Col5)
select 'Example', 1, getdate(), getdate(), 1 union all
select 'Example', 1, getdate()+1, getdate()+1, 1 union all
select 'Example', 2, getdate(), getdate(), 2;
-- Query row with max values
select top 1 *
from #T
order by Col2 desc, Col3 desc, Col4 desc, Col5 desc;
Try that on your data, please.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 6, 2009 at 9:03 am
Hi
Could you please give us with sample input data and out put data which you are expecting. Your requirement is bit confusing.
April 6, 2009 at 9:13 am
Jack Corbett (4/6/2009)
One problem with your first query is that it includes the columns you want the MAX for in the GROUP BY so you get all the rows. I think you want to change this query:to this
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_5
There's an interesting issue here Jack - MAX(Col_3) will give exactly that, but MAX(Col_4) in the result set will be Col_4 from the row which was chosen for MAX(Col_3) ...
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 9:24 am
Chris Morris (4/6/2009)
Jack Corbett (4/6/2009)
One problem with your first query is that it includes the columns you want the MAX for in the GROUP BY so you get all the rows. I think you want to change this query:to this
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_5
There's an interesting issue here Jack - MAX(Col_3) will give exactly that, but MAX(Col_4) in the result set will be Col_4 from the row which was chosen for MAX(Col_3) ...
I don't think so Chris. It should return the Max value for COL_4 in the grouping of COL_1, COL_2, and COL_5. Try this (slightly modified test data provided by GSquared):
DECLARE @test-2 TABLE
(
ID int identity
primary key,
Col1 varchar(100),
Col2 int,
Col3 datetime,
Col4 datetime,
Col5 int
) ;
insert into @test-2
(
Col1,
Col2,
Col3,
Col4,
Col5
)
select
'Example',
1,
getdate(),
getdate() + 1,
1
union all
select
'Example',
1,
getdate() + 1,
getdate() - 1,
1
union all
select
'Example',
2,
getdate(),
getdate(),
2 ;
SELECT
*
FROM
SELECT
Col1,
Col2,
MAX(Col3),
MAX(Col4),
Col5
FROM
WHERE
Col1 = 'Example'
GROUP BY
Col1,
Col2,
Col5
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 9:35 am
Is this what you need? (It is hard to figure out what do you really need)
SELECT Col_1, Col_2, Col_3, Col_4, Col_5
FROM Table
INNER JOIN (
SELECT
MAX(Col_3) AS max_col_3,
MAX(Col_4) AS max_col_4
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_3
) AS X ON Col_3 = max_col_3 AND Col_4 = max_col_4
WHERE Col_1 = "Example"
Of course this is not going to work if the Col3 and Col4 max values are not in the same row, but somehow I got the impression that they are in your case.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 6, 2009 at 9:36 am
Jack Corbett (4/6/2009)
Chris Morris (4/6/2009)
Jack Corbett (4/6/2009)
One problem with your first query is that it includes the columns you want the MAX for in the GROUP BY so you get all the rows. I think you want to change this query:to this
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_5
There's an interesting issue here Jack - MAX(Col_3) will give exactly that, but MAX(Col_4) in the result set will be Col_4 from the row which was chosen for MAX(Col_3) ...
I don't think so Chris. It should return the Max value for COL_4 in the grouping of COL_1, COL_2, and COL_5. Try this (slightly modified test data provided by GSquared):
Good Lord, sorry Jack, what was I thinking :blush: time for more coffee!
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 9:39 am
Chris Morris (4/6/2009)
Jack Corbett (4/6/2009)
Chris Morris (4/6/2009)
Jack Corbett (4/6/2009)
One problem with your first query is that it includes the columns you want the MAX for in the GROUP BY so you get all the rows. I think you want to change this query:to this
SELECT Col_1, Col_2, MAX(Col_3), MAX(Col_4), Col_5
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_5
There's an interesting issue here Jack - MAX(Col_3) will give exactly that, but MAX(Col_4) in the result set will be Col_4 from the row which was chosen for MAX(Col_3) ...
I don't think so Chris. It should return the Max value for COL_4 in the grouping of COL_1, COL_2, and COL_5. Try this (slightly modified test data provided by GSquared):
Good Lord, sorry Jack, what was I thinking :blush: time for more coffee!
Chris,
you need more sleep, not more coffee. 😀
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
April 6, 2009 at 10:11 am
Chris Morris (4/6/2009)
Good Lord, sorry Jack, what was I thinking :blush: time for more coffee!
No worries. I was just a little surprised you said that, and knew something must be up. 😀
I could use more sleep too, as suggested by Jacek.
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 11:24 am
Jack Corbett (4/6/2009)
Chris Morris (4/6/2009)
Good Lord, sorry Jack, what was I thinking :blush: time for more coffee!No worries. I was just a little surprised you said that, and knew something must be up. 😀
I could use more sleep too, as suggested by Jacek.
Thanks for the break, Jack - only an hour to beertime...
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 12:29 pm
GSquared (4/6/2009)insert into #T (Col1, Col2, Col3, Col4, Col5)
select 'Example', 1, getdate(), getdate(), 1 union allselect 'Example', 1, getdate()+1, getdate()+1, 1 union all
select 'Example', 2, getdate(), getdate(), 2;
[/code]
GSquare, (and thus far to everyone else I have yet to reply to),
The data I'm trying to produce looks like the following
X, Y, 1, Z, 3
X, Y, 2, Z, 4
X, Y, 5, A, 6
I apologize, the correct datatime datatype columns are the ones with the integers (column 3 and 5). I can still use your example, but before I run the create table query, you created getdate values being identical. The datetime datatype I'm using are all distinct, as showing by the tabler results. Also, you choose "2" as the 2nd value in the third row. The data i'm working with has idential data in first 2 fields in all 3 rows, and the 4th column has letters, not numbers, so the data I'm working with is:
X, Y, 1, Z, 3
X, Y, 2, Z, 4
X, Y, 5, A, 6
Following your example, I would modify the part of the query to
select 'Example', X, getdate()+1, Z, getdate()+3 union all
select 'Example', X, getdate()+2, Z, getdate()+4 union all
select 'Example', Y, getdate()+5, A, getdate()+6,;
But I'm trying to determine the reasoning for the values you selected for the inserts before proceeding. I appreciate your help and will wait for your response.
-uman
April 6, 2009 at 12:33 pm
Jack, thanks for the guide, i reposted by reply coded as table data to the previous poster. I hope this makes a difference. I'll review your link in more detail. Thanks for your help.
April 6, 2009 at 12:42 pm
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?
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 12:58 pm
JacekO (4/6/2009)
Is this what you need? (It is hard to figure out what do you really need)
SELECT Col_1, Col_2, Col_3, Col_4, Col_5
FROM Table
INNER JOIN (
SELECT
MAX(Col_3) AS max_col_3,
MAX(Col_4) AS max_col_4
FROM Table
WHERE Col_1 = "Example"
GROUP BY Col_1, Col_2, Col_3
) AS X ON Col_3 = max_col_3 AND Col_4 = max_col_4
WHERE Col_1 = "Example"
Of course this is not going to work if the Col3 and Col4 max values are not in the same row, but somehow I got the impression that they are in your case.
The query works, but there is no difference in the results: I receive the same 3 rows that I specificed in my first post. I just need to extract the 3rd row only (I have table in which there are many duplicates like that appear in triplicate; i'm just trying to weed out the row with the most updated datetype field in Col_3 or Col_4 and produce that 3rd row only for each row of its kind).
I appreciate your help, and see how I can figure this out
-uman
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply