Tricky SQL without subqueries

Delzhand

I've got a working solution for this, but in the interest of performance I want to see if it can be done in a single query. Given the following table:

a | 1
a | 6
a | 2
b | 3
b | 5

I'm trying to get only those rows which have the greatest number for a given letter. So, this:

a | 6
b | 5

I could have sworn there was a way to do this with group by/having. Any ideas?

BigBadWolf

For the example you gave, you want to group it by column1 & then find the max value of column 2.
Something like:

SELECT Column1, Max(Column2) FROM tableName GROUP BY Column1

If you've got other fields in the data that you want to pick up then it gets a bit trickier! Are the values in the 2nd column always unique?

Delzhand

No, and there's a lot of other data. Here's a better slice:

| p_id | act_id | created    |
|  561 |   1009 | 1461783822 |
|  562 |   1032 | 1461790728 |
|  562 |   1031 | 1461790691 |
|  562 |   1030 | 1461790630 |
|  562 |   1025 | 1461789681 |
|  563 |   1032 | 1461790728 |
|  563 |   1031 | 1461790691 |
|  563 |   1030 | 1461790630 |
|  563 |   1027 | 1461789921 |
|  564 |   1032 | 1461790728 |
|  564 |   1031 | 1461790691 |
|  564 |   1030 | 1461790630 |
|  564 |   1028 | 1461789946 |
|  565 |   1032 | 1461790728 |
|  565 |   1031 | 1461790691 |
|  565 |   1030 | 1461790630 |
|  565 |   1029 | 1461790623 |
|  566 |   1033 | 1461793310 |
|  567 |   1035 | 1461850275 |
|  568 |   NULL |       NULL |

This is already the result of 3 joins, and I don't know if I'm just tired or what but my brain is breaking even trying to figure out how to do it with subqueries.

BigBadWolf

Is act_Id always unique for a given p_id? If not, and there's 2 rows with the maximum value do you need both or just one of them?

Also, what database system are you using are you using?

Delzhand

Oh, actually using max does work. I had the act_id column in there but p_id and created are all I really care about. Thanks!

BigBadWolf

You can also join the max query back to the original table to get the entire row:

SELECT T.* 
FROM
(SELECT p_id, Max(act_id) AS MaxAct_id
 FROM <tableName> GROUP BY p_id) AS T1
INNER JOIN
<tblName> AS T on T.p_id = T1.p_id AND T.act_id = T1.MaxAct_Id

Paladin

Ooh people know sql on this forum? Neat

Sir Carcass

People know anything on this forum.

bowen

There's programmer and system admin threads in Moe's sub forum

Paladin

bowen wrote: »

There's programmer and system admin threads in Moe's sub forum

I actually want to ask an sql question; should I do it here or in Moe's?

bowen

@Paladin I'd say try moe's first then H/A if none of us can help you