Query question: How to get last record 1

[Zargo]

Hi all,

I have two tables like:

Table1
indexnr
1001
1002
1003
1004

Table2
Indexnr | Datefield | Actionfield

1001 |09-20-2006 | 4000
1001 |09-21-2006 | 4001
1001 |09-22-2006 | 4008
1001 |09-23-2006 | 4050
1002 |09-20-2006 | 4001
1002 |09-21-2006 | 4050
1002 |09-22-2006 | 4060


I want to see al entries in table1 which has entries in table 2 (table1.index = table2.index) and the last record in table2 must have action 4050. So the result must be:
1001

It is a huge table so i have give an exmaple here.

TIA,
Zargo

 

[bborissov]

--- Preparing Test data
--- You don't need this

DECLARE @Table1 Table (IndexNr int)

INSERT INTO @Table1 VALUES (1001)
INSERT INTO @Table1 VALUES (1002)
INSERT INTO @Table1 VALUES (1003)
INSERT INTO @Table1 VALUES (1004 ) 


DECLARE @Table2 Table (IndexNr int, DateField datetime, Actionfield int)
INSERT INTO @Table2 VALUES (1001,'20060920', 4000)
INSERT INTO @Table2 VALUES (1001,'20060921', 4001)
INSERT INTO @Table2 VALUES (1001,'20060922', 4008)
INSERT INTO @Table2 VALUES (1001,'20060923', 4050)
INSERT INTO @Table2 VALUES (1002,'20060920', 4001)
INSERT INTO @Table2 VALUES (1002,'20060921', 4050)
INSERT INTO @Table2 VALUES (1002,'20060922', 4060)


SELECT Table1.*
       FROM @Table1 Table1
       INNER JOIN (SELECT Table2.IndexNr, Table2.Actionfield 
                          FROM @Table2 Table2
                          INNER JOIN (SELECT IndexNr, MAX(Datefield) AS MaxDate
                                             FROM @Table2 Table2
                                             GROUP BY IndexNr) Tbl2
                          ON Table2.IndexNr   = Tbl2.IndexNr AND
                             Table2.Datefield = Tbl2.MaxDate) Tbl3
      ON Table1.IndexNr = Tbl3.IndexNr AND Tbl3.Actionfield = 4050

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Zargo]

Thanks a lot!!!!! YOu get a big star!!!

 

[Zargo]

Hi,

Its a quite complex query . I want to add some more info in my select list from other tables how can i do that. So i have some info in table3 also with a indexnr. Here are some columns which i want to see in my select list. And a want some columns to see from table2.

So now we have: SELECT Table1.*
I want Select Table1.*, Table2.somecolumns, Table3.somecolumns .....

How to do this?

 

[Zargo]

By the way table3 has always unique indexnr's

 

[bborissov]

SELECT Table1.*, Tbl3.(list of fields here), Table3.(list here)
       FROM Table1
       INNER JOIN (SELECT Table2.IndexNr, Table2.Actionfield (add all additional fields you want in result)
                          FROM Table2
                          INNER JOIN (SELECT IndexNr, MAX(Datefield) AS MaxDate
                                             FROM Table2
                                             GROUP BY IndexNr) Tbl2
                          ON Table2.IndexNr   = Tbl2.IndexNr AND
                             Table2.Datefield = Tbl2.MaxDate) Tbl3
       ON Table1.IndexNr = Tbl3.IndexNr AND Tbl3.Actionfield = 4050
       INNER JOIN Table3 ON Table1.IndexNr = Table3.IndexNr

(this is not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Zargo]

I'm getting the message The column prefix 'XXX' does not match with a table name or alias name used in the query.

XXX are the new columns of table3.

Must we add the other tables also in the from list of the first part?

SELECT Table1.*, Tbl3.(list of fields here), Table3.(list here)
FROM Table1, TB13, TABLE3

?

Thanks a lot for your quick response.

 

[bborissov]

Post some values from Table3 and say which columns you want to add from table3 and table2.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Zargo]

In my selectlist i want to add table3.status_index in the query above. I want also select some extra columns of the existing table2, because this table has also a column named lastcomment, i also want to add this in my select list.

table3
IndexNr Statusindex
1001 1
1002 64
1003 81
1004 95

table2
Table2
Indexnr | Datefield | Actionfield | LastComment

1001 |09-20-2006 | 4000 | adfasdf
1001 |09-21-2006 | 4001 | adfasdfas
1001 |09-22-2006 | 4008 | drewf
1001 |09-23-2006 | 4050 | dfasd
1002 |09-20-2006 | 4001 | sadfas
1002 |09-21-2006 | 4050 | sadfa
1002 |09-22-2006 | 4060 | dsfasdfa

 

[bborissov]

--- Preparing Test data
--- You don't need this

DECLARE @Table1 Table (IndexNr int)

INSERT INTO @Table1 VALUES (1001)
INSERT INTO @Table1 VALUES (1002)
INSERT INTO @Table1 VALUES (1003)
INSERT INTO @Table1 VALUES (1004 )


DECLARE @Table2 Table (IndexNr int, DateField datetime, Actionfield int, LastComment varchar(200))
INSERT INTO @Table2 VALUES (1001,'20060920', 4000,'aaaaaaa')
INSERT INTO @Table2 VALUES (1001,'20060921', 4001,'bbbbbbbbbbb')
INSERT INTO @Table2 VALUES (1001,'20060922', 4008,'cccccccc')
INSERT INTO @Table2 VALUES (1001,'20060923', 4050,'ddddddddddd')
INSERT INTO @Table2 VALUES (1002,'20060920', 4001,'eeeeeeeeeeeee')
INSERT INTO @Table2 VALUES (1002,'20060921', 4050,'fffffffffffffffffff')
INSERT INTO @Table2 VALUES (1002,'20060922', 4060,'gggggggggggggggggggggg')


DECLARE @Table3 Table (IndexNr int, Statusindex int)
INSERT INTO @Table3 VALUES (1001,10)
INSERT INTO @Table3 VALUES (1002,64)
INSERT INTO @Table3 VALUES (1003,81)
INSERT INTO @Table3 VALUES (1004,95)



SELECT Table1.*, Tbl3.LastComment, Table3.StatusIndex
       FROM @Table1 Table1
---- Begin with derived table, we use it only to filter records and to get LastComment from Table2
       INNER JOIN (SELECT Table2.IndexNr, Table2.Actionfield, Table2.LastComment
                          FROM @Table2 Table2
                          INNER JOIN (SELECT IndexNr, MAX(Datefield) AS MaxDate
                                             FROM @Table2 Table2
                                             GROUP BY IndexNr) Tbl2
                          ON Table2.IndexNr   = Tbl2.IndexNr AND
                             Table2.Datefield = Tbl2.MaxDate) Tbl3
      ON Table1.IndexNr = Tbl3.IndexNr AND Tbl3.Actionfield = 4050
----- End of Derived table

--- Now we want to join Table3 so we can get Statusindex field
      INNER JOIN @Table3 Table3 ON Table1.IndexNr = Table3.IndexNr

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP