SQL Query - Grouping with multiple tables

Question

There are 2 tables involved in this question:

Table1:

|- Time Stamp -|- Special Number -|- Other Data -|
|- 2011       -|- 1              -|- green      -|
|- 2010       -|- 1              -|- blue       -|
|- 2009       -|- 2              -|- yellow     -|
|- 2011       -|- 3              -|- red        -|
|- 2010       -|- 3              -|- orange     -|
|- 2009       -|- 4              -|- purple     -|

Table 2:

|- Special Number -|- State (location) -|
|- 1              -|- Hawaii           -|
|- 2              -|- Hawaii           -|
|- 3              -|- Alaska           -|
|- 4              -|- Alaska           -|

Table 2 relates the 'Special Number' to a 'State'

Now then, the result I am trying to get would look something like:

|- Time Stamp -|- State (location) -|- Other Data -|
|- 2011       -|- Hawaii           -|- green      -|
|- 2011       -|- Alaska           -|- red        -|

Where I am trying to get the MAX time stamp, grouped by each state, as well as the other data corresponding to that 'latest time stamp' row from Table 1.

If I do:

SELECT MAX(time stamp), state
FROM table 1, table 2
WHERE table 1.special number = table 2.special number
GROUP BY state

This returns the max time stamp, for each state (which is almost what I am looking for), but when I try to include 'Other Data', it returns all the records (since every 'other data' record is unique).

I hope someone can provide some ideas, Thanks

EDIT:

Table 1 has a Unique ID column:

|- Time Stamp -|- Special Number -|- Other Data -|- Unique Row ID -|
|- 2011       -|- 1              -|- green      -| 0              -|
|- 2010       -|- 1              -|- blue       -| 1              -|
|- 2009       -|- 2              -|- yellow     -| 2              -|
|- 2011       -|- 3              -|- red        -| 3              -|
|- 2010       -|- 3              -|- orange     -| 4              -|
|- 2009       -|- 4              -|- purple     -| 5              -|

EDIT 2: SOLUTION * * * * Thanks to everyone who posted ! * * * *

 SELECT t1.timestamp, t2.specialNumber, t1.otherData
 FROM Table1 t1 inner join Table2 t2 on t1.specialNumber = t2.specialNumber
     inner join (select MAX(Table1.timestamp) maxts, Table2.state
         from Table1 inner join Table2 on Table1.specialNumber = Table2.specialNumber
         group by Table2.state) t3
     on t2.state = t3.state and t1.timestamp = t3.maxts

* whew *

Do you have an id column to uniquely identify a row in table 1? — geekchic, Nov 17 '11 at 16:25
Search on here for "sql latest row" and you should be able to find several approaches — Tom H, Nov 17 '11 at 16:25
I do have an ID column to uniquely identify a row in Table 1, I will post an edited table 1 — SimpleSam5, Nov 17 '11 at 16:29

score 4 · Accepted Answer · edited May 23 '17 at 12:22

4

Ahhh, the good old greatest-n-per-group...

Here's one way to do it:

SELECT t1.TimeStamp, t2.State, t1.OtherData
FROM Table1 t1
inner join Table2 t2 
    on t1.SpecialNumber = t2.SpecialNumber
inner join (SELECT MAX(time stamp) maxts, state
            FROM table1 inner join table2
            ON table1.specialnumber = table2.specialnumber
            GROUP BY state) t3
    on t2.State = t3.State and t1.TimeStamp = t3.maxts

There's a very comprehensive answer here: SQL Select only rows with Max Value on a Column

edited May 23 '17 at 12:22

Community

1
1

answered Nov 17 '11 at 16:26

Adriano Carneiro

57,693
12
90
123

you missed the alias maxts in the derived table. – Kash Nov 17 '11 at 16:35
@Kash Yes, I did. Thanks for the heads up, it's fixed! – Adriano Carneiro Nov 17 '11 at 16:36
Thank you, this was close enough to get me to the solution! I will post it soon. – SimpleSam5 Nov 17 '11 at 16:43
@Adrian: Is there a way to use your second method (Left Joining with self, tweaking join conditions and filters) with the 2 smart moves in this situation. Struggling with it (just curious). – Kash Nov 17 '11 at 17:08
@Kash Yes. I will give you a hint: in the example in the comprehensive answer you join ONE TABLE with itself. In this scenario, you will have to join `Table1 join Table2` with itself (meaning you will join joins). If the DB supports CTEs, this is done withe less code and more clearly. Don't forget to upvote the answer over there! http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column/7745635#7745635 – Adriano Carneiro Nov 17 '11 at 17:21

score 1 · Answer 2 · answered Nov 17 '11 at 16:35

1

You could make your query into a subquery (called states in my version) and then join that back into table1 again and grab the extra columns, like this:

SELECT * FROM

(SELECT MAX(time stamp), state
FROM table 1, table 2
WHERE table 1.special number = table 2.special number
GROUP BY state) states

LEFT JOIN table1 t1_again
ON states.specialNumber = t1_again.specialNumber

answered Nov 17 '11 at 16:35

pseudopeach

1,475
14
29

Besides Adrian's comment on specialNumber, this code is inconsistent in table and column names. There is no alias for the MAX function's column. – Kash Nov 17 '11 at 16:39
My post was not meant as literal code, but as an example of how you might solve this problem. Obviously column names like "special number" aren't really going to work, but thanks for pointing out the obvious. – pseudopeach Nov 17 '11 at 18:10

SQL Query - Grouping with multiple tables

2 Answers2

Linked