CODEWITHSUNDEEP
sqljoingroup-by
rjcarr
rjcarr

Reputation: 2091

SQL Query Help: Probably Involves Group By

My software runs on a few databases so I need to be generic (noticibly, I think a "distinct on" solution might work, but that isn't standard).

Say I have two tables defined as:

Table A: id, time (pk: id)
Table B: id(fk), key, value (pk: id, key)

Where the id of Table B is a foreign key to Table A and the primary keys are as specified.

I need the latest (in time) value of the requested keys. So, say I have data like:

id | key | value
1  | A   | v1
1  | B   | v2
1  | C   | v3
2  | A   | v4
2  | C   | v5
3  | B   | v6
3  | D   | v7

Where time is increasing for the id, and I want values for keys A and C, I would expect a result like:

key | value
A   | v4
C   | v5

Or for keys D and A:

key | value
D   | v7
A   | v4

Ideally, I'd only get n rows back for n key requests. Is something like this possible with a single query?

Upvotes: 2

Views: 81

Answers (2)

dbenham
dbenham

Reputation: 130819

I'm assuming that no ID values will have the same TIME value.

I believe the following is about as generic as you can get with the option of running on as many databases as possible. But the trade off for platform flexibility is less than optimal performance.

The sub-query with alias t identifies the latest time available for each key.

select a.id,
       a.time,
       b.key,
       b.value
  from tableB as b
  inner join tableA as a
    on a.id=b.id
  inner join (
      select b2.key,
             max(a2.time) time
        from tableB as b2
       inner join tableA as a2 on a2.id=b2.id
       group by b2.key
  ) as t on a.time = t.time and b.key = t.key
  where b.key in ('D','A')

The WHERE clause could be moved inside the sub-query and it might perform slightly better on a database that has a primitive optimizer. But putting the WHERE clause in the outer query makes it easier to maintain, and also leaves open the possibility of creating a view without the WHERE clause. The WHERE clause can then be added to a select from the view.

A query using ROW_NUMBER() would be more efficient, or even better yet a query using something like Oracle's KEEP LAST. But those features would make the query more restrictive to certain platforms.

Upvotes: 1

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102408

Here is it working with SQL Server 2008 R2:

Schema

CREATE TABLE B (
   ind integer,
   [key] varchar(8),
   value varchar(16)
);

INSERT INTO B VALUES (1, 'A', 'v1');
INSERT INTO B VALUES (1, 'B', 'v2');
INSERT INTO B VALUES (1, 'C', 'v3');
INSERT INTO B VALUES (2, 'A', 'v4');
INSERT INTO B VALUES (2, 'C', 'v5');
INSERT INTO B VALUES (3, 'B', 'v6');
INSERT INTO B VALUES (3, 'D', 'v7');
INSERT INTO B VALUES (3, 'A', 'v12');
INSERT INTO B VALUES (3, 'C', 'v17');
INSERT INTO B VALUES (3, 'C', 'v101');

Query

select [key], max(CAST(SUBSTRING(value, 2, LEN(value)) as INT)) from B
where [key] in ('A', 'C')
group by [key]

If you want to keep the v as a prefix, do this:

select [key], 'v' + CAST(max(CAST(SUBSTRING(value, 2, LEN(value)) as INT)) as VARCHAR) as Value
from B
where [key] in ('A', 'C')
group by [key]

Output

| KEY |    VALUE |
------------------
|   A |      v12 |
|   C |     v101 |

The SQL Fiddle to play with: http://sqlfiddle.com/#!3/9de72/1

Upvotes: 2

Related Questions