Select top Y.column for each X

Select top Y.column for each X

Post by loungef.. » Fri, 15 Oct 1999 04:00:00



Two tables:

Documents
-------
Doc_ID
Doc_Name

Versions
-------
Version_ID
Doc_ID    -    FK to Documents.Doc_ID

I need to SELECT every Document.ID and Document.Name along with the TOP
Version.ID from Documents on Version JOINED to Documents on
Versions.Doc_ID = Documents.ID .

This should be simple, but my SQL is a little rusty.

Thanks,
Kurt

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Select top Y.column for each X

Post by Eric Garz » Fri, 15 Oct 1999 04:00:00


Kurt,
    SELECT
        D.Doc_ID,
        D.Doc_Name,
        V.Version_ID
    FROM
        Documents D INNER JOIN Versions V
            ON D.Doc_ID = V.Doc_ID
    WHERE V.Version_ID = (
        SELECT MAX(V2.Version_ID)
        FROM Versions V2
        WHERE V2.Doc_ID = D.Doc_ID
        )

    I wrote it freehand so it might have an error, but that's the idea.

Regards,
Eric Garza


Quote:> Two tables:

> Documents
> -------
> Doc_ID
> Doc_Name

> Versions
> -------
> Version_ID
> Doc_ID    -    FK to Documents.Doc_ID

> I need to SELECT every Document.ID and Document.Name along with the TOP
> Version.ID from Documents on Version JOINED to Documents on
> Versions.Doc_ID = Documents.ID .

> This should be simple, but my SQL is a little rusty.

> Thanks,
> Kurt

> Sent via Deja.com http://www.deja.com/
> Before you buy.


 
 
 

Select top Y.column for each X

Post by loungef.. » Sat, 16 Oct 1999 04:00:00


Eric,

That was it, exactly. I really appreciate your assistance.

Thanks,
Kurt



Quote:> Kurt,
>     SELECT
>         D.Doc_ID,
>         D.Doc_Name,
>         V.Version_ID
>     FROM
>         Documents D INNER JOIN Versions V
>             ON D.Doc_ID = V.Doc_ID
>     WHERE V.Version_ID = (
>         SELECT MAX(V2.Version_ID)
>         FROM Versions V2
>         WHERE V2.Doc_ID = D.Doc_ID
>         )

>     I wrote it freehand so it might have an error, but that's the
idea.

> Regards,
> Eric Garza

Sent via Deja.com http://www.deja.com/
Before you buy.
 
 
 

1. Selecting TOP column values only?

I'm trying to do a route look-up against an SQL table containing data from
multiple BGP feeds.  So I have address, mask, and length and I want to match
the best (longest) route.


BY length DESC;

Now if I want to return only the top rows whos length is the maximum value,
how do I do this?  And I don't want to do a TOP 1, I want all cases where
the length is the same as the TOP 1, so the number of rows will vary
depending upon the route entries present.  I could just select the TOP 1,
store the length, and then add it to a WHERE clause later; something like
this:



BY length DESC;


But this seems to me to be somewhat inefficient to me.  Perhaps it's not
inefficient, as maybe the result would be cached from the previous query.  I
have just short of 1,000,000 rows in the prefixes table.  I have a further

working against.  Are there any other optimizations I could be doing?  Would
indexing address or mask make any difference?  Both are bigint values, as
I've not been able to figure out how to store an unsigned int with SQL
Server 2000 (though I intend to explore storing it as a signed int so long
as the bitwise and works correctly).

Any suggestions/comments?

Thanks.

-saxon

2. How to propagate a parent key update?

3. SELECT TOP n OF EACH DISTINCT VALUE IN A COLUMN

4. SOFTWARE AG - Natural Transfer Utility

5. SELECT TOP @top

6. Newbie : IDENTITY Problem...

7. SQL - way to select the top 10 / top 20 from a database

8. Snapshot replication with updating subscribers

9. HELP:select all columns, but do not select where specific columns are duplicated

10. Select TOP, Select BOTTOM ???

11. Is SELECT ABSOLUTE like SELECT TOP 1 possible?

12. Selecting the first 6 columns of a table by column order/ column id

13. Select to Combine Column.Thisrow with Column.NextRow