Help! I can't write this SQL Server query ... can you?

[Teknari]

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04
I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

Teknari

[Prashant]

Lets say the name of your table is Shipment_Status. Here goes

select Shipment,Id from shipment_status a where
id=(select max(id) from shipment_status b where shipment=a.shipment )
or
id=(select max(id) from shipment_status c where shipment=a.shipment
and id <>(select max(id) from shipment_status d where
shipment=c.shipment) )
Try it
Prashant

ju***@bgtl.com (Teknari) wrote in message news:<b1**************************@posting.google. com>...

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04
I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

Teknari

[Anith Sen]

You can write this in a couple of ways:

--#1 (using TOP clause)
SELECT *
FROM tbl
WHERE Updated IN ( SELECT TOP 2 t1.Updated
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
ORDER BY t1.Updated DESC );

--#2 (using an aggregate)
SELECT *
FROM tbl
WHERE( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
AND t1.Updated >= tbl.Updated) <= 2 ;

--
- Anith
( Please reply to newsgroups only )

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You might want to use ISO-8601 temporal formats,
just in case you have to exchange data with someone, either human or
machine.

I also hope that you have not written your own audit logs in SQL and
that "updated" refers to the ETA and not the PHYSICAL row. There are
tools for that kind of function, which is totally outside the scope of
an application database. Likewise, I will assume that you know better
than to use any kind of auto-increment number for a key, so I assume
that the real DDL looks like this:

CREATE TABLE ShipmentHistory
(shipment_nbr INTEGER NOT NULL,
eta DATETIME NOT NULL,
revised_eta DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (shipment_nbr, eta, revised_eta));

I need a query that will return the two most recently updated rows

for each shipment. <<

SELECT H1.shipment_nbr, H1.eta, MAX(H1.revised_eta),
(SELECT MAX(H2.revised_eta)
FROM ShipmentHistory AS H2
WHERE H1.shipment_nbr = H2.shipment_nbr
AND H2.revised_eta < H1.revised_eta)
FROM ShipmentHistory AS H1
GROUP BY H1.shipment_nbr, H1.eta

This will give you a NULL if the ETA changed only once.

[Tyrone Perkins]

Celko wrote...

<snip>
Likewise, I will assume that you know better

than to use any kind of auto-increment number for a key

<snip>

Why not use an auto increment number as a key?

[Igor Raytsin]

You could try this

SELECT s.*
FROM shipment_status s
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment

UNION ALL

SELECT s1.shipment, MAX(s1.Updated)
FROM shipment_status s1
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment) s2 ON s1.shipment = s2.shipment AND
s1.Updated < s2.max_updated
GROUP BY s1.shipment) as m_all
ON s.shipment = m_all.shipment AND s.Updated = m_all.max_updated
ORDER BY s.shipment, s.ID
"Teknari" <ju***@bgtl.com> wrote in message
news:b1**************************@posting.google.c om...

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04
I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

Teknari

[Joe Celko]

>> Why not use an auto increment number as a key? <<

I have longer rants, but the Cliff Notes version is:

1) It is PHYSICAL and not LOGICAL

2) Non-relational

3) Proprietary

4) Meaningless in the data model

5) Unverifiable in the reality of the data model

6) Dangerously redundant, assuming that the table is actually a properly
designed table.

Newbies throw this on a table to fake a key because they don't know what
a key is, they are too lazy to research their industry for standards and
it looks like a sequential file's record number. They only know files
and still think in those terms.

As an aside, if this is an audit log, then you might want to look at
third party tools which are built for audits:

http://www.sswug.org/searchresults.a...find2=lumigent

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Teknari]

Thanks Anith,

You solution seemed the most elegant (at least as far as length of
query) so I went with it.

I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I
really don't understand this.

Can somebody explain to me how it is used below or point me to a good
tutorial on the web?

Thanks,
Teknari

"Anith Sen" <an***@bizdatasolutions.com> wrote in message news:<g7*****************@newsread3.news.atl.earth link.net>...

You can write this in a couple of ways:

--#1 (using TOP clause)
SELECT *
FROM tbl
WHERE Updated IN ( SELECT TOP 2 t1.Updated
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
ORDER BY t1.Updated DESC );

--#2 (using an aggregate)
SELECT *
FROM tbl
WHERE( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
AND t1.Updated >= tbl.Updated) <= 2 ;

[Joe Celko]

>> I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I really
don't understand this.<<

This is a correlation name, or alias. It lets youuse the same table in
sevreal places. This is one of the reasons that SQL used to mean
"Structured Query Language"; it is that fundamental to the language. It
acts as if the engine has made a copy of the base table under the new
name that exists for the duration o the statement.

... point me to a good tutorial on the web? <<

Go to the FirstSQL website or use Google to find one.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!