Reputation: 25
A B C
+--------+-------+----+
| 310 | 09001 | 18 |
| 80614 | 09001 | 18 |
| 169009 | 09001 | 19 |
| 201695 | 09001 | 19 |
| 282089 | 09001 | 19 |
| 320438 | 09001 | 19 |
| 131733 | 09001 | 19 |
| 387427 | 09001 | 19 |
| 404201 | 09001 | 19 |
| 525449 | 09001 | 19 |
| 605542 | 09001 | 19 |
| 710740 | 09001 | 18 |
| 746380 | 09001 | 19 |
| 867492 | 09001 | 19 |
| 864637 | 09005 | 18 |
| 710741 | 09005 | 17 |
| 746375 | 09005 | 18 |
| 556470 | 09005 | 18 |
| 604258 | 09005 | 18 |
| 401597 | 09005 | 18 |
| 141331 | 09005 | 18 |
| 336054 | 09005 | 18 |
| 387423 | 09005 | 18 |
| 203706 | 09005 | 18 |
| 278651 | 09005 | 18 |
| 126352 | 09005 | 18 |
| 312 | 09005 | 17 |
| 74627 | 09005 | 17 |
+--------+-------+----+
Wasnt sure how to title the question, but here's the scenario. Im looking to display 1 row for each distinct value in column B, Which is the highest value in column C, and the lowest in Column A.
For example, for 09001 It would be 169009 09001 19, and for 09005 it would display 126352 09005 18
So basically max of column C grouped by column B, and lowest value in column A of those rows.
Thanks in advance
SELECT MIN(A) AS MaxA, B, MAX(C) AS MinC FROM Table GROUP BY B
gives
310 09001 19
312 09005 18
not
131733 09001 19
141331 09005 18
which is what I need
select min(A), B, C
from my_table
where ( C) in (
select max(C)
from my_table
where B in (09001,09005) group by B)
and B in (09001,09005)
group by B, C
results in
310 09001 18
126352 09005 18
131733 09001 19
but there should only be one result for 09001
Select min A,B
from my_table
where C in (select max(c)
from my table
where A
in (09001,
09005)group by A)
and fips in (09001,
09005)
group by A
ended up working, although it only shows A and B, but all I need is A for this example. Im sure there are other ways to get C to show as well.
Thanks for the help guys
Upvotes: 0
Views: 51
Reputation: 107
I think you've already solved your own problem.
SELECT MIN(A) AS MaxA, B, MAX(C) AS MinC
FROM Table
GROUP BY B
Unless I made some sort of silly error there.
EDIT: I understand the requirements better now. Give this a shot.
WITH T1 AS (
SELECT B, MAX(C) AS MaxC
FROM Table
GROUP BY B
), T2 AS (
SELECT MIN(A) AS MinA, MaxC
FROM T1
GROUP BY MaxC)
SELECT T2.MinA AS A, T1.B, T1.MaxC AS C
FROM T1
INNER JOIN T2
ON T1.MaxC = T2.MaxC
Upvotes: 1
Reputation: 133410
could you need min over the max for B
select min(A), B, C
from my_table
where ( B, C ) in (
select B, max(C)
from my_table
group by B
)
group by B,C
Upvotes: 0